Preventing a User f entering duplicate values 3

[sanan]

Hi There
I am trying to stop the users of entering duplicate values in a textBox control called (InvoiceID).
I came up with following codes, But the do not work for me at the time, Any suggestion?
Here are the codes, that I tried;

Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[InvoiceID100] = " & Me.InvoiceID100.Value
'Or Incase of Text or Nchar data type'
Criteria = "[InvoiceID100] = ' " & Me.InvoiceID100.Value & " ' "

If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", "[Criteria]") Then 'Or'
If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", "Criteria")) Then
'Or'
If Not IsNull(DLookup("[InvoiceID100]", "[TableSales]", Criteria)) Then

Msg = "InvoiceId Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub

But no success,
The Data Type of InvoiceID100 was originally nchar,
I tried numeric with the first selection of above Criteria, No success
I should mention, that the InvoiceId100 Lostfocus events
Works fine for both nchar and Numeric data type selection, and the MsgBox pops up,
But for the case of Data type to be text, The LostFocus events works a little strange and msgBox Pops Up even if I have entered some value for it.

Best regards
Sanan

 

[formerTexan]

Hi Sanan,

The BeforeUpDate event is the one you want.

I suggest using the TRIM function to clear leading and trailing spaces off of the textbox value and any value returned via DLookUp. Dispense with the [] when referring to the table in your DLookUp function. Also I have changed to names a bit to reflect the use of a more standard naming convention.

Sp perhaps something like the following:

