Auto filling a textbox from a combo box

S

stuck! again!

Hi,
i am trying to setup a cash register type database.

When i select an item to be sold (item) i want the corresponding Price from
(table products) to go into a text box, Price on the form. I then want to be
able to store this price in the table Purchases.

Any help at all on this would be greatly appreciated.

Thx
 
C

Chris B via AccessMonster.com

Make sure your table products list store the product as well as its price.
Your Purchases table needs to have the lookup combo box for the product name
and the price needs to be just a text box, make a form based on this.....
Get your items combobox to look up correctly, then in its "after update"
parameter place the following code (change the names as needed)
........................................
Private Sub item_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's 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
........................................................
Hope it helps....
 
O

Ofer

To add to Chris suggestion
If Item Number is string then change it to
strFilter = "ProductID = '" & Me!ProductID & "'"

And add the NZ function, incase the dlookup doesn't return any value, the
null will return an error

Me!UnitPrice = nz(DLookup("UnitPrice", "Products", strFilter),0)
 
S

stuck! again!

Hey,
thanks for your help. but im only new to access so...
where should i put the code exactly. in code builder or macro or expression
builder. im veddy veddy new to access.
sri but thx. any extra help would be great.
 
Top