Query in VBA by criteria on form

[kayjenx]

So I have a small database for managing stationery orders (internally with my company - I order from the stationery dept). In the orders forms i have a subform with the list of items being ordered. I have the Item_ID, description, UnitsOrdered,UnitPrice, TotalPrice.

What a code that will lookup the Item_ID i have entered in the record, and then give me the UnitPrice from the Table Named "Stationery Items". This value is captured in an unbound textbox named UnitPriceRes. This value will then be updated through vba to become the value for UnitPrice. Which is then multiplied by the UnitsOrdered to give you the value for the the TotalPrice.

Here is the code I am using but the field remains Blank. (UnitPriceRes and thus UnitPrice)

Private Sub Item_ID_AfterUpdate()
Dim varX As Variant
varX = DLookup("UnitPrice", "Stationery Items", "[ItemID] = [Item_ID]")
Me.UnitPrice.Value = Me.UnitPriceRes.Value
End Sub

Private Sub UnitPriceRes_AfterUpdate()
Me.UnitPrice.Value = Me.UnitPriceRes.Value
End Sub

Private Sub UnitsOrdered_AfterUpdate()
Me.TotalCost.Value = Me.UnitPrice.Value * Me.UnitsOrdered.Value
End Sub

What am I doing wrong and what can I use instead. I was thinking I could try to do a select Query in vba instead on Dlookup but i am not familiar with how to do that.

[AndreRet]

Have a look at the following links.

http://www.blueclaw-db.com/select_case.htm

Or

http://objectmix.com/ado-dao-rdo-rds/309076-trying-write-select-statement-vba;-access-2002-a.html