Dim strCriteria as string
Dim strLookUp as string
strCriteria = TRIM(Me.txtInvoice100) & "" 'clear spaces and nulls
If Not IsNull(TRIM(DLookup
("[InvoiceID100]", "tblSales", [InvoiceID100] = '" & strCriteria & "'"))) Then
' Insert msgbox/message here
End If

Cheers
Bill

 

[TheAceMan1]

How are ya sanan . . . . .

Your not using the criteria variable correctly. Try one or the other of the following:

[blue]   [green]'Numeric[/green]
   Criteria = "[InvoiceID100] =  " & Me.InvoiceID100
   If Not IsNull(DLookup("[InvoiceID100]", "TableSales", Criteria)) Then


   [green]'Text[/green]
   Criteria = "[InvoiceID100] =  '" & Me.InvoiceID100 & "'"
   If Not IsNull(DLookup("[InvoiceID100]", "TableSales", Criteria)) Then[/blue]

See Ya! . . . . . .

 

[PHV]

For text value, you may try this:
If Not IsNull(DLookup("InvoiceID100", "TableSales", "Trim(InvoiceID100)='" & Trim(Me!InvoiceID100) & "'")) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanan]

Hi formerTexan, TheAceMan1, PHV
I would like to thank you very much, for all your Comments.
I tried all your 3 Suggestions, but still no success.
After entering any value in my InvoiceID100 txtBox, The curser Just Frizzes, and I can not move at all, even my mouse frizzes, I just have to close the form or use the design view, which at first I get the Data can not be saved MsgBox then my LostFocus Event’s MsgBox Pops up for 3 times.
Here are all the codes that I tried from your Suggestions;

· formerTexan’s Technique;
Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, strCriteria As String, strLookUp As String
DL = vbNewLine & vbNewLine
strCriteria = Trim(Me.txtInvoiceID100) & ""
If Not IsNull(Trim(DLookup("[InvoiceID100]", "TableSales", "[InvoiceID100] = '" & strCriteria & "'"))) Then
Msg = "InvoiceId Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub

· TheAceMan1’s technique;
Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String
DL = vbNewLine & vbNewLine
Criteria = "[InvoiceID100] = " & Me.InvoiceID100
If Not IsNull(DLookup("[InvoiceID100]", "TableSales", Criteria)) Then
Msg = "InvoiceId Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub

· PHV’s technique;
Private Sub InvoiceID100_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String
DL = vbNewLine & vbNewLine
If Not IsNull(DLookup("InvoiceID100", "TableSales", "Trim(InvoiceID100)='" & Trim(Me!InvoiceID100) & "'")) Then
Msg = "InvoiceId Has Been Issued Allready, Try Another One"
Style = vbCritical + vbOKOnly
Title = "Duplicate Invoice ID Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub

Final Note;
This is an application made of Access as FE, And SQL as BE.
Data Type of InvoiceId100 is Numeric, Length 9, Precision 18, scale 0, Allow Null is checked.

· Also it’s Lost focus Events is as follow, and it works;
Private Sub InvoiceID100_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me.InvoiceID) Or Me.InvoiceID = "" Then
strMsg = "You must Enter an Invoice No. A Date Or Check No.."
strTitle = "Invoice Number Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.ReffrenceID.SetFocus
Me.InvoiceID100.SetFocus
End If
End Sub

As you can see the user must enter a value in InvoiceID100 txtBox,
And in it’s BeforUpdate event, Should not be able to enter a Duplicate value in this Control.

Best Regards
Sanan

 

[TheAceMan1]

sanan . . . . . .

I find that hard to believe . . . . [purple]The method where dealing with here is pretty much standard in Access.[/purple]

From the table [blue]TableSales[/blue] post back the the following items for the field [blue]InvoiceID100[/blue]:
[ol][li][blue]Data Type[/blue][/li]
[li][blue]Field Size[/blue][/li]
[li][blue]Indexed[/blue][/li][/ol]
Post the RecordSource for the form!

A test:
If you make a query returning the same records in the form, and add a number in the criteria existing/non-existing for [blue]InvoiceID100[/blue], does it work?

[blue]After entering any value in my InvoiceID100 txtBox, The curser Just Frizzes, and I can not move at all, even my mouse frizzes,[/blue]

Is this the first time since you first posted?

Either there's some code in another event causing this, or you have some kind of corruption.

Getting back to your origional post:

[blue] should mention, that the InvoiceId100 Lostfocus events
Works fine for both [purple]nchar[/purple] and Numeric data type selection, and the MsgBox pops up,
But for the case of Data type to be text, The LostFocus events works a little strange and msgBox Pops Up even if I have entered some value for it.[/blue]

Post the code for the [blue]Lost Focus[/blue] event.

What is data type [purple]nchar[/purple], where did you get it?

See Ya! . . . . . .

 

[formerTexan]

Sanan,

This is probably a good time to set a breakpoint at the beginning of the code and then step through it to see where your problem lies.

And at the same time have a look at the variables to see what values are actually being passed.

Doing this won;t solve your problem, but it should pinpoint where to look. Let us know what happens.

Cheers,
Bill

 

[sanan]

Hi TheAceMan1
Actually I answered most your questions on my previous reply, the one Just on top of your latest reply.
But anyhow your codes work also PHV’s Code. I tested all your techniques on a New Access DB made of a single Table and a form, I named one of the TxtBox Control’s name InvoiceID100, and in it’s BeforeUpdate Event I used your Techniques;
· The AceMan1 Tech. worked just fine
· PHV’s tech. Also worked just fine.
· FormerTexan’s tech., well it did not work, But I know it just has some minor problems.

Therefore the problem is not with codes that we know now.
The beforeUpdate Events of my original InvoiceID100 also works, since I tested with a simple Beep. And It works, After entering any value in it the Curser moves to next Control, and it also Beeps. But as soon as I put “for example” Your codes in InvoiceID100 beforeUpdate’s Event, Upon entering any value in InvoiceID100, It just fezzes and Curser does not move at all.

The Record Source of my form is called “Sales Query” which is a query made up of 2 Tables “Customers” and “Tablesales”.
These are the Properties of my InvoiceId100 txtBox;
This is an application made of Access as FE, And SQL as BE.
Data Type of InvoiceId100 is Numeric, Length 9, Precision 18, scale 0, Allow Null is checked.
And It is not Indexed, Only my salesID Control is Indexed, and it is also the Primary Key.

Following is the LostFocus event of my InvoiceID100 control;
Private Sub InvoiceID100_LostFocus()
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me.InvoiceID) Or Me.InvoiceID = "" Then
strMsg = "You must Enter an Invoice No. A Date Or Check No.."
strTitle = "Invoice Number Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me.ReffrenceID.SetFocus
Me.InvoiceID100.SetFocus
End If
End Sub

Which works fine, I tested it with data different Types; nchar, Numeric, and Int.
It works with all of them, But it does not work if Data Type is Text.


Best regards
sanan

 

[sanan]

Hi formerTexan
Thank you for reply.
Could you please explain your BreakPoint and Variables just a little more, I never used it before.

Best Regards
sanan

 

[sanan]

Hi There
A Reminder
Once again a reminder, that your suggested codes and technique woks since I tried them in a different Access Data base Application.
But If I put the same codes “The ones that works” in my original form’s InvoiceId100 BefroeUpdate Event, Nothing works and Basically my curser frizzes and does not move at all, Other wise I can not enter any value in it, Duplicate Or Non-Duplicate.
As soon as I remove the above codes from my InvoiceId100’s BeforeUpDate Events, every thing works again, and I can enter values into my form and then it will updates my Query “Query sales”, and finally the Table “TableSales”
In my previous Replies, I gave you all my codes in all the Events.
I should mention in my Experimental Access Only Application Data base, My exp. InvoiceID100 txtBox control had Both BeforeUpDate event, and Lostfocus Event in it and Every thing works Just Fine.

Best regards
Sanan

 

[formerTexan]

Hi Sanan,

My apologies for not checkiny my code syntax: the other suggested examples are excellent, so use one of them.

Variables are declarations such as Msg or DL. They are called variables because they will contain differing values from one instance to another.

Breakpoints are stops placed on executable code lines (see VBA Help for further explanation). A breakpoint will suspend the running of code. Open the VBA Editor window and click on the "window frame" just to the left of a line of code. This should result in the line of code highlighted. Note that breakpoints can't be set on Declaration (Dim) statements and comment lines.

So set a breakpoint near the beginning of your code module and then initiate the code from your form. The code will halt at the breakpoint and the VBA editing window will appear.

BY using the F8 key you can step through your code line by line. If there is a runtime or syntax error, you will be able to see which line produces the error.

Go the the menubar Run > Reset to stop the running of the code. Click on the breakpoint to turn it off.

While in the VBA Editor window, you can hover your cursor over a variable and see what its current value is. This will work only for the module currently running and only prior to the line where the code is currently halted.


THe problem with using the LostFocus event for data verification is that newly entered data has already been saved to the table. If the new data doesn't meet with your criteria, you will have to take extra steps to either delete the data from the specific field or delete the record. Therefore the BeforeUpDate event is recommended as data has not yet been saved to the table.

Let me do a summation.

1. You have an Access form with a textbox ("InvoiceID100") in which string type data can be entered.
2. There is a back end table with a field by the same name.
3. You are attempting to use a DLookUp function to verify if the newly entered string data duplicates prior data.
4. Your back end tables are in SQL Server.

Now I need help with some confusion.

Your earlier posting states that the Data Type of InvoiceId100 is Numeric. You also indicate that this field is for invoice numbers.

First, users should not be assigning invoice numbers. There should be some sort of automatic mechanism (code generated or autonumbers) for doing so.

Second, where is a Text data type coming from? Are you changing the textbox's control source? Are you just ignoring the control source field's data type and entering ttext data into a numeric field? Are you trying to link back end numeric data types to front end text data types?

Since appropriate data types seem to be working, I am curious as to what you are trying to accomplish.

Cheers,
Bill

 

[sanan]

Hi formerTexan
Thank you so much for your Comment and your Info. On BreakPoint. It was very interesting.
Going Back to your Summation;

1. You have an Access form with a textbox ("InvoiceID100") in which string type data can be entered.
A: Yes I have a TextBox (“invoiceId100”), But It takes mostly Numeric Data, and it’s Control Source Name is (“InvoiceID”) with Data Type Numeric, But I tried data type nchar, And int , Which they seem working properly.

2. There is a back end table with a field by the same name.
A: Yes there is a back end Table, But the field Name is only (“InvoiceID”), With Data Type Numeric

3. You are attempting to use a DLookUp function to verify if the newly entered string data duplicates prior data.
A: Yes, But just when I was trying to answer this question, I noticed that the Data Type (“InvoiceId100”) is Numeric, Which sounds like a little conflict is developing.

4. Your back end tables are in SQL Server.
A: Yes

Now I need help with some confusion.

Your earlier posting states that the Data Type of InvoiceId100 is Numeric. You also indicate that this field is for invoice numbers.

First, users should not be assigning invoice numbers. There should be some sort of automatic mechanism (code generated or autonumbers) for doing so.
A: Yes you are correct, I already have a form for that, this one is for Out of Routine situations.

Second, where is a Text data type coming from? Are you changing the textbox's control source? Are you just ignoring the control source field's data type and entering text data into a numeric field? Are you trying to link back end numeric data types to front end text data types?
A: Here are the conflicts that I was talking about from your previous questions. The data type of my control is Numeric; Therefore, What would be your suggestion to correct this Situation? I am out of Ideas.

Best regards
sanan

 

[PHV]

the field Name is only (“InvoiceID”), With Data Type Numeric
If Not IsNull(DLookup("InvoiceID", "TableSales", "InvoiceID=" & Val(Me!InvoiceID100))) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanan]

Hi PHV
Bravo to you, It Finally works.
And Thanks so much to all you Guys who helped me on this Issue.
It is just Excellent.

This problem is solved, But few more still remains.
Another challenging issue has developed, when I want to close this particular Form.
I will start a new Thread for this problem it is called “Problem of closing a Form”
Hope to see you there.

Best Regards
Sanan