Assigning Data to Multiple Fields with a Combo Box

[SteveH]

I have a form with a combo box that looks up the item number, average cost,
and last cost from the items table. The item number is bound to the field in
the orders table. I would like for it to also populate the average cost and
last cost fields in the order table since those change frequently. How can I
do this? Thanks for any help.

 

[John Spencer]

If you have controls on the form bound to an AverageCost and LastCost fields
in the Orders table then you should be able to use the after update event of
the combobox to set the values. Assuming that the combobox has 3 columns
showing in order ItemNumber, AverageCost, and LastCost you would need
something like the following in the afterupdate event of the combobox.

Private Sub ComboboxItems_AfterUpdate()

'Column count is zero-based so first column is column zero
Me.TxtAverageCost = Me.ComboBoxItems.Column(1)
Me.txtLastCost = me.ComboboxItems.Column(2)

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have a form with a combo box that looks up the item number, average cost,
and last cost from the items table. The item number is bound to the field in
the orders table. I would like for it to also populate the average cost and
last cost fields in the order table since those change frequently. How can I
do this? Thanks for any help.

You can put VBA code in the combo box's AfterUpdate event. Include the desired
fields in the combo's rowsource and "push" them into textboxes bound to the
other fields:

Private Sub cboItem_AfterUpdate()
Me!LastCost = cboItem.Column(3)
<etc>
End Sub

The Column property is zero based so this would copy the *fourth* field in the
combo's row source query.

Do note that storing the *AVERAGE* cost is probably a bad idea - average over
what span? Wouldn't the average change every time the cost changed?