Switch Zip Code Lists With States

  • Thread starter Thread starter wisemax
  • Start date Start date
W

wisemax

When "NY" is entered into the form, I want the ZipCode to link to a
list of NYZipCode.
When "NJ" is entered into the form, I want the ZipCode to link to a
list of NJZipCode.

So the user can pick a zip code from the drop down box.

How can I do this?

Thanks in advance.

HZ
 
It would take at leat 3 tables and probably one linking table between
them,...unless the Zipcode Table can double as the linking table.

tblState [fldStateAbrv], [fldStateFullName]
tblCities [fldCityName], [fldStateAbrv]
tblZipCode [fldCodeNumber], [fldCityName], [fldStateAbrv]

The relationships can get complex. It is a mix of "1-to-many" and
"many-to-many".
The same city name can appear in more than one state and a state has more
than one city (many-to-many), but city names are never duplicated in each
state and states have more than one city (1-to-many), and then Zipcodes are
never repeated but yet the same city name can have more than one Zipcode in
both the same State and also across States (many-to-many?,..not sure).

There is probably something out there that is already created by someone if
you hunt around.
 
When "NY" is entered into the form, I want the ZipCode to link to a
list of NYZipCode.
When "NJ" is entered into the form, I want the ZipCode to link to a
list of NJZipCode.

So the user can pick a zip code from the drop down box.

How can I do this?

Thanks in advance.

HZ
Click to expand...

You could build your own system with combo boxes if you have a list of
zips by state. Remember that some Zips cross state lines.
 
Thanks Phillip!

What I want to accomplish is to avoid zip code getting entered
incorrectly (I saw severl cases in a huge database), plus I think it is
a good exercise.

For this purpose, I have a list of New York zip codes, there are 2,235
items. For now, I do not consider matching city with correct zip code
(too complex as you said). I just want the user to pick an existing zip
code from the drop down box.

Another thought: After an entry of State is entered into the form, if
the user enters a wrong zip code that does not exit for the state, he
will see a message box. I tried to use array in VBA. But with 2,235 zip
code items, it was hard to build array. Can you help?

One more thing: any idea or code about compare a zip code entry against
the array?

HZ
 
Another thought: After an entry of State is entered into the form, if
the user enters a wrong zip code that does not exit for the state, he
will see a message box. I tried to use array in VBA. But with 2,235 zip
code items, it was hard to build array. Can you help?
Click to expand...

The Zipcodes would all exist in one single table no matter where they were
from. The table would be linked to a States table in a "one to many"
relationship.

(key field)
tblState [fldStateAbrv], [fldStateFullName]
1
----------
|
---------
8
tblZipCode [fldCodeNumber], [fldStateAbrv]
(key field) (foreign key)

The ZipCode selection box on the form would list the ZipCode values from the
tblZipCodes where the Foreign Key [fldStateAbrv] = the "text" value of the
form's control containing the State Abrv.

I can't give you exact code,...I'm not that good at it. But here are some
ideas. My few code samples may have errors, but they should point you in the
proper general direction.

On the LostFocus Event of the "State" control, have it populate the ZipCode
Selection control. You create saved Access Parameter Queries to use for this
without having to write a bunch of SQL queries. For example if the control
holding the State value was a Text Box called "txtState", it could take the
value of "txtState.Text" and use it as the parameter for that Parameter
Query to produce the content for the Zipcode Selection control.

On the GotFocus Event of the ZipCode Selection control, have it popup a
MessageBox if the State control value is empty. It should tell the user they
must fill in the Sate first,...when they click Ok on the Messagebox the
Focus is passed to the State control.
lstZipCode.Focus = False
txtState.Focus = True
One more thing: any idea or code about compare a zip code entry against
the array?
Click to expand...

No way I could answer that,..I don't even see any need for an array.
Arrays, potentially, eat up a lot of memory. You would already have a
RecordSet in Memory,...just use it rather than doubling the amount of used
RAM by adding an Array that may be duplicating data already held in the
record set.

I sometimes use Arrays, but I try to close the RecordSet once the Array is
"loaded up" with data from the RecordSet.
 
Joseph Meehan said:
(e-mail address removed) wrote:
You could build your own system with combo boxes if you have a list of
zips by state. Remember that some Zips cross state lines.
Click to expand...

Good point. I didn't think about them crossing State lines. Surely a set of
tables for doing this already eixsts, and already populated, is out there
somewhere. It could then just be imported into the database.
 
Phillip said:
Good point. I didn't think about them crossing State lines. Surely a
set of tables for doing this already eixsts, and already populated,
is out there somewhere. It could then just be imported into the
database.
Click to expand...

You can get tables down to street addresses, but the last time I looked
they were expensive.

You might be able to find a State table however. Try the US Postal
Service. (BTW what are you going to do with non-US addresses?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with update query and unbound combobox 4
Entering Data In Form Field 2
Zip Codes 6
Zip Code Table 6
Table Relationships and Automation 3
Autopopulate in Form 9
XBase programmer needs help understanding Access 33
Many (City) to Many (Zip Code) help 8

Back
Top