Automatically Populate a City from a ZIP Code in Microsoft Access
Question details
Automatically populate or select a city after entering a ZIP Code in an Access form, while allowing for ZIP Codes associated with multiple cities.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An address form uses an existing list of ZIP Codes and cities to reduce manual city entry.
- Observed behavior
- The form should assign the city when there is one match and offer a choice when there are multiple matches.
Check that your ZIP and city data includes every valid pairing, and store ZIP Codes as Short Text so leading zeros are preserved. The example below uses a form named frmAddresses and assumes each city has a unique CityID.
Filter a city combo box by the selected ZIP Code
Use related tables and form events to select a single matching city automatically or let the user choose among multiple matches.
A junction table supports both ZIP Codes with multiple cities and cities with multiple ZIP Codes. The example stores CityID and ZIP Code in the address record and displays the state from the selected city.
In Access, create Cities with CityID, City, and State; ZIPCodes with a Short Text ZipCode key; and CityZips with CityID and a Short Text ZipCode. Make CityID and ZipCode a composite key in CityZips, then use Database Tools > Relationships to link each junction field to its parent table and enforce referential integrity.
Open frmAddresses in Design View and add a combo box named cboZipCode bound to the address table's ZIP Code field. Set its Row Source to SELECT ZipCode FROM ZIPCodes ORDER BY ZipCode; and set Limit To List to Yes.
Add a combo box named cboCity bound to the address table's CityID field. Set its Row Source to SELECT Cities.CityID, Cities.City, Cities.State FROM Cities INNER JOIN CityZips ON Cities.CityID = CityZips.CityID WHERE CityZips.ZipCode = Forms!frmAddresses!cboZipCode ORDER BY Cities.City; Set Column Count to 3, Bound Column to 1, Column Widths to 0;2;1, and Limit To List to Yes.
Select cboZipCode in Design View, open its Property Sheet, and choose Event > After Update > [Event Procedure]. Click the build button and add these lines inside the generated procedure: Me.cboCity = Null; Me.cboCity.Requery; If Me.cboCity.ListCount = 1 Then Me.cboCity = Me.cboCity.ItemData(0). Enter each statement on its own line; leave the existing Sub and End Sub lines in place.
In the form's Property Sheet, choose Event > On Current > [Event Procedure]. Add Me.cboCity.Requery inside the generated procedure so the city list reflects each record's ZIP Code without clearing its saved city.
Add an unbound text box and set its Control Source to =[cboCity].Column(2). Switch to Form View and test ZIP Codes with one, multiple, and no matching cities. For multiple matches, open cboCity and choose a city; for no matches, add the missing pairing to CityZips if appropriate.

Work with Office documents in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents and spreadsheets. It supports common Office formats for an easier move, but it does not replace Microsoft Access or run Access form events.

Frequently Asked Questions
Why should ZIP Codes be stored as Short Text in Access?
Short Text preserves leading zeros, such as the zero in 02108. Set the ZipCode field's data type to Short Text in Table Design, including the corresponding field in the junction table.
What happens when a ZIP Code matches more than one city?
The filtered cboCity list contains all matching cities. Because the After Update procedure assigns a value only when ListCount is 1, the user can open the city combo box and choose the correct one.
Why does the city list not update when I change the ZIP Code?
Check the ZIP combo box's After Update event in the Property Sheet and make sure it calls Me.cboCity.Requery. Also confirm that the Row Source references the actual form and control names.
How can I show the state for the selected city?
Include State as the third column in the city combo box's Row Source, then set an unbound text box's Control Source to =[cboCity].Column(2). Combo-box columns are numbered from zero.




