DLookup Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called tblCUSTOMER with 3 fields: CUST_NO, CUSTOMER, ROUTE_NO.
I have a form with 3 unbound text boxes: txtCUST_NO, txtCUSTOMER,
txtROUTE_NO. Using the afterupdate event property, I want to populate
txtCUSTOMER and txtROUTE_NO when I key in the customer number in the
txtCUST_NO field.

When I enter a customer number in the text box, I get the following error:

Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field
with a value whose data type does not match the field’s data type.

The DLookup was written as such - can anyone help me?

Private Sub txtCUST_NO_AfterUpdate()

Me.txtCUSTOMER = DLookup("[CUSTOMER]", "tblCUSTOMER", "[CUST_NO] =" _
& Forms![frmERROR_HEADER]!txtCUST_NO)

End Sub
 
Don,

try this....


Me.txtCUSTOMER = DLookup("CUSTOMER", "tblCUSTOMER", "CUST_NO =" _
& Forms!frmERROR_HEADER!txtCUST_NO)


I have had similar problems using DLookup and found little help with the
actual format which is dependant on the types of data you are looking up.


If this doesn't work, refer to this webpage...It has helped me....

http://members.iinet.net.au/~allenbrowne/casu-07.html

Cheers
 
I have a table called tblCUSTOMER with 3 fields: CUST_NO, CUSTOMER, ROUTE_NO.
I have a form with 3 unbound text boxes: txtCUST_NO, txtCUSTOMER,
txtROUTE_NO. Using the afterupdate event property, I want to populate
txtCUSTOMER and txtROUTE_NO when I key in the customer number in the
txtCUST_NO field.

When I enter a customer number in the text box, I get the following error:

Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field
with a value whose data type does not match the field¢s data type.

The DLookup was written as such - can anyone help me?

Private Sub txtCUST_NO_AfterUpdate()

Me.txtCUSTOMER = DLookup("[CUSTOMER]", "tblCUSTOMER", "[CUST_NO] =" _
& Forms![frmERROR_HEADER]!txtCUST_NO)

End Sub
Click to expand...

Assuming Access isn't lying to you, I would have to venture that
[Cust_No] is not a Number datatype, as your syntax would indicate, but
text.

Try it this way:

Me!txtCUSTOMER = DLookup("[CUSTOMER]", "tblCUSTOMER", "[CUST_NO] = '"
& [Forms]![frmERROR_HEADER]![txtCUST_NO] & "'")

If this code is written on the "frmError_Header" form, use the Me
keyword to make it a bit more compact:

Me!txtCUSTOMER = DLookup("[CUSTOMER]", "tblCUSTOMER", "[CUST_NO] = '"
& Me![txtCUST_NO] & "'")
 
Back
Top