On Tue, 15 Jan 2008 07:30:00 -0800, MISSING LINK <MISSING
I HAVE A LIST OF PRODUCTS AND I WANT TO LINK THEIR UPC AND COST TO EACH
PRODUCT, SO THAT WHEN THEY ARE CHOSEN FROM A DROP DOWN LIST IN A FORM THE
CORRECT UPC AND COST FOR THAT ITEM WILL SHOW UP AUTOMATICALLY IN THE CORRECT
FIELD.
EX: (FIELDS') - PRODUCT / UPC / COST
(DATA) - HAT /000-000-000 / $ 5.00
Please turn off the caps lock key. It's hard to read, and considered rude - it
looks like SHOUTING.
That said... what is the Rowsource of the combo box? What is its Control
Source (the field into which the selected value will be stored)? I'd think
that you should have the UPC as the Control Source so that it will be stored
automatically. The product name should certainly NOT be stored redundantly in
any other table. You can *display* it on the form using a textbox with a
control source like
=comboboxname.Column(0)
to display the first column in the combo.
To actually copy the cost from the combo box into a Cost field (which *is*
valid and non redundant, since you want the cost as of the time the purchase
is made) use just a bit of VBA code in the combo's AfterUpdate event. View the
form in design view; select the combo; view its properties; click the ... icon
by the AfterUpdate property on the Events tab; and choose Code Builder. Access
will give you the sub and end sub lines for free; edit it to
Private Sub comboboxname_AfterUpdate()
Me!txtCost = Me!comboboxname.Column(2)
End Sub
using the actual name of your combobox for comboboxname and the actual name of
the textbox bound to your cost field for txtCost. The (2) means the *third*
column, since the column property is zero based.
John W. Vinson [MVP]
