Auto populating City and St. based on zip code

  • Thread starter Thread starter X
  • Start date Start date
X

X

I was surfing the web and found a third party product
that would allow me to enter a zip code and have the
accurate City and State field auto populate based on the
United States Postal Service standards. After discussing
this with a friend he insisted that I can build this
feature all by myself in my database. Can anyone shed
light on where to begin.

Thanks!!!!!
 
Yes, you can do it yourself. To begin with, you need a listing of zip codes
and their associated cities/states. You would then enter a zip code in the
zip code control on a form. In the After Update event of the zip code
control, you would use the DLookup function to find the associated city and
state and assign those values to the city and state controls. The problem
with these systems is that they give you the city and state of the post
office, not necessarily the city and state of the address. This doesn't
cause a problem with the mail, it will get there, but if you're looking on a
map for the location, you may be looking in the wrong city.

Another option is to skip the premade table and just start entering values
yourself. You could then use a combo box for the city and state (to take
care of the problem listed above) and when you enter the zip code, requery
the two combo boxes using the zip code as criteria. You would search the
records you've already entered and that would fill in the combo boxes' drop
downs. If there was only one value returned, assign that value to the combo
box. If there was more than one value returned, leave the combo box blank
for the user to select from the available entries or add a new one that you
may not have there yet.

The second method saves you the trouble of buying a listing of zip codes
that you may never use, you will build a listing of the zip codes you use on
a regular basis in a short time.
 
Just a caution: some cities have multiple ZIP codes, and some ZIP codes have
multiple cities, so what you want to do is not as simple as it might seem,
in the general case. You may want to consider that once a ZIP code is
entered, determining if there is more than one city associated with it, and
displaying the city/state in a list or combo box so the user can choose.

I like Wayne's approach for building your own list if you have the kind of
application where that is possible -- such as dealing with your own
(particularly if they are local) customers.

Larry Linson
Microsoft Access MVP
 
Back
Top