Runtime 3075 Syntax error

2
votes

I'm trying to build an ordering system in the style of northwind in access 2010 but slightly less complicated! I need to bring the price of the product from the inventory to the order details subform when the product is selected from the combo box, so being a novice my code isn't working properly...

Private Sub Product_AfterUpdate()
Dim PriceX As Currency, UnitX As Currency
PriceX = DLookup("Unit Price", "ProductInventory", "[ProductInventory].[Product]=" & [Product].Value)
UnitX = DLookup("Unit", "ProductInventory", "[Product] =" & [Product].Value)
Unit_Price.Value = PriceX
Unit.Value = UnitX
End Sub
1
ms-accessvbams-access-2010
In the future, please give details about the error message. We aren't all as brilliant as @HansUp! - Smandoli
It's easy to be generous when the posted answer solved my problem. Happy 2016 to you. - Smandoli

1 Answers

4
votes

I suspect the full text of the error message was "Syntax error (missing operator) in query expression 'Unit Price'."

You're looking up a value in a field named Unit Price. Since the field name includes a space, enclose it in square brackets to eliminate the error.

PriceX = DLookup("[Unit Price]", "ProductInventory", "[Product]=" & [Product].Value)

If you get a different error after that one, please give us the full text of the error message and indicate which line in your code triggered the error.

From the current error you reported, it seems [Product] is text instead of numeric data type. So add single quotes around the value in the last part of the DLookup expression.

PriceX = DLookup("[Unit Price]", "ProductInventory", "[Product]='" & [Product].Value & "'")