Dlookup issue

[AccessRockie]

Hi All,

I can't seem to get this Dlookup function to work.
Basically, I have table: "Registry", the field within the table I want to lookup is "Reference". The form is called "AddToRegistry" and the text inputted in the textbox "Reference" I want to check it against the field within the table for duplicates before saving. I had it working at one point but not anymore.

Any ideas.
Thanks in advanced.


Dim Answer As Variant
Answer = DLookup("[Reference]", "Registry", "[Reference] =" & Forms![AddToRegistry]!Reference)

If Not IsNull(Answer) Then
MsgBox "Reference Number Already Exists!" & vbCrLf & "Check the Number and Try Again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.Reference.Undo

Else:
End If

End Sub

[ssanfu]

If the field data type is TEXT, then try enclosing the form reference with single quote delimiters

Dim Answer As Variant

Answer = DLookup("[Reference]", "Registry", "[Reference] ='" & Forms![AddToRegistry]!Reference)& "'"

Instead of DLookup(), I would think about using query or DCount(). (You can use the DCount function to determine the number of records that are in a specified set of records.)

[AccessRockie]

Thanks for the reply.
I tried the code below.
Now I'm getting a Run-time error '3075' Syntax error in string in query expression '[Reference] ='A123456'.

If the field data type is TEXT, then try enclosing the form reference with single quote delimiters

Dim Answer As Variant

Answer = DLookup("[Reference]", "Registry", "[Reference] ='" & Forms![AddToRegistry]!Reference)& "'"

Instead of DLookup(), I would think about using query or DCount(). (You can use the DCount function to determine the number of records that are in a specified set of records.)

[roaftech]

I have been using the DLookup function recently - it can be a pain to get right.
This may sound unlikely but I have found that the full stop (.) and the exclamation mark (!) are not quite interchangeable so I would be inclined to change your second ! in your original formula to a . and see if that makes a difference.

[AccessRockie]

Tell me about it....a pain it is.
Still getting the same error.

I have been using the DLookup function recently - it can be a pain to get right.
This may sound unlikely but I have found that the full stop (.) and the exclamation mark (!) are not quite interchangeable so I would be inclined to change your second ! in your original formula to a . and see if that makes a difference.

[AccessRockie]

FIXED!!! Thanks I used the Dcount method.
Worked like a charm.

Thanks again.

Tell me about it....a pain it is.
Still getting the same error.