Access Data Selection

[Guest]

I have a Form with a Subform attached. In the Subform I have the following
Fields:- Product Info (Combo Box), Unit/Metre Price (Currency), Qty Used
(Number) and Total (Calculation). What I want to do is have Access
automatically insert the Unit/Metre Price (already populated along with
Product Info on another table) once I have selected the relevant Product Info
from the Combo Box. This I cant seem to figure out, Any suggestions. Any
replies to (e-mail address removed)

 

[Graham Mandeno]

Hi Steve

The usual practice is to reply to the newsgroups. That way, others can
benefit from the reply and any ensuing discussion.

I'm assuming the following fields (or something similar) in your ProductInfo
table:
ProductID
ProductName
UnitPrice

....and that the table bound to your subform (OrderItems?) has the following
fields:
OrderID
ProductID
UnitPrice
Qty

Your combo box is bound to the ProductID field, and naturally you want to
record the UnitPrice of the product *at the time of the sale* in the
OrderItems table.

So...

Your combo box needs three columns, and a RowSource such as:

Select ProductID, ProductName, UnitPrice from ProductInfo
order by ProductName;

Set the BoundColumn property to 1 (ProductID) and set the ColumnWidths
property to:
0;;0

This means "hide the first and third columns (width zero) and allow the
second column to use all the available space".

Now, when you select a product from the combo box, its bound column
(ProductID) will be stored in the bound (ControlSource) field (ProductID).
Further, the current UnitPrice will be available in the hidden third column,
so you need a single line of code to transfer it to its destination field,
after the value has been selected:

Private Sub cboProduct_AfterUpdate()
Me!UnitPrice = cboProduct.Column(2)
End Sub

[Note that the Column property starts numbering from zero, so the third
column is .Column(2)]