auto fill of zip code

[Richard Farrell]

I am trying to improve a database by adding so automatic
fetures. When you type your city i want the zip code to
automatically post in the zip code text box. It easy for
one or two cities the following code works.

Private Sub City_AfterUpdate()
If Me![City] = "Malden" Then [postalCode] = "02148"
End Sub

obviously I cant do this for all possible cities. Please
advise on a better stragety.

Thanks

 

[Mike Painter]

I am trying to improve a database by adding so automatic
fetures. When you type your city i want the zip code to
automatically post in the zip code text box. It easy for
one or two cities the following code works.

Private Sub City_AfterUpdate()
If Me![City] = "Malden" Then [postalCode] = "02148"
End Sub

obviously I cant do this for all possible cities. Please
advise on a better stragety.

Read the section in help about what a relational database is and look at
examples in help and the Northwind database.

You will need a related table to do this in a simple manner but that will
not solve the problem of many cities having more than one zip code.

My approach since the ZIP is normally known and the correct spelling may not
be is to be lazy and have the ZIP fill in the city name and state name with
a lookup by city if the person does not have the information.

 

[tina]

suggest you create a table for city/ZIP combinations. if you're dealing with
multiple states, you'll need to add a State field to the table also - there
are a lot of duplicate town names among the states.
also suggest you turn it around - enter the ZIP and have the city filled in
automatically. many cities have multiple ZIP codes, it's less likely that
one ZIP code will belong to multiple cities. you could go a step further. if
the user chooses to enter the city first, the relevant ZIP codes could be
offered in a combo box.
if you have a table of city/ZIP combinations, you can offer combo box
options (with Limit to List set to No, probably) for both city and ZIP,
which will help the user enter valid city names (spelled correctly) and
valid ZIP codes.

hth

 

[Tom Wickerath]

Hi Richard,

You've gotten some good advice from Tina and Mike Painter on entering the zip code and having the
city & state information filled in. You can download a demo zip code database from FMS to get an
idea of how to put it all together.

http://www.fmsinc.com/products/zipcode/index.html

You have to provide a little personal information to download the free trial, so that their
marketing folks can bug you at a later time, but its not too bad.

Tom
________________________________________


suggest you create a table for city/ZIP combinations. if you're dealing with
multiple states, you'll need to add a State field to the table also - there
are a lot of duplicate town names among the states.
also suggest you turn it around - enter the ZIP and have the city filled in
automatically. many cities have multiple ZIP codes, it's less likely that
one ZIP code will belong to multiple cities. you could go a step further. if
the user chooses to enter the city first, the relevant ZIP codes could be
offered in a combo box.
if you have a table of city/ZIP combinations, you can offer combo box
options (with Limit to List set to No, probably) for both city and ZIP,
which will help the user enter valid city names (spelled correctly) and
valid ZIP codes.

hth

________________________________________


I am trying to improve a database by adding so automatic fetures. When you type your city i want
the zip code to automatically post in the zip code text box. It easy for one or two cities the
following code works.

Private Sub City_AfterUpdate()
If Me![City] = "Malden" Then [postalCode] = "02148"
End Sub

obviously I cant do this for all possible cities. Please advise on a better stragety.

Thanks

 

[Mike Painter]

http://www.mikepainter.com/ZIP_Codes/zip_codes.php has a zipped access file
with a few odds and ends in it. The ZIPCODE table is the latest and has 45k
good codes.

 

[jwm]

Check Chris St. Valentine's site at http://www.csvtools.com/ Amongst the
free tools is a ZIP lookup routine that works very well...and accounts for
the fact that some ZIPs represent multiple locations.