Looking up price field in subform

[MThompson]

Hi,

I am creating a database with orders of products in access. I have tblcustomer, orders, orderdetails and stock.

How could i make it so that my Price field in my subform automatically looks up from the data in my tblstock.

Thanks.

 

[RickSpr]

You need to have an AfterUpdate event procedure attached to the control that holds the value you want to look up. Let's say that's a combo box named cboProductID, and that it holds a product number that matches the ProductID field in tblStock. The field you want to get from tblStock is named Price. Your AfterUpdate event procedure then looks like this:

Private Sub cboProductID_AfterUpdate()
    Me.Price = DLookup("Price", "tblStock", "ProductID = '" & Me.cboProductID & "'")
End Sub

What this does is look up the "Price" column in the "tblStock" table, in the row where the "ProductID" column has a text value equal to the contents of the cboProductID control. The value obtained is entered into the Price field on the form.

Note: If your ProductID (or whatever it's called) is a numeric value, rather than a text value, remove the two single quotes in the statement above. Rick Sprague