Data Type Mismatch with DLOOKUP

[Nerida Menzies via AccessMonster.com]

I am trying to compare a employee code that is entered into a form with a
list of existing employee codes to make sure employees are not entered twice.

Using the On Exit event I am using a bit of code I found in Access’ help:

Private Sub ServiceNo_Exit(Cancel As Integer)

If (Not IsNull(DLookup("[ServiceNo]", "tblParticipantDetails", _
"[ServiceNo]='" & Me!ServiceNo & "'"))) Then
MsgBox ("ServiceNo has already been entered in the database.")
Cancel = True
Me!ServiceNo.Undo
End If

End Sub


I keep getting a “Run time error 62506 – Data Type Mismatch in criteria
expression. I know the problem likes in the last part of the DLOOKUP, maybe
something to do with the single and double quotes, but I just can’t seem to
crack it. I have spent hours trying to work out what the problem is. If
anyone can help I’d most appreciative.

Cheers,
Nerida.

 

[Rick Brandt]

I am trying to compare a employee code that is entered into a form
with a list of existing employee codes to make sure employees are not
entered twice.

Using the On Exit event I am using a bit of code I found in Access’
help:

Private Sub ServiceNo_Exit(Cancel As Integer)

If (Not IsNull(DLookup("[ServiceNo]", "tblParticipantDetails", _
"[ServiceNo]='" & Me!ServiceNo & "'"))) Then
MsgBox ("ServiceNo has already been entered in the database.")
Cancel = True
Me!ServiceNo.Undo
End If

End Sub


I keep getting a “Run time error 62506 – Data Type Mismatch in
criteria expression. I know the problem likes in the last part of
the DLOOKUP, maybe something to do with the single and double quotes,
but I just can’t seem to crack it. I have spent hours trying to work
out what the problem is. If anyone can help I’d most appreciative.

Cheers,
Nerida.

Your expression has single quotes around the final argument value. If ServiceNo
is a number and not text, then you need to eliminate those. You need to keep
the double quotes, just remove the single ones.

 

[kiln]

Hi

The ServiceNo is a text value right? Nothing looks wrong with your
dlookup to me either, except that you might use a variable in the place
of Me!ServiceNo...I think domain lookup calls sometimes can have a hard
time resolving form refs as params. Hope it helps?

 

[Nerida Menzies via AccessMonster.com]

You must be an angel from heaven! It works like a dream.

Kind regards,
Nerida.

I am trying to compare a employee code that is entered into a form
with a list of existing employee codes to make sure employees are not

[quoted text clipped - 22 lines]

Cheers,
Nerida.

Your expression has single quotes around the final argument value. If ServiceNo
is a number and not text, then you need to eliminate those. You need to keep
the double quotes, just remove the single ones.