I need multiple column inserts from a single combo box lookup

[Guest]

I have implemented a combo box lookup on a dealer name. When I select an
individual dealer, I want his Account number and address to be automatically
inserted into their own individual columns.

It does not appear possible. Can anyone help? I appreciate all your
comments.

 

[Rick B]

Are you sure?

In most cases, that would not be proper design. You will be duplicating
data. If the data is stored in a separate dealer table, then it would be
redundant to store it again in a detail table of some kind. If they end up
with 100 records, then you'd have their address in your database 101
times!!!!

A normalized relational database would say that you should simply relate the
two tables.

Now, there are cases where you'd do what you want. If you want to store the
address used at the time of the "order" and you don't want to have updates
affect it, it would make sense. BUT, unless they change their address every
few weeks, I can't see much reason to do that. A more common case where
you'd do that is if you were storing some value that changed almost daily
(price paid for a gallon of gas, for example).

 

[Guest]

Thank you Rick. I will have to rethink my idea.

Blake

 

[Pat Hartman\(MVP\)]

If you want to see additional columns from the lookup table on your form,
the best solution is to create a query that joins the main table to the
lookup table. You can then select columns from both tables. Use this query
as the RecordSource for your form/report. You will be able to select the
new columns from the field list and bind controls to them. When you select
something from the combo, Access will automatically populate the fields from
the lookup table. When using this method, it is best to set the Locked
property to Yes for all controls with data from the lookup table (except the
combo of course). This will prevent accidental changes to the lookup table.

 

[Duane Hookom]

Another method is to include the extra columns in the Row Source property of
your combo box. You can then add a text box with a control source like:
=cboMyCombo.Column(2)
This text box would display the 3rd column from the combo box.

As Pat suggested, the text box should be locked and possibly disabled. I
usually set the forecolor of these "looked up" values to something other
than the text boxes that can be edited.