The way this is commonly done is by means of correlated combo boxes, so that
having made a selection in one combo box the available items in the second
combo box are restricted on the basis of the selection made in the first.
You'd start with a combo box of departments, cboDepartnents say, which lists
all departments by means of a RowSource such as:
SELECT Department
FROM tblDepartments
ORDER BY Department;
The second combo box of descriptions, cboMajorDescriptions say, would be
correlated with the first by referencing it in its RowSource, e.g.
SELECT MajorDescription
FROM tblMajorDescriptions
WHERE Department = Form!cboDepartments
ORDER BY MajorDescription;
The tblMajorDescriptions table must have a foreign key Department column of
course.
Note that because both controls are on the same form you can use the Form
property to refer to the form rather than having to fully reference it by
name in the above SQL statement.
To get the second combo box to restrict its list to those descriptions
applicable to the selected department you need to requery it in the
AfterUpdate event procedure of cboDepartments with:
Me.cboMajorDescriptions.Requery
While the above will work it does mean that there is some redundancy in the
table underlying the form. As the department is inferred by the description
the inclusion of a Department column in the table is redundant. This leaves
it open to possible update anomalies, which is exactly what the use of the
correlated combo boxes is attempting to prevent. There is still the
theoretical possibility of the table being incorrectly updated in some other
way however, so the integrity of the data is still open to possible
compromise. You'll find a demo at the following link of how to eliminate the
redundancy but still be able to select a department first then a description.
It uses different types of data (the local administrative areas in my part
of the world) and there are three levels to the hierarchy rather than two,
but the principle is just the same:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
