Referring to a field value in a different record

[Guest]

I have a situation where I have a form that contains a query of a table of
items with pricing, units, etc. There are some like records in this table
where the only difference is the quantity in units and the associated
pricing. That quantity is listed in a unit field. There is a reference
field to list the id of the base record (with a unit of 1) for each different
type of item. I am trying to have a button on the form to automatically look
at the units qty field in the current record and multiply it by the price
field in the referenced (base) record and place that result in the price
field of the current record. I am trying to use Access VB and have tried SQL
statements but have found that the DoCmd.RunSQL statement does not allow SQL
select statements. Does anyone have a solution for this?

 

[Lee Robinson]

I would write it this way:

Private sub btnGetPrice_click()
Dim Quantity as Integer
Dim ItemPrice as currency
Dim ExtendedPrice as currency

Quantity = Me.txtQuantity
ItemPrice = nz(dlookup("ItemPrice","BaseRecord","RecordID = " &
Me.BaseRecordID),0)
ExtendedPrice = Quantity * ItemPrice
Me.txtExtendedPrice = ExtendedPrice
End Sub

I include the NZ( ,0) in case the DLOOKUP doesn't find a record.

Lee Robinson