autofill in forms

Question

I have created a form from a table. 

FirstName

LastName

Address

City

State

Zip

I want to have the zip automatically filled in based on what I type in the City.  I have created a second table called Zip codes.

City

Zip

How do I accomplish this task.  I have read many forums.  I cannot find an answer.

Answer 1

That's very easily done by with your present set-up by basing the form on the following query, in which I've assumed that the first table is named Contacts:

SELECT FirstName, LastName,Address,

Contacts.City, [Zip Codes].Zip, State

FROM Contacts INNER JOIN [Zip Codes]

ON Contacts.City = [Zip Codes].City

ORDER BY LastName, FirstName;

But this is going to work for the following reasons:

1.  City names are not distinct, so are unsuitable as keys.  You would have to use a numeric surrogate CityID key of distinct values.

2.  US cities have many zip codes each, so Zip is not determined by City, so the Zip Codes table needs to be decomposed into four tables as follows:

Cities

….CityID  (PK)

….City

….StateID (FK)

States

….StateID  (PK)

….State

ZipCodes

….ZipCode  (PK)

CityZipCodes

….CityID  (FK)

….ZipCode  (FK)

The primary key of this last table is a composite one made up of the two foreign key columns.

The Contacts table should have a numeric CityID column in place of the City column, and the State column should be removed from the query as this is determined by CityID.  The form should, therefore be base on a query which joins Contacts, Cities and States.  The state will show automatically when you select a city in a combo box bound to the CityID column.  To select a zip code you will need a 'correlated' combo box bound to the Zip column, whose RowSource is a query which restricts the zip codes in the combo box's list to those for the selected city, as determined by the rows in CityZipCodes.  As it happens I've done exactly this recently in a project I've been undertaking on a pro bono basis for an organisation in the USA.

Answer 2

Well the first thing would be to change your structure.

tblPeople

PersonID (PK Autonumber)

Firstname

Lastname

StreetAddress

CityID (FK)

tblCity

CityID (PK autonumber)

City

Zip

State

Next you add a combobx on your form bound to the CityID field. the relevant properties of the combo would be:

RowSource: SELECT CityID, City, Zip, State FROM tblCity ORDER BY City;

Bound Column: 1

Column Count: 4

Column Widths: 0";2";1";.5"

When you drop down the City list it will show you a list of cities with their Zip and state for you to choose from. So you select the correct combination from that list.

Finally, you add two unbound text boxes to your form with Controlsources of:

=cboCity.Column(2)

=cboCity.Column(3)

where cbocity is the name of the combobox. These two with then display the Zip and State respectively. 

Note: that I left City, Zip and State out of your people table. This is because they are now redundant. Since they already exist in tblCity, they don't need to be repeated. When doing reports you will create a query that joins the 2 tables on CityID and then pull the text fields into your query. That is how relational databases work.