Auto Post Zip based on City?

[Guest]

I was wondering if there was anyway I could have my database automatically
post a matching zip code based on city into the zip field, or even maybe
enter zip and have it post city and state in city field and state field. If
so, would I put this into the table or the form?

Sherry

 

[Rick B]

Yes, but you would have to have the zip code table loaded in your database.

Note that you can't do the first option. If I enter Houston, TX there are
about 40 ZIP codes that could match, you could go the other way though.

It would involve doing a lookup from the ZIP table after you enter the ZIP
code in a form.

 

[Guest]

Thanks for your response. Yes, I see where I wasn't thinking logically on the
first option, however, the second is feasible for me, I'm just not sure how
to make it work. I'm not very well versed in Access, but I'm the only person
in my company who knows how to use it at all.

I know I could do a lookup, but how would that automatically post city in a
city field and state in a state field. Wouldn't there have to be some kind
of IF expression built in?

Thanks,
Sherry

 

[Rick B]

First things first. Do you have the full ZIP file saved in your database in
a table?

If so, you would add code to your ZIP field in your form that says, "go out
and find this zip in the table and then pull the city entry into the city
field on my form, pull the state entry to the state field on my form.

A good example of this is in the Northwind database. When you select a
product, the description, price, etc are pulled.

One way to do this is to add a combo-box to your form that is based on your
ZIP table. Include all three fields in the combo-box (you don't have to
actually see all three). Then your city and state fields can simply grab
the value from your combo-box's second and third column.

 

[Guest]

I hate to burst that bubble also but often smaller cities share a zip code.
In our local area both Belleville and Swansea had the same Zip. Swansea,
being a little uppity, did not like their mail being addressed as Belleville.
Eventually they got big enough for the USPS to assign them their own a couple
of years ago.