Create a combo box.
For the row source you can either use a table/query or a value list. I'd suggest creating a table that contains two columns, one with the department code and the second with the department name (or a table with just one column for the department name). You can then define a query that sorts by the department name, but returns the code and the deparmtnt name.
On the combo box properties set the column count to 2 columns (for the 2 column table/query). Since you probably don't want the code displayed, set the column widths to 0";2" (or whatever width is appropriate for the department name), and the list width to 2". You want to limit the selections to the list so the user can't enter something not on the list.
Set the OnUpdate property of the combo box (let's call the combo box cboFindDept) to run code like the following to limit the records displayed to the selected department:
me.FilterOn=True
me.Filter = "[DeptName]='" & me.cboFindDept & "'"
me.Requery
This will insure that the filter is set to true. Note that the filter must include the single quotes around the value selected in the cboFindDept combo box.
Me.Requery forces Access to select the records again for the form, this time where the department is the one selected.
You can go futher with this if you also need to select something within the deparment. You could make a second combo box with a query that includes the criteria that [DeptName] = the value on the form in cboFindDept. Just include the code
me.cboFindNextLevel.requery
as part of the onUpdate property for cboFindDept.
