Best Way to Handle City/Zip Auto-fill

[CS]

I have a DB which lists clients from a limited number of cities/towns (25 to
30 cities/towns) and would like to auto-fill the zip code (or at least offer
a second combo with a filtered list of zip code choices) .

This is my problem. Some of the cities have several zip codes, and some of
the zip codes include several towns. Since the person doing the data entry
is most likely to know the city/town rather than the exact zip code off the
top of their head, I am leaning toward having them fill in the city/town
(from a combo box), then having the zip code field as a combo box populated
with the options (if any) for that city/town (most towns have only one
option). This information goes to tblCustomer in the appropriate address
fields.

Here's my dilemma -- I have a concern that the user might not KNOW that
there is any other option for that city/town, as the first option for the
town will show by default in the zip combo box.

I want to somehow alert the user that there are several options for a given
town, and am not sure what is the best way to go about it. I had thought
about a pop-up message with the various zip-code alternatives available --
user clicks the correct one, which selects the correct record from the table
described below.

This is how it's structured now:
tblCities contains 3 fields
CityName
CityZip
CityState
There is no primary key in this table, and each town may have several
records, with a different zip for each record. I suspect this is the wrong
approach.

I had considered:
tblCities with 6 fields
City Name (PK)
CityZip1
CityZip2
CityZip3
CityZip4
CityState

It is highly unlikely that any of the towns involved would have more than 4
zips (its a small business dealing with rural townships, for the most part).

I'd appreciate any help or advice you could give on this.

Thanks,
CS

 

[Damon Heron]

There is a zipcode db (an mde file) at this site:
http://www.granite.ab.ca/access/downloadsindex.htm

You will have to import the zipcode table into your db. If you have a
limited area to cover (for instance, Ohio) then you might want to truncate
the file because it is +/- 73000 records. On your form, you have two text
boxes, one for city, and one for state (2 letters abbreviated). Add a
combobox that has as a control source something like this:

SELECT tblZipCode.Zip, tblZipCode.City, tblZipCode.State
FROM tblZipCode
WHERE (((tblZipCode.City)=[txtCity]) AND ((tblZipCode.State)=[txtState]));

That will filter the combobox to just those zips in your city/state.

If you want to make users aware of all the choices, then look in help for
the dropdown method for your combobox when it gets the focus.


Damon

 

[Damon Heron]

Oops! forgot to add in the gotfocus event of the combobox, put
Me![name of your Combo].Requery
Me![name of your Combo].Dropdown

There is a zipcode db (an mde file) at this site:
http://www.granite.ab.ca/access/downloadsindex.htm

You will have to import the zipcode table into your db. If you have a
limited area to cover (for instance, Ohio) then you might want to truncate
the file because it is +/- 73000 records. On your form, you have two text
boxes, one for city, and one for state (2 letters abbreviated). Add a
combobox that has as a control source something like this:

SELECT tblZipCode.Zip, tblZipCode.City, tblZipCode.State
FROM tblZipCode
WHERE (((tblZipCode.City)=[txtCity]) AND ((tblZipCode.State)=[txtState]));

That will filter the combobox to just those zips in your city/state.

If you want to make users aware of all the choices, then look in help for
the dropdown method for your combobox when it gets the focus.


Damon

I have a DB which lists clients from a limited number of cities/towns (25
to 30 cities/towns) and would like to auto-fill the zip code (or at least
offer a second combo with a filtered list of zip code choices) .

This is my problem. Some of the cities have several zip codes, and some
of the zip codes include several towns. Since the person doing the data
entry is most likely to know the city/town rather than the exact zip code
off the top of their head, I am leaning toward having them fill in the
city/town (from a combo box), then having the zip code field as a combo
box populated with the options (if any) for that city/town (most towns
have only one option). This information goes to tblCustomer in the
appropriate address fields.

Here's my dilemma -- I have a concern that the user might not KNOW that
there is any other option for that city/town, as the first option for the
town will show by default in the zip combo box.

I want to somehow alert the user that there are several options for a
given town, and am not sure what is the best way to go about it. I had
thought about a pop-up message with the various zip-code alternatives
available -- user clicks the correct one, which selects the correct
record from the table described below.

This is how it's structured now:
tblCities contains 3 fields
CityName
CityZip
CityState
There is no primary key in this table, and each town may have several
records, with a different zip for each record. I suspect this is the
wrong approach.

I had considered:
tblCities with 6 fields
City Name (PK)
CityZip1
CityZip2
CityZip3
CityZip4
CityState

It is highly unlikely that any of the towns involved would have more than
4 zips (its a small business dealing with rural townships, for the most
part).

I'd appreciate any help or advice you could give on this.

Thanks,
CS

 

[missinglinq via AccessMonster.com]

BTW, the statement:

"Some of the cities have several zip codes, and some of the zip codes include
several towns"

is only half true! People in rural areas often will give an address using
their actual zipcode and the "town" they live in, giving the impression that
a zipcode can include more than one town, but in fact the USPS only
recognizes one town per zipcode! Having the zipcode entered and then
autofilling the town (as most apps that use this do) assures that the correct
town will be entered in an address.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[CS]

I understand that the USPS only recognizes one "approved city name" in any
given zip code.

However, most of the people that provide their information to this database
include the name of the town that they actually live in, as they have known
it for 20 or more years, rather than the name that the USPS considers
"approved" for their zip code -- which is why, I assume, the USPS provides
the following type of look-up: http://zip4.usps.com/zip4/citytown_zip.jsp

This is a small, rural region. Yes, if we sent to the indicated zip and
included the wrong city name, it would probably still get to the address
they provided -- I think, I hope.

However, my problem is that the information is being gathered from
individual clients who may, or may not, know that their township is actually
"zipped" under the name of another town, and which information is
transmitted to onsite providers who are likely to get the town name correct
(or near correct), but who might make errors in the zip code.

In a DB covering a 50 square mile rural area (max), and which will have
about 100 clients (max), I don't particularly want to download a huge
zip-code table of 70K+ records. It just seems like overkill to me.

Thanks to all for helpful advice,
CS

 

[John Spencer]

I would reverse the order entry.

Enter a ZIP Code and it will display the location in a combobox. If there
are multiple locations for a specific ZIP code then drop down the combobox.

It is less likely that a ZIP code will be "spelled wrong" or mistyped, plus
the Zip Code is almost always shorter than the location name.

I would populate the location combobox based on the zip code in the lost
focus event of the zipcode control.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[missinglinq via AccessMonster.com]

CS,

For that kind of limited area and with you wanting to include multiple towns
per zipcode, you probably should simply run up your own Zip Table. I'm
currently porting an old QuickBasic app to Access. In the original db (which
was used for tracking home healthcare visits) I did just that, only opting to
go to a national Zipcode table now that I'm planning to market the db country
wide. And I do, indeed, understand about country folk; I've had clients who
could only tell you to turn off the main highway, pass 5 corn fields and look
for an old grey dog laying by a milk can!

Good Luck!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com