DLookup Runtime Error

[MuskokaMad]

Thank you in advance for your assistance.

I am a relative Access 2007 Newbie and am having a problem with a form I created.

This is a variation on a Order Database.

I have a table Products with a field RetailPrice that gets its value from a table BoxStyle from a field BoxStyleRetailPrice in an On Change Event. I have used this code.

Private Sub CabinetStyle_Change()
RetailPrice = DLookup("BoxStyleRetailPrice", "tbl_BoxStyle", "BoxStyle_ID=" & CabinetStyle)
End Sub

This returns the value I need.

In a later form I need to retrieve the RetailPrice Value from the Products Table for a field in the OrderDetails table called Price.

In a SubForm Called OrderDetails I am trying to use the same On Change Event in The ProductID Field to get the value I need.

I have tried this code

Private Sub Product_Change()
Price = DLookup("RetailPrice", "tbl_Products", "Product_ID=" & Product)
Quanity = 1

End Sub

I get an error.

Run-time error `3075`:

Syntax Error in query expression 'Product_ID= '.

Not sure what I am doing wrong.

Thanks again for your help

Jason

 

[vbaInet]

Your Product_ID field must be a String data type. This is how you wrap values that are of that type:

Price = DLookup("RetailPrice", "tbl_Products", "Product_ID = [COLOR=Red]'[/COLOR]" & Product & "[COLOR=Red]'[/COLOR]")

Notice the single quotes in red.

By the way, be a bit weary with doing a DLookup on the On Change event. Things could get considerably slow.