pick up two controls in combo box

[Barry Broome]

i have 2 tables, 1)Price list with fields itemcode,
description and price. 2) transaction list with fields,
itemcode, price, quantity and extended value. in a form
to populate the transaction table i want to select in a
combo box on description and automatically populate the
item code and price from the price list. How do i do
this?

 

[Sandra Daigle]

Hi Barry,

This is pretty easy to accomplish. First add the UnitPrice field to the
rowsource query of the combo. Be sure to increase the ColumnCount property
of the combo in order to have access to the newly added field. This new
column will not be visible unless you also change the ColumnWidths property
and add a non-zero width to correspond with this column. Go ahead and do
this if you want to see the unitprice in when the combo is expanded. Also,
the combo should be bound to the ItemCode field of your second table
(orderdetails?). Description should not be stored in the second table.

Your rowsource query would be something like:

Select items.Itemcode, items.description, items.Unitprice
From items
Order by Items.Description;

Other properties

ColumnCount Property = 0
ColumnWidths=0,1,0
ControlSource=ItemCode

Let's say that UnitPrice is now the 3rd column in your rowsource query. Now
create an AfterUpdate event for the combo - it just needs to have the
following assignment. Notice that this assignment is actually pulling the
value from the 3rd column since the column property is indexed starting with
0.

me.UnitPrice=me.ItemCode.column(2)