Stacie
One way to do this is to use an unbound combobox (I place mine in the form
header), and "feed" that combobox the (in your case) department numbers you
want the user to select from. Typically, there's a tlkpDepartment "lookup"
table that contains DeptID and DepartmentName. Both go to the query, but
only the DepartmentName is shown, to make it easier for the user to pick the
correct one (no need to memorize Dept#s!).
The form itself is based a query that's based on the ??? table (I can't
tell what table this is in your situation). In that query, add a Selection
Criterion for the DeptID that points back to the form ... something like:
Forms!YourFormName!cboYourPickADepartmentComboboxName
Now add an AfterUpdate event to the combobox that requeries the form,
something like:
Me.Requery
The next effect of all this is that the form opens (with nothing in the
combobox) and displays the record that corresponds to that nothing (i.e., no
record). Then the user selects a Department from the combobox and the
AfterUpdate event requeries the form, now selecting the record that has the
ID of the item selected in the combobox.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP