ZipCode Lookup and field fill

[Guest]

I would like to be able to type a zip code into a form, and have it
automatically return the City and State into their respectiver fields, or
enter the city and state and have it return the zip code.

I have one table which contains Zip, City, and State fields which is the
master listing for the Zip Codes.

The other table is where I would like the data to be stored after it is
entered and looked up. This table is basically a contacts table with name,
address, phone, etc.

I have been trying this for about two weeks and can't get it to work. Any
help is appreciated, thanks in advance.

 

[jahoobob via AccessMonster.com]

You don't have to store the city and state in the other table as you can
always find them through the ZIP. Base all your lookup forms and reports
(mailing labels, etc.) on a query based on both tables with the link between
ZIPs.

 

[Jeff Boyce]

This approach doesn't always work.

We have a pair of adjacent municipalities in my area that share a single zip
code. Storing the zip code only would NOT resolve which city.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jahoobob via AccessMonster.com]

Yes, a better way would be to index all the cities ZIPs and store the index
in the main table but from what Scott stated, that he will type a ZIP and a
city and state will be stored, indicates he has only one-to-one city to ZIP.
I used to live in Pace, FL 32571, however, if you put 32571 in any on line
registration it will show up as Milton, FL. It really doesn't matter what
the city, the PO delivers by ZIP.
I haven't tried it but I believe you could put Timbuktu as the city and 20500
as the ZIP and your mail will get to the White House.

This approach doesn't always work.

We have a pair of adjacent municipalities in my area that share a single zip
code. Storing the zip code only would NOT resolve which city.

Regards

Jeff Boyce
Microsoft Office/Access MVP

You don't have to store the city and state in the other table as you can
always find them through the ZIP. Base all your lookup forms and reports

[quoted text clipped - 16 lines]

 

[Guest]

In reference to the comment that several smaller towns may fall under one
zipcode reaffirms my need to be able to store the city/state/zip in the main
table so I can change the city in such cases.

Thanks

Yes, a better way would be to index all the cities ZIPs and store the index
in the main table but from what Scott stated, that he will type a ZIP and a
city and state will be stored, indicates he has only one-to-one city to ZIP.
I used to live in Pace, FL 32571, however, if you put 32571 in any on line
registration it will show up as Milton, FL. It really doesn't matter what
the city, the PO delivers by ZIP.
I haven't tried it but I believe you could put Timbuktu as the city and 20500
as the ZIP and your mail will get to the White House.

This approach doesn't always work.

We have a pair of adjacent municipalities in my area that share a single zip
code. Storing the zip code only would NOT resolve which city.

Regards

Jeff Boyce
Microsoft Office/Access MVP

You don't have to store the city and state in the other table as you can
always find them through the ZIP. Base all your lookup forms and reports

[quoted text clipped - 16 lines]

I have been trying this for about two weeks and can't get it to work. Any
help is appreciated, thanks in advance.

 

[John W. Vinson]

In reference to the comment that several smaller towns may fall under one
zipcode reaffirms my need to be able to store the city/state/zip in the main
table so I can change the city in such cases.

That's certainly how I do it. Zip to City is in reality a many to many
relationship: one zip can cover several cities and many larger cities have
multiple zips.

If you go to the USPS Zipcode directory online

http://zip4.usps.com/zip4/citytown.jsp

you will see that multicity zips have a "preferred city" but if the zip is
correct it will be delivered whichever city is named; and depending on the
nature of the mail and the feelings of the recipient, it might be important to
use the actual city where the person lives rather than the name of the city
where their post office stands.

John W. Vinson [MVP]

 

[jahoobob via AccessMonster.com]

If you type in a ZIP (as you say) and you have multiple cities for any one
ZIP the first city will always be the one you get.

In reference to the comment that several smaller towns may fall under one
zipcode reaffirms my need to be able to store the city/state/zip in the main
table so I can change the city in such cases.

Thanks

Yes, a better way would be to index all the cities ZIPs and store the index
in the main table but from what Scott stated, that he will type a ZIP and a

[quoted text clipped - 20 lines]

 

[Guest]

I understand that it will pick the first match, which in most cases will be
the one I need. However, the problem at hand is how to get it to populate
the fields with the information in the first place.

If you type in a ZIP (as you say) and you have multiple cities for any one
ZIP the first city will always be the one you get.

In reference to the comment that several smaller towns may fall under one
zipcode reaffirms my need to be able to store the city/state/zip in the main
table so I can change the city in such cases.

Thanks

Yes, a better way would be to index all the cities ZIPs and store the index
in the main table but from what Scott stated, that he will type a ZIP and a

[quoted text clipped - 20 lines]

I have been trying this for about two weeks and can't get it to work. Any
help is appreciated, thanks in advance.

 

[Jeff Boyce]

Scott

In the AfterUpdate event of the combobox, add something like:

Me!txtCity = Me!cboZip.Column(1)
Me!txtState = Me!cboZip.Column(2)
...

where the SECOND field in your combobox's quey is the City and the THIRD is
your State (.Column() is zero-based).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Scott Whetsell, A.S. - WVSP"

I understand that it will pick the first match, which in most cases will be
the one I need. However, the problem at hand is how to get it to populate
the fields with the information in the first place.

If you type in a ZIP (as you say) and you have multiple cities for any
one
ZIP the first city will always be the one you get.

In reference to the comment that several smaller towns may fall under
one
zipcode reaffirms my need to be able to store the city/state/zip in the
main
table so I can change the city in such cases.

Thanks

Yes, a better way would be to index all the cities ZIPs and store the
index
in the main table but from what Scott stated, that he will type a ZIP
and a
[quoted text clipped - 20 lines]
I have been trying this for about two weeks and can't get it to
work. Any
help is appreciated, thanks in advance.