Autofill Field

[Michael Dane]

Item Number Description Unit Cost
DC-1037 Large Dish $1,000.00
DC-1010 Medium Dish $500.00
DC-1017 Small Dish $250.00

The above table is a 3 column lookup column in a form. This form was
created from another table that collects all my data. What I want to have
happen is when I select say DC-1037 (Record Source - ItemNumber in underlying
table) from the lookup column that the unit price gets displayed on the form
and recorded in the underlying table that has a record source (UnitPrice).
Disregard description, it is only informational to verify the item number is
correct.

 

[Steve Schapel]

Michael,

The correct answer to this question depends on your answer to this
question... Can the Unit Cost for any Item in your lookup table change?
And if so, should the UnitPrice for that item in previously entered
records in your main table remain at the same value it was when entered,
or should the previously entered records also reflect the change in the
Unit Cost?

If the Unit Cost will not change, or if it can change and previous
records should reflect the change, then the answer is...
It is not a valid design of your table structure to have a UnitPrice
field in the main data table. Remove this field altogether. Whenever
you need this value for your purposes on form or report or for
calculation or whatever, it should be based on a query which includes
the lookup table. You can display the price on your form using an
unbound textbox on the form, by making the ItemNumber combobox 3
columns, and then in the Control Source property of the price textbox
you put the equivalennt of...
=Me.ItemNumber.Column(2)

If, on the other hand, the Unit Cost can change, but existing main table
records should remain as entered, then the answer is...
Make the ItemNumber combobox 3 columns, and in the AfterUpdate event of
this combobox, put code like this...
Me.UnitPrice = Me.ItemNumber.Column(2)