DLookup with Filter string

[Chuck712]

I have a database with a table for products that includes a unit price for each prodcut and another table that records the Order Detail for each product ordered.
The order detail form is based on a query that automatically fills in the product namd from the product ID number that is entered on the form, however I cannot get the unit price to fill in on the rest of the form.
Here is my code:

Private Sub ProductID_AfterUpdate()
Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
'Look up products unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("[UnitPrice]", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub
Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub

What I keep getting is an error that states the object doesn't contain the Automation object. "C101'. (In this case, C101 is the product number that I have entered on the form.
Any help would be greatly appreciated.
I am working in Access 2000
Chuck

 

[jimmythegeek]

Try changing this line:

strFilter = "ProductID = " & Me!ProductID

To this:

strFilter = "ProductID = '" & Me!ProductID & "'"
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Chuck712]

Beautiful. I do not understand, but it works.
Sorry I guess that my theory was not crazy enough!
Have you got time for a quick explination as to why that worked?
Chuck

 

[jimmythegeek]

Whenever the field you are setting the criteria on, is a text field, your actual criteria value (in your case C101), must be surrounded by single quotes. Numbers do not have to have quotes, but strings do. So by using this line:

strFilter = "ProductID = '" & Me!ProductID & "'"

strFilter will equal this:

ProductID = 'C101'

Because you are concatenating a single quote before and after the value itself.

Hope that helps. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Chuck712]

Thanks. It makes sense now.