show record based on selection from combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables T_Owner and T_Owner Contact. Each owner may have 1-5
different contacts. I would like the user to select which owner from a combo
box and then be able to pick which contact from a another combo box - and
have the address, phone, etc for that contact filled in as applicable. Any
help would be appreciated.
 
Hi, smboyd.

By "fill in", I presume you mean to display the useful phone, fax, etc., and
not STORE it redundantly in the RecordSource of the current form. You need
only store the Owner and Owner Contact ID's.

I will assume a table structure like this:

T_Owner
T_OwnerID AutoNumber or Integer (Primary Key)
ContactName Text
....

T_Owner Contact
T_OwnerContactID AutoNumber or Integer (Primary Key)
T_OwnerID Integer (Foreign Key to T_Owner)
ContactName Text
....

I will also assume that the T_Owner and T_Owner Contact combo boxes on your
form *display* the name but store the numeric code, i.e., the Column Count =
2, the Bound Column = 1, the ColumnWidths = 0";x", x being some number.

Include any field you wish to display on the form in the RowSource property
of the 2nd combo box (you can use the wizard to do this). To limit the list
to those corresponding to only those associated with the current owner, add a
WHERE clause to the Row Source statement:

SELECT T_Owner Contact.T_Owner ContactID, T_Owner Contact.ContactName,
T_Owner Contact.Address, T_Owner Contact.City, T_Owner Contact.State, T_Owner
Contact.Phone
FROM T_Owner Contact
WHERE T_Owner Contact.OwnerID = Me!YourOwnerIDComboBox;

You will need to requery the combo box each time the owner changes, or the
user moves to another record (the first combo box' AfterUpdate event
procedure and the form's OnCurrent event procedure):

Me!MySecondComboBox.Requery

Hope that helps.
Sprinks
 
Ok - I think I understand that, but where would the address, city, state,
phone, etc. be displayed if they are all included in the row source of the
2nd combo box. I would like for them to be displayed in a different section
"field" of the form.
 
Oops.

Other columns can be displayed in another textbox by using the Column
property of the textbox. Columns are indexed starting with zero, so to
display the third column, set your textbox' ControlSource to:

=YourComboBox.Column(2)

In case you hadn't discovered it, you needn't show all of the columns in
your dropdown list, you can set their ColumnWidth to 0":

0";1";0";0" shows the 2nd column only.

Be sure also that the combo box' ColumnCount property is set to the total
number of columns as specified in your RowSource. The wizard will set this
automatically, but if you type in the RowSource manually, you'll have to set
it yourself.

Hope that helps.
Sprinks
 
Ok - all worked great.

Although, when the user opens the combo box it lists the contact names but
also shows the "space" for the rest of the columns even though it isnt
showing them. Is there any way to change this? I only have column 2 set to
..5", the rest are at 0"
 
Back
Top