Autofill City and County info based on Zip Code entered

[Tim Atkins]

I am using Access XP. I would like to simplifiy the data
entry on my form. I have a field for City, County,
State, and Zip Code. The State will default to WI, but I
would like to have the City and County fields
automatically fill in based on what information is
entered in the Zip Code field.

In Excel, I did a simple VLOOKUP formula and had it fill
in the informaiton for me based on what Zip Code was
entered.

How can I do it in Access.

Thanks.

 

[Byron]

You will need a table to hold the City, State and Zip
Code information. Here's a suggestion:
Create a table named: tblCityStateLookup
Add the following fields:

DataID as AutoNumber, Primary Key
City as text
State as text, 2 char, input mask: >LL
ZipCode as text, input mask: 00000\-9999;;_

Save the table. Then add the data you need. Make an entry
for every zip code that you will need to use. This will
most likely require that there will be multiple entries
for some Cities.

Then in the AfterUpdate event of your ZipCode control,
add the following code. (watch out for work wraping) In
this code replace the "txtCity" with the name of your
control that will get the name of the city and
the "txtState" with the name of your control for the
state.

Dim varZip As String
varZip = Me.txtZip
Me.txtCity = DLookup
("City", "tblCityStateLookup", "[ZipCode]=""" & varZip
& """")
Me.txtState = DLookup
("State", "tblCityStateLookup", "[ZipCode]=""" & varZip
& """")

When you type in a zipcode that is in your table and
press tab or enter to leave the field, the Dlookup
function will lookup the appropriate values from your
table and place them in the correct text box.

HTH

Byron

 

[Tim Atkins]

Thank you so much. This works SWEET! Saved me many
hours of hair pulling, not to mention the timed saved
looking up and re-entering the same data over and over.

Thanks again.

Tim