DLookup for saveing the correct value

[atisz]

I have an old Access 2000 database which needs some functionality update, and I can't get things working as they should.
The issue is related to frmOrderDetails form based on tblOrderDetails table, with following fields:

Order_ID
Product_ID
Ordered_Quantity
Product_Price

For entering ordered products there is a combo box for Product_ID which is displaying information about available products, their stock level, and price.
The Row source for Product_ID looks like this:

SELECT DISTINCTROW [qryStock4Orders].[Product_ID], [qryStock4Orders].[Product_name], [qryStock4Orders].[Available_quantity], [qryStock4Orders].[Selling_price] FROM qryStock4Orders ORDER BY [qryStock4Orders].[Product_name];

qryStock4Orders holds the calculated values of stock level (Available_quantity) for each product, grouped by Product_ID and Selling_Price. Selling_Price is from tblX.
Because I can buy one product from different suppliers (for different purchase prices) and purchase prices (so selling prices too) of the same product may vary over time, qryStock4Orders may list more then 1 available Selling_Price for that product, for example:

Product_ID Product_name Available_quantity Selling_Price
123 Product xyz 12 25$
123 Product xyz 42 29$
123 Product xyz 8 30$

What I want to achieve is to turn this code placed on After Update event of Product_ID combo box

Dim strFilter As String
    strFilter = "Product_ID = " & Me!Product_ID
    Me!Product_Price = DLookup("Selling_Price", "tblX", strFilter)

in something usable, in order to save into tblOrderDetails the selected Selling_Price and not one random Selling_Price from the 3 available above.

As the stock level is updated only after Ordered_Quantity and Product_Price are entered manually, what I'm trying to do with the code above is to automate the data input into Product_Price field of tblOrderDetails.

Can anyone point me to the right direction to solve this?

Thank you, Attila

 

[bob fitz]

This is untested "air code".
Perhaps something like:

Me!Product_Price = Me.Product_ID.Column(3)

in the After Update event of the combo box called Product_ID

 

[MarkK]

If I'm not mistaken, the price you need is present in the combo.column(3), so you could just do . . .

Private Sub ProductID_Click()
    Me.ProductPrice = Me.ProductID.Column(3)
End Sub

... or what bob said.

 

[atisz]

Thank you guys! It is so simple. I thought I should use DLookup