Intermitent DLOOKUP error

[garyholc]

Strange one this.

I have a form a user fills in. The form it set to only allow an addition. The user fills in a unique number and after filling in all the other fields, I direct them to a button which asks if they want to save the record.

If they decide to save it, I have a dlookup statement which says if the unique number already exists , i.e. DLookup>0 then return a message which says the number already exists and prompt the user to change the number.

99% of the time this works fine. However, on the odd occasion, it will say the number exists when it doesnt. I'm a bit lost as to why this is occuring as its only on the odd occasion.....

Anyone got any ideas?

 

[KenHigg]

Maybe it has something to do with a zero length string. Can you post the entire pc of code that does the validation?

 

[garyholc]

Hi Ken

Ok here is how it works. The form has two fields which together must equal a unique number, this is a change number and a version number, so for example, 12345 and version 2. I have a hidden field on my form which combines the two, this becomes 123452.

I have a query which contains a list of all the current change numbers and version numbers combined.

The Dlookup checks the value in the hidden field on the form against the values in the query to see if it already exists. Code is below:

' hidden field to contain the rfc number and the version number
Me.rfc_code = Me.RFC_Number & Me.Version_Number
' check if the rfc number is null
If IsNull(Me.RFC_Number.Value) Then
MsgBox "RFC Number cannot be empty", vbOKOnly + vbInformation, "Error"
Me.Version_Number.SetFocus
' if not check if the rfc number already exists
Else

If DCount("[check_no]", "q_check_rfc", "[check_no]= '" & Me.rfc_code & "'") > 0 Then
' if so tell the user and ask if they want to continue

If MsgBox("RFC " & Me.RFC_Number & " Version " & Me.Version_Number & " already exists. Click YES to go back and change the number, or NO to loose all changes and exit", vbYesNo, "Conflict") = vbNo Then


Obviously this is the main part of the code, I know the IF statements and endifs are not correct here.....

 

[KenHigg]

Hum...

Maybe there's one or more extraneous spaces at the front or the end of one of the strings. Try something like:

If DCount("[check_no]", "q_check_rfc", "trim([check_no])= '" & trim(Me.rfc_code) & "'") > 0 Then

???

 

[garyholc]

Ken

Good thinking.... will try it out!

Cheers

Gary