DLookup Help

[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.

 

[Beetle]

There are several problems here. Comments in line;

If IsNull(Machine) Then

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

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)

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

 

[Jamie Dickerson]

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?

 

[Beetle]

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.

 

[Jamie Dickerson]

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.