Zip Code auto populate City and State

[greedbegone]

Hello,

I have an Access 2007 database that i am building with the following:

Tables:
1. tblCustomer
2. tblZip

I have created a 1 to many relationship between them with the tblZip being the parent table. I have created a form with both tables. I have added customer name from tblCustomer, Zip from tblCustomer and then created 2 unbound text boxes named: City and State. The Zip field is a combo box. I have it set up to where I enter the zip code and the corresponding city and state auto populate the two unbound boxes. I have placed the following event on the Zip combo box:

After Update:

Private Sub Zip_AfterUpdate()

City = DLookup("City", "tblZip", "Zip = '" & Me.Zip & "'")
State = DLookup("State", "tblZip", "Zip = '" & Me.Zip & "'")


End Sub

Everything works great until I actually enter or select the zip code for the combo box... the moment i hit the tab key, it auto populates the city and state with the wrong "city" and "state" information. Along with that, it doesn't seem to update when i go back and enter a different zip code... Hopefully this makes sense... I would appreciate any help with this.

Thank you.

 

[dhookom]

Apparently you are assuming a zipcode has one unique city and state associated with it. I'm not sure it is true but let's assume it is.

I'm not sure why you "created a form with both tables". There is probably a Zip field in tblCustomer. If there is only one city and state associated with the zip, you should not have city and state fields in tblCustomer.

If you want to display the City and State, use a combo box cboZip to select the Zip with a row source like:

SELECT Zip, City, State
FROM tblZip
ORDER BY Zip

Set the bound column to 1 and the column widths to whatever you desire.

Use two text boxes on your form with control sources of:
=cboZip.Column(1)
=cboZip.Column(2)


Duane
Hook'D on Access
MS Access MVP

 

[traycee]

Zip Codes do not have one unique city where I live. Some small towns actually use the zip code of their larger sister city. Have you thought about typing in the name of the city and populating the state and zip from that field?

 

[dhookom]

I would think that more cities have multiple zipcodes than zipcodes have multiple cities. I could be wrong...

Duane
Hook'D on Access
MS Access MVP

 

[traycee]

There probably are more cities with multiple zip codes than zipcodes with multiple cities....unless you live in a small rural area. We have no cities with multiple zip codes but quite a few towns that share a neighboring towns zip code. Weird....I know.

 

[papadba]

If there may sometimes be a one-to-many and sometimes a many-to-one, you may want to simply keep all 3 columns in the tblCustomer.

You could then load the City, State, and Zip from tblCustomer.

Possibly i am missing something. . . .