How do I make MS Access fill in the city when I type the zip code in the form?

Dear Alene:

You would first need a table that gives the name of the city given the zip
code.

Unfortunately, there is sometimes more than one city or town with the same
zip code. In less populated portions of the country there can be several
towns with the same zip code. The zip code represents a single post office.
There could be only one post office for several small towns.

So, if you have:

John Smith
101 Main Street
55111

That is, not specifying the city or state, but using the zip code to
"lookup" the city and state, there's a possible problem. If there are 2
towns in that zip code, and they both have a Main Street, this address could
be for two different buildings in different towns.

Perhaps this is not terribly common, but not impossible.

You could still do this, but allow users to correct the City if its wrong.
It would then be quite useful 99% of the time.

Tom Ellison


"Alene" <(E-Mail Removed)> wrote in message
news:82B6ADB3-3B04-4B37-984E-(E-Mail Removed)...
> How do I make MS Access fill in the city when I type the zip code in the
> form?

I'd only add to Tom's excellent answer that Zip codes change and are
augmented by the Post Office, and they publish new files periodically
((monthly or thereabouts, if memory serves).

The easiest way to get up-to-date files in the format you want, if what the
Post Office provides is not your format, is to subscribe to a third-party
service that converts and provides the files on a schedule. One provider of
ZIP code data usable with Access is FMS, Inc.
http://www.fmsinc.com/products/zipcode/index.html. I have not used, nor
tried, their product so cannot offer an opinion, though I see there is a
free trial that you can download. There may be other suppliers, as well.

Larry Linson
Microsoft Access MVP

"Tom Ellison" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dear Alene:
>
> You would first need a table that gives the name of the city given the zip
> code.
>
> Unfortunately, there is sometimes more than one city or town with the same
> zip code. In less populated portions of the country there can be several
> towns with the same zip code. The zip code represents a single post
> office. There could be only one post office for several small towns.
>
> So, if you have:
>
> John Smith
> 101 Main Street
> 55111
>
> That is, not specifying the city or state, but using the zip code to
> "lookup" the city and state, there's a possible problem. If there are 2
> towns in that zip code, and they both have a Main Street, this address
> could be for two different buildings in different towns.
>
> Perhaps this is not terribly common, but not impossible.
>
> You could still do this, but allow users to correct the City if its wrong.
> It would then be quite useful 99% of the time.
>
> Tom Ellison
>
>
> "Alene" <(E-Mail Removed)> wrote in message
> news:82B6ADB3-3B04-4B37-984E-(E-Mail Removed)...
>> How do I make MS Access fill in the city when I type the zip code in the
>> form?
>
>

I already have the table with city and zip - I just need to know how to join
the tables so that when I type the zip in the form it will automatically make
the city show up.

"Larry Linson" wrote:

> I'd only add to Tom's excellent answer that Zip codes change and are
> augmented by the Post Office, and they publish new files periodically
> ((monthly or thereabouts, if memory serves).
>
> The easiest way to get up-to-date files in the format you want, if what the
> Post Office provides is not your format, is to subscribe to a third-party
> service that converts and provides the files on a schedule. One provider of
> ZIP code data usable with Access is FMS, Inc.
> http://www.fmsinc.com/products/zipcode/index.html. I have not used, nor
> tried, their product so cannot offer an opinion, though I see there is a
> free trial that you can download. There may be other suppliers, as well.
>
> Larry Linson
> Microsoft Access MVP
>
> "Tom Ellison" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Dear Alene:
> >
> > You would first need a table that gives the name of the city given the zip
> > code.
> >
> > Unfortunately, there is sometimes more than one city or town with the same
> > zip code. In less populated portions of the country there can be several
> > towns with the same zip code. The zip code represents a single post
> > office. There could be only one post office for several small towns.
> >
> > So, if you have:
> >
> > John Smith
> > 101 Main Street
> > 55111
> >
> > That is, not specifying the city or state, but using the zip code to
> > "lookup" the city and state, there's a possible problem. If there are 2
> > towns in that zip code, and they both have a Main Street, this address
> > could be for two different buildings in different towns.
> >
> > Perhaps this is not terribly common, but not impossible.
> >
> > You could still do this, but allow users to correct the City if its wrong.
> > It would then be quite useful 99% of the time.
> >
> > Tom Ellison
> >
> >
> > "Alene" <(E-Mail Removed)> wrote in message
> > news:82B6ADB3-3B04-4B37-984E-(E-Mail Removed)...
> >> How do I make MS Access fill in the city when I type the zip code in the
> >> form?
> >
> >
>
>
>

I already have the table with city and zip - I just need to know how to join
the tables so that when I type the zip in the form it will automatically make
the city show up.

"Tom Ellison" wrote:

> Dear Alene:
>
> You would first need a table that gives the name of the city given the zip
> code.
>
> Unfortunately, there is sometimes more than one city or town with the same
> zip code. In less populated portions of the country there can be several
> towns with the same zip code. The zip code represents a single post office.
> There could be only one post office for several small towns.
>
> So, if you have:
>
> John Smith
> 101 Main Street
> 55111
>
> That is, not specifying the city or state, but using the zip code to
> "lookup" the city and state, there's a possible problem. If there are 2
> towns in that zip code, and they both have a Main Street, this address could
> be for two different buildings in different towns.
>
> Perhaps this is not terribly common, but not impossible.
>
> You could still do this, but allow users to correct the City if its wrong.
> It would then be quite useful 99% of the time.
>
> Tom Ellison
>
>
> "Alene" <(E-Mail Removed)> wrote in message
> news:82B6ADB3-3B04-4B37-984E-(E-Mail Removed)...
> > How do I make MS Access fill in the city when I type the zip code in the
> > form?
>
>
>

two different options:

The better for always having a value:
Make it a dropdown and return 3 fields zip, city, state and limit to
list and then in the afterupdate event
me.cityfieldname = me.zipcombo.column(1)
me.statefieldname = me.zipcombo.colmun(2)
however this does not cover Canadian zips nor foreign countries.

An alternative:
have zip as txtbox.
In the afterupdate event
dim holdcity as variant

if isnull(me.zipfieldname) then
msgbox.................... error
elseif len(me.zipfieldname) < 5 then
msgbox ............... error
elseif maybe, depending on how you want to handle zip+4

else

holdcity = dlookup ("[zipfilecityname]",
"querytogetcityusing zip")
if isnull(holdcity) then
msgbox "zip not found"
else
me.cityfieldname = holdcity
holdcity = dlookup ("[zipfilecityname]",
"querytogetcityusing zip")
me.statefieldname = holdcity
endif

This is aircode, but something like this needs to be done and it can
vary depending on how elaborate/safe you want to make it.

Ron

"The definition of a fool proof system is that some fool comes along
and proves you wrong."

Hi Larry,

Thanks for the referral. Our Total ZipCode Database product is a
monthly subscription to the official USPS zip code database. We provide
it to you in an Access table, along with some other lookup tables like
country and states for a handful of countries.

We also include code and a form that shows how people can enter the zip
code and automatically have the city and state filled in. The combo box
for the city also displays other non-primary but acceptable city names.
As you can imagine, it increases data entry speed and eliminates typos.


Visit http://www.fmsinc.com/products/zipcode/index.html for more
information.

Luke Chung
President
FMS, Inc.
http://www.fmsinc.com

P.S. Check out our latest update to Total Access Statistics, the most
advanced number crunching tool designed specifically for Access:
http://www.fmsinc.com/products/statistics/index.html