Linking Sub Tables

[Scott Connery via AccessMonster.com]

I am hoping thsi is a basic issue. i know I can do it easily in other data
bases. I want to create a relational relationship. That is that I want
information to flow through. I have the Countries in the Country table, the
Country table links to the State table. In this way I want the choice of a
State in the Main table to fill in the Country automatically. Currently
the table view I get a drop down menu that lists the numeric numbers for
the Countries in the order they appear in the State table. It appears I
have two problems - I can't get the field to auto complete (or fill in the
information provided in the Countryfield of States and connected to the
State chosen in the Mian table) and the Main table is providing the "value"
of the Coutries (as defined in the State table) not the "text".

Table: Main
KeyMain
Customer
State
Country

Table:State
StateKey
State
Country

Table:Country
KeyCountry
Country

 

[Guest]

Hi, Scott.

First of all, it sounds as if you are using the Lookup feature of Access
that is defined in Table Design view. Don't do it! See the following thread
for why.

http://www.mvps.org/access/lookupfields.htm

Also, do not *store* either the Country Code, the Country Name, or the State
Name in your Main table. All you need is the State code. The State Code's
relationship to States gets you the State Name and the Country Code, and the
States relationship to Countries gets you the Country Name. This is done by
foreign keys, which are entirely analogous to the physical key that opens
your front door. The numeric Country field in the States table "unlocks" the
Countries table by matching its primary key, and enables you to Access (no,
not a coincidence!) any other field in that table. Similarly for the numeric
State foreign key in your Main table.

It also sounds like you're editing the table directly rather than using a
form. This is unwise, because you have little control over what's entered,
and how.

The easiest way to implement what you want, which is to display the state
name and country name once a user has selected the state is to use a combo
box, whose Row Source is a query that selects the State Code, the State Name,
and the Country Name from the States and Countries tables.

Set the Bound Column to 1 to store the first column (the State Code) in your
main table. Set the first Column Width to 0" so that the State Code does not
display in the dropdown list, and so that the State Name displays once
selected. And use another textbox to display the Country Name, using the
Column property of the combo box. Set its Control Source to:

= MyComboBox.Column(2)

Hope that helps.
Sprinks