Linked tables and fields...

[jgi]

I am trying to create a database that links up a Firm Name, a Firm Office, and Firm Address. When the user chooses a Firm Name (let's say "Law Firm&quot, the table then formats the next field to the different offices the firm has (let's say "Chicago," "New York," and "Los Angeles&quot. The next field then lists the address of the office that the user chose. Let me map it out for clarity.

Law Firm
--------> Chicago
New York
Los Angeles
(user chooses New York)
------------> 1234 Broadway, New York, NY

This is trying to be a predictive table depending on what the user chooses. I am unsure, however, how to set this up entirely. Please swiftly help me out as a Christmas gift. Thank you in advance.

jgi.

 

[jgi]

Oh, and I would love to do this in a Table and not in a Form...

 

[dhookom]

This can't be done in a table, only in a form.

Signed,
Grinch

 

[ptpaton]

jgi,

Here's how I would take this on. Have at least two tables. One stores the data for selecting the law firm. The second stores all the offices with their cities and addresses.

Build a form based on a query with the fields from the law firm table, but bring the address in from the addresses table. I'll explain more later.

As Grinch has already said, this can only be done in a form. You can set up a series of combo boxes with one of the fields in the criteria of the row source set to the previous combo box. Then, when you select your law firm, the values in the next combo box are limited to only those records that have the specific law firm. At the second combo box, select the city. Set the control sources for the combo box equal to the corresponding fields in your law firm table.

Now, have a text box that is based on the address that you brought in through the query. If it's set up right, the address for the city and law firm selected will pop right in, and your done.

Make sure that your combo boxes record what the user has selected, and write those values to the table.

Give it a shot and write back with questions. Good luck!





-Patrick

Nine times out of ten, the simplest solution is the best one.