DLookup not working

  • Thread starter Thread starter Design by Sue
  • Start date Start date
D

Design by Sue

My code is

If DLookup("Mechanic", "PartSuffixTbl", "Mechanic = " &
CLng(MechanicList.Column(0)) & "") = MechanicList.Value Then
MechanicList.SetFocus
MechanicBox.Value = ""

MessageBox = "This Mechanic cannot be modified because"
MessageBox2 = "there are Wheels assigned to it"
Me.refresh


This is to look in the Mechanic column of PartSuffixTbl and if the
MechanicList.Value is found, the following code takes place. I have and else
also but the problem is with the DLookup so I'm not posting that.
CLng(MechanicList.Column(0) returns the value correctly and so does
MechanicList.Value. But instead of running the code in the Then statement,
it is skipping to the Else statement. It is as if the code is not looking in
the table. Can anyone tell me what I am not seeing in this?

Thanks in advance.
Sue
 
If DLookup("Mechanic", "PartSuffixTbl", "Mechanic = " &
CLng(MechanicList.Column(0)) & "") = MechanicList.Value Then
Click to expand...

Looks to me that
& ""
would change a number like 10 to a string like 10". Not exactly sure how
Dlookup is interpreting it, but I'm guessing it is not looking for the
number 10 anymore.

Try this:

If nz(DLookup("Mechanic", "PartSuffixTbl", "Mechanic = " &
CLng(MechanicList.Column(0))),0) <> 0 Then
'Match found
 
Thanks so much - that worked. I was getting a type mismatch so I thought the
CLng changed it to a number, but guess not.

Sue
 
The Clng does change it to a number, Long data type to be specific. It was
the & "" that was causeing the problem.

However, the Clng function is not really necessary. Access will convert the
data type for you. You could just use
If DLookup("Mechanic", "PartSuffixTbl", "Mechanic = " & MechanicList) =
MechanicList.Value Then
 
I had already tried that and it gave an error message, Type Mismatch. The
Mechanic field is a number (it is an ID number that is assigned as an auto
number in another table.
 
Back
Top