help using access populate data based on another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in my form in have
country
state
city

if i select country (USA) a drop down menu
I would like to have the states appear a drop down menu
If i select the state i want the cities to appear a drop down menu

the table are set up as follows
Country = countryId - country
State = StateID - state - country (country foreign key) 1 to many
counrty to state
City = CityID -city- state (state foreign key) 1 to many state to city
 
Thanks i hav e gotte a little furhter ahead

inmy counntry table i have two countrues

CountryID Counrty
001 canada
002 usa

when i select country
canada in state box the number "1" appears
usa inthe state box the number "2" appears

that as far as i can get

:
 
Ian,

By far the best way to enter a combo box is to use the wizard, which sets
the SQL for the RowSource, chooses the Bound Column and the Column Widths,
and sets the Control Source, that is, in what field to store the selection.

What is stored is the Bound Column. What is displayed in the drop-down list
are the non-zero-width columns specified in the RowSource. What is displayed
after the user makes his selection is the first non-zero-width column.

If "1" is being displayed after the selection, it must also be displayed in
the drop-down list, which means its width is set to something other than
zero. Change it to 0" in the ColumnWidth property. Then the country name
should appear when you make your selection.

Place the other two combo boxes either with the wizard, specifying Hide Key
Field, and telling the wizard where to store the selection, or create them
the same way you did for the first one, and manually change the first column
width to 0" to display the State and City, respectively, rather than their
primary keys.

Save the form and test it. You should see a list of all countries in the
first box, a list of all states in the second, and a list of all cities in
the third. All should display the name, not the code.

Now, to add the code that filters the downstream combo box based on your
selection. I will assume your combo boxes are named cboCountry, cboState,
and cboCity respectively, and that your tables are named Country, State, and
City. In the AfterUpdate event of the first combo box, add the following
code:

Me![cboState].RowSource = "SELECT State.StateID, State.State FROM State
WHERE State.Country = " & Me!cboCountry & " ORDER BY State.State;"

Similarly, in the AfterUpdate event of the State combo box, enter the
following code to set the RowSource of the City combo box:

Me![cboCity].RowSource = "SELECT City.CityID, City.City FROM City WHERE
City.State = " & Me!cboState & " ORDER BY City.City;"

Hope that solves it.
Sprinks
 
Ian,

I see there are two separate threads on this topic, which will possibly
add to the confusion.
 
Back
Top