Really you should have separate tables for each, Zips, Cities and States, the
first referencing the second on CityID (city names can be duplicated so use a
unique number as the key), the second referencing the third on StateID (a
unique numeric key isn't really needed here as state names are not
duplicated, but many people prefer to use one nevertheless). This assumes
each Zip relates to only one city, which is implied by your question of
course.
Having a single table for Zips and Cities introduces redundancy as where
there is more than one Zip for a City the table would contain the information
that that city is in a particular state more than once. This would allow for
inconsistent data in that the same city could be incorrectly placed in
different states in separate rows of the table. Having all three in one
table would only compound the problem of course.
You don't need to include a City or State column in the main referencing
table, and to do so introduces redundancy and the possibility of inconsistent
data. Just include the Zip column and for its combo box's RowSource use a
query which joins the three tables, e.g.
SELECT Zip, City, State
FROM Zips INNER JOIN
(Cities INNER JOIN States
ON Cities.StateID = States.StateID)
ON Zips.CityID = Cities.CityID
ORDER BY Zip;
Set the controls ColumnCount property to 3.
Include unbound text box controls on the form for city and sate with
ControlSource properties respectively of:
=cboZip.Column(1)
and:
=cboZip.Column(2)
where cboZip is the combo box bound to the Zip column in the form's
underlying table. The Column property id zero-based so Column(1) refers to
the second column and so on.
You might be interested in a demo of how to handle this sort of data using
correlated combo boxes, which can be found at the following link:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
The demo uses the local administrative units of Parish, District and County
in my area, but the principle is the same. It differs from what you want to
do in that it allows the units to be selected from the top down, starting
with County, then selecting from Districts in the selected County, then from
Parishes in the selected District, but at the same time maintaining the
normalized structure of the referencing table by storing only the ParishID.
BTW storage of addresses by PostCode (usually along with house number or
name)is common here in the UK as our post codes relate to tightly defined
areas, my own for instance pinpointing the location to one side of my street
(35 houses). There is thus no need to include any other location data.
Ken Sheridan
Stafford, England
