DLookup Help

  • Thread starter Thread starter Jamie Dickerson
  • Start date Start date
J

Jamie Dickerson

I have a form (Production Reports) where work performed by plant personnel is
recorded. I have a a field on the form (standard) where the quota must be
entered manually. I would like to have this field auto populate based on the
item# and machine ID. I have set up a table (Standards) with the following
fields:

Machine ID (number)
Item# (Text)
Standard (number)

I have placed the following code in the AfterUpdate Event of the machine
type combo box:

Dim myStandard As Long

If IsNull(Machine) Then
MsgBox "Please choose a machine."
Cancel = True
Exit Sub
End If

myStandard = Nz(DLookup("Standard", "tblStandards", "MachineID = " &
MachineID & -" AND Item = " & Item), -1)
If myStandard = -1 Then
MsgBox "Could not find standard value for this combination of Machine & Item
#."
Else
Standard = myStandard
End If


I am recieving runtime errors (type mismatch). The debugger highlights the
line containg the DLookUp function. Can someone please tell me what I am
missing, or what I have done wrong?

Thanks in advance.
 
There are several problems here. Comments in line;
If IsNull(Machine) Then
Click to expand...

It seems unlikely that the machine combo box would be Null
*after* it's been updated, so I'm not sure this check is necessary.
You may want to check if the Item is null though.
Cancel = True
Click to expand...

The After Update event does not have a Cancel argument, so this
line will do nothing, except maybe generate an undeclared variable
error.
myStandard = Nz(DLookup("Standard", "tblStandards", "MachineID = " &
MachineID & -" AND Item = " & Item), -1)
Click to expand...

You have an extra dash in your criteria (right before the " And)
and if the Item is text it needs to be delimited with qoutes. Also,
it seems unusual to convert the Null to -1 unless one of your
Standard values is zero.

Sample revised code below;

If IsNull(Me!Item) Then
MsgBox "Please select an Item"
Me!txtItem.SetFocus
Else
Dim MyStandard As Long

MyStandard = Nz(DLookup("Standard", "tblStandard", "MachineID=" _
& Me.txtMachine & " And Item=""" & Me.txtItem & """"), -1)

If MyStandard <> -1 Then
Me!Standard = MyStandard
Else
MsgBox "No Standard available for this record."
End If
End If
 
Thank you for your help. I managed to get rid of all the runtime and syntax
errors. Now my problem is that no matter what combination of Item and
Machine I enter I recieve my error message that there is no standard for the
combination. I have checked the table several times and there are standards
listed. Any suggestions?
 
It sounds like there is still something wrong with the
criteria in the DLookup and it is always returning -1.
You can verify this by temporarily adding another
message box right after the DLookup to see what it
is returning;

MsgBox MyStandard

If that's the case, try re-writing the DLookup using
one field at a time in the criteria and see if it returns
a valid Standard, then add the other field into the
criteria and test it again.
 
I was able to get it working. The problem was the names of my fields. The
names did not match the labels. Once I fixed that, it worked like a charm.
Your help is appreciated.
 
Back
Top