Correlate the controls by referencing the first as a parameter in the
second's RowSource. Say you have two combo or list boxes (the former is more
usual with bound controls) cboStates and cboCities the RowSource property of
the second might be:
SELECT CityID, City FROM Cities
WHERE State = Form!cboStates
ORDER BY City;
Note the use of the Form property to qualify the control rather than a full
reference to the form by name. This is possible where both controls are on
the same from.
In the AfterUpdate event procedure of cboStates you'd requery cboCities with:
Me.cboCities = Null
Me.cboCities.Requery
Setting the control to null caters for it already containing a value which is
not in the selected state. In a bound form you'd also requery the control in
the form's Current event procedure, but not set it to Null:
Me.cboCities.Requery
However, there are a couple of possible problems when correlating combo boxes
like this:
1. If used in a continuous form, and the second combo box's value is a
hidden surrogate key, as with CityID in the above example (city names are not
suitable as keys as they can be duplicated), then this approach won't work as
once a state is selected in one row the city combo box in other rows in which
a different state is selected will go blank. They'd still have the correct
CityID values, so no data is lost; its just not seen. This problem doesn't
arise with 'natural' keys, but these are only possible where the values are
distinct, which, as noted, is not the case with city names.
2. If the above example were used in a form bound to a table of addresses
which included both State and CityID columns the table would contain
redundancy and not be correctly normalized. It should only contain a CityID
column as this determines the State, which can be shown in a computed control
on the form. Its still possible with such a normalized design to use a 'top-
down' data entry sequence, selecting state first, then city, but it requires
the use of a hybrid control for the city, superimposing a text box on a combo
box to give the appearance of a single combo box control.
You'll find examples of how to handle both of the above pitfalls, using the
local administrative units of county, district and parish in my area, at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
