Autopopulate based on a selection for a form linked to a different

[Guest]

I have a combo box on a form that pulls from a different table, then posts
into the table linked to the form. I then need the data pulled in that combo
box to populate other fields and also to pop that info into the table linked
to the form. How do I do this?
Specifically, I have a form with some info on a case and I want to pull in
contact info for a contact person. The name of the contact person is a drop
down box based on another table that has all of the contact info. Then I need
the address, email, phone, and fax to autopopulate based on the selection of
name, and also to backfill the table that is the form source.
Hopefully this makes sense!
Thanks.

 

[Guest]

Victoria,

You are making a very typical error of new users, i.e., trying to duplicate
data that already exists authoritatively in one table into another. All you
need to store is the primary key of the RowSource of the combo box; all other
fields can be displayed in unbound form controls, and retrieved via a query.
That's the power of a relational database.

Say for example, you store the phone number of the contact in your Case
table, and let's say, a month from now, he moves and changes his phone
number. You'd have to update every case record with which he's associated,
AND his Contacts master record. On the other hand, if you store only the
primary key of the Contacts table in the Case table (there called a "foreign
key"), any changes are done once, in the Contacts table.

To *display* the other fields of interest, either:

1) base your form on a query that links the two tables by the
primary/foreign key, and include those fields of interest. Do not include
the primary key of the Contacts table or your recordset will not be
updateable. Then place textboxes for each of these fields of the query.

2) include those fields in the RowSource of the combo box, and use the
combo's Column property to display them in unbound textboxes, where the index
of the column begins with 0. For example, to display the 3rd column of the
textbox, set the ControlSource to:

=Me![MyComboBox].Column(2)

Hope that helps.
Sprinks