Dlookup returning the wrong values

[Guest]

I have a Dlookup that's running without syntax errors -- but it's not
returning the correct values from the table. What it's supposed to do is
(Before Insert), check a person's full name against an existing Full Name
field and display a "This user's already in database" message. The problem
is, whether or not the user is in the database, the Dlookup always returns a
value (the same one each time).
Any idea what's going wrong here?

strFullName = Replace((Me![First Name] & " " & Me![Middle Name] & _
" " & Me![Last Name]), " ", " ")

varMyDlookup = DLookup("[Entity ID]", _
"Entity", "'Entity = " & strFullName & "'")

If (Not IsNull(varMyDlookup)) Then
intUserResponse = MsgBox(strEntityType & " " & strFullName & " is
already " _
& "in the database. Enter name anyway?", vbYesNoCancel)
If intUserResponse = vbYes Then
Exit Sub
Else
Cancel = True
Me![Last Name].Undo
End If
End If

 

[Douglas J Steele]

The quotes appear to be wrong in your DLookup. Exagerated for clarity, you
want:

varMyDlookup = DLookup("[Entity ID]", _
"Entity", " Entity = ' " & strFullName & " ' ")

You've got

varMyDlookup = DLookup("[Entity ID]", _
"Entity", " ' Entity = " & strFullName & " ' ")

which means that your WHERE clause is a string 'Entity = xxxxxxxx', which is
more or less meaningless.

 

[Guest]

Worked like a charm. Thanks!!

The quotes appear to be wrong in your DLookup. Exagerated for clarity, you
want:

varMyDlookup = DLookup("[Entity ID]", _
"Entity", " Entity = ' " & strFullName & " ' ")

You've got

varMyDlookup = DLookup("[Entity ID]", _
"Entity", " ' Entity = " & strFullName & " ' ")

which means that your WHERE clause is a string 'Entity = xxxxxxxx', which is
more or less meaningless.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a Dlookup that's running without syntax errors -- but it's not
returning the correct values from the table. What it's supposed to do is
(Before Insert), check a person's full name against an existing Full Name
field and display a "This user's already in database" message. The problem
is, whether or not the user is in the database, the Dlookup always returns a
value (the same one each time).
Any idea what's going wrong here?

strFullName = Replace((Me![First Name] & " " & Me![Middle Name] & _
" " & Me![Last Name]), " ", " ")

varMyDlookup = DLookup("[Entity ID]", _
"Entity", "'Entity = " & strFullName & "'")

If (Not IsNull(varMyDlookup)) Then
intUserResponse = MsgBox(strEntityType & " " & strFullName & " is
already " _
& "in the database. Enter name anyway?", vbYesNoCancel)
If intUserResponse = vbYes Then
Exit Sub
Else
Cancel = True
Me![Last Name].Undo
End If
End If