Auto-Populate Combo Box.


B

Brian Cook

I have three tables that have relationships to each other. I have a form
built that shows three ComboBoxes on it. One each for ZIP, CITY, STATE.

I want to type/pulldown the ZIP code and populate the City, and State fields
without any other user actions.

I have tried adding an AfterUpdate to the ZIP field, and performing a
requery, however it does not populate the other fields.

Looking for some code or query suggestions.

Thanks,
 
Ad

Advertisements

D

Duane Hookom

Assuming you have only one city/state per each zip code (not always true)
your combo box Row Source could have additional columns for City and State ie:
SELECT DISTINCT ZipCode, City, State
FROM .....
ORDER BY ZipCode;

Then in the after update event of the combo box, use code like:
Me.txtCity = Me.cboZipCode.Column(1)
Me.txtState = Me.cboZipCode.Column(2)
 
B

Brian Cook

ZIP Codes and Cities are in one table, and the States are in another. Would
it be smarter/easier to have it all in one table?
 
D

Duane Hookom

They don't all need to be in the same table. You only need to be able to
create a query that links all three fields.
 
K

Ken Sheridan

Really you should have separate tables for each, Zips, Cities and States, the
first referencing the second on CityID (city names can be duplicated so use a
unique number as the key), the second referencing the third on StateID (a
unique numeric key isn't really needed here as state names are not
duplicated, but many people prefer to use one nevertheless). This assumes
each Zip relates to only one city, which is implied by your question of
course.

Having a single table for Zips and Cities introduces redundancy as where
there is more than one Zip for a City the table would contain the information
that that city is in a particular state more than once. This would allow for
inconsistent data in that the same city could be incorrectly placed in
different states in separate rows of the table. Having all three in one
table would only compound the problem of course.

You don't need to include a City or State column in the main referencing
table, and to do so introduces redundancy and the possibility of inconsistent
data. Just include the Zip column and for its combo box's RowSource use a
query which joins the three tables, e.g.

SELECT Zip, City, State
FROM Zips INNER JOIN
(Cities INNER JOIN States
ON Cities.StateID = States.StateID)
ON Zips.CityID = Cities.CityID
ORDER BY Zip;

Set the controls ColumnCount property to 3.

Include unbound text box controls on the form for city and sate with
ControlSource properties respectively of:

=cboZip.Column(1)
and:
=cboZip.Column(2)

where cboZip is the combo box bound to the Zip column in the form's
underlying table. The Column property id zero-based so Column(1) refers to
the second column and so on.

You might be interested in a demo of how to handle this sort of data using
correlated combo boxes, which can be found at the following link:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo uses the local administrative units of Parish, District and County
in my area, but the principle is the same. It differs from what you want to
do in that it allows the units to be selected from the top down, starting
with County, then selecting from Districts in the selected County, then from
Parishes in the selected District, but at the same time maintaining the
normalized structure of the referencing table by storing only the ParishID.

BTW storage of addresses by PostCode (usually along with house number or
name)is common here in the UK as our post codes relate to tightly defined
areas, my own for instance pinpointing the location to one side of my street
(35 houses). There is thus no need to include any other location data.

Ken Sheridan
Stafford, England
 
Ad

Advertisements

B

Brian Cook

This sounds like a great idea. I am going to bang this out tonight and see if
I can get it to work..

Thanks Ken.

Brian
 
B

Brian Cook

Ken, I took part of your suggestion and got it to work.

I ended up with this query in the ZIP field;

SELECT ZIPCodes.ZIPCode, ZIPCodes.City, States.ST
FROM States INNER JOIN ZIPCodes ON States.StateCode = ZIPCodes.StateCode
ORDER BY ZIPCodes.ZIPCode;

Then Code in the back is this;

Me.txtST = cboZip.Column(2)
Me.txtCity = cboZip.Column(1)

Thanks all of you for your suggestions and help.

V/R

Brian
 
B

Brian Cook

Ok, new problem. Solution works fine.

Now after I have made a change to a ZIP/State/City field, and I navigate to
the next record, or do a look up for a specific user, it keeps the data that
I entered in the first record.

I got to be missing something simple.
 
D

Duane Hookom

Apparently txtST and txtCity aren't bound to a field in your form's record
source (this is generally good practice).

Try remove the code:
Me.txtST = cboZip.Column(2)
Me.txtCity = cboZip.Column(1)

Set the Control sources of the text boxes to
= cboZip.Column(2)
= cboZip.Column(1)
 
Ad

Advertisements

B

Brian Cook

Removed the Me.xxx stuff, and put the source as specified.

Data in table is not updated. Should I have some kind of an append query in
the AfterUpdate event?
 
D

Duane Hookom

Your cboZip should have a Control Source of a field in your form's record
source.
 
B

Brian Cook

I tried that and it does not let me select any of the items in the cboZip, or
even type into the field. If I leave it unbound it does let me select and
type into the field.
 
D

Duane Hookom

Is your form updateable? What are you setting as the control source of the
combo box? Is the control source a field from your form's record source?
 
Ad

Advertisements

B

Brian Cook

Ok, I figured it out. I deleted the ComboBox, and recreated it. Set the
control source for the txtCity, txtST fields to the primary table, and tested
it.

That work. I appreciate the help that you guys have given.

Learned a lot from this one.

V/R Brian
 
Ad

Advertisements


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


Top