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
