Novice trying to create zip code look-up

[Karen]

I know that it is possible to create a situation where a zip code is typed
into one column, and the city identified with that zip code pops up in
another column, but cannot figure out where to start with this. Can anyone
give some guidance?

 

[Al Campagna]

Karen,
Use a 2 column combo box. (ex. cboZip)
First Column for Zipcode and second column for City.
Set the combo's ControlSource (bind it) to your ZipCode field.

Since this combo control will capture the ZipCode, it is not necessary
to
also "capture" the City value in another control... just "display" it.
Given that
you have the Zip, you can always redetermine the correct city, on the fly,
in any subsequent query or report, through it's relationship to the Zip.
For example, if I capture Price and Qty, I don't need to capture
LineTotal (Price * Qty = LineTotal). I can always recaclulate the
LineTotal,
on the fly, whenever needed.

So, in this case... an unbound text control with a ControlSource of...
=cboZip.Column(1)
would always "display" the correct City whenever you select any Zip from the
combo.
(Combo columns are numbered 0, 1, 2, 3, etc.. so the second column would
be referred to as Coulumn(1))

On my website below, I have a 97 and 2003 sample file called
Combo Populates Multiple Fields, that demonstrates the above.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Karen]

Thanks a million Al!!
--
Karen

Karen,
Use a 2 column combo box. (ex. cboZip)
First Column for Zipcode and second column for City.
Set the combo's ControlSource (bind it) to your ZipCode field.

Since this combo control will capture the ZipCode, it is not necessary
to
also "capture" the City value in another control... just "display" it.
Given that
you have the Zip, you can always redetermine the correct city, on the fly,
in any subsequent query or report, through it's relationship to the Zip.
For example, if I capture Price and Qty, I don't need to capture
LineTotal (Price * Qty = LineTotal). I can always recaclulate the
LineTotal,
on the fly, whenever needed.

So, in this case... an unbound text control with a ControlSource of...
=cboZip.Column(1)
would always "display" the correct City whenever you select any Zip from the
combo.
(Combo columns are numbered 0, 1, 2, 3, etc.. so the second column would
be referred to as Coulumn(1))

On my website below, I have a 97 and 2003 sample file called
Combo Populates Multiple Fields, that demonstrates the above.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."





.

 

[Larry Linson]

I know that it is possible to create a situation where a zip code is typed
into one column, and the city identified with that zip code pops up in
another column, but cannot figure out where to start with this. Can anyone
give some guidance?

You appear to be making the assumption that a ZIP code identifies a
particular city, which is often, but not necessarily, the case. There are
cases where the physical area covered by a ZIP code may be in two or more
cities. If you are using your own data for a limited area, collected or
extracted from your own address files, this caution may be unnecessary as
you won't necessarily encounter the situation I describe, and Al's solution
is appropriate.

Larry Linson
Microsoft Office Access MVP

 

[Karen]

Ken,

Great information! Thank you so much. As Larry said, I was making the
assumption that one zip code identifies one city. So I think I am going to
try your solution.

If I miss a zip code, will the end user be able to type in the city and
zipcode? I would think that this would work if they typed in the city first???

Anyway, I am going to play around and see what happens.

You guys are great!!!