Retrieve CurrentPrice from UnitPrice

[slim]

I have an Orders form with an OrderDetails subform with
the following fields:
CustomerID, OrderDate, ProductID, Quantity, CurrentPrice,
Discount, LineAmount

The CurrentPrice field needs to retrieve an amount from
the UnitPrice in the Products table as soon as the
ProductID is inserted.

How the heck do I get this to work???

I've put in the following VB code using an On Enter Event
Procedure:
Private Sub CurrentPrice_Enter()
Dim mydb As Database
Dim productSet As Recordset
Dim ProductNumber As Integer
Dim UnitPrice As Double

Set mydb = CurrentDb()
Set productSet = mydb.OpenRecordset("tblProducts")

ProductNumber = [Form_frmCustomersOrderDetails
subform].ProdID.Value
productSet.Index = "PrimaryKey"
productSet.Seek "=", ProductNumber

UnitPrice = productSet!UnitPrice
[Form_frmCustomersOrderDetails subform].CurrentPrice.Value
= UnitCost

productSet.Close
End Sub

 

[Jess]

Hi

If I had to do this I would probably use a Combo box to
select by lookup the product being entered. The combo box
data would comprise three things viz. ID (the bound
field), description and UnitPrice. I would make column
width on ID and UnitPrice equal to ZERO so that only the
description is actually shown. On the After Update event
of the combo box I would insert the following code:

Me!CurrentPrice = Me![ComboBoxName].Columns(2)

The index at Columns(2) starts at ZERO so, being the third
field extracted, it would be found at position TWO.

HTH