Hi Yvette,
One good general approach is this:
1) Bind the form to a query that returns all the employee information
you need.
2) Create a query that returns a list of employees the way you want them
to appear in the dropdown list. Its SQL view will be something like
this:
SELECT EmployeeID, LastName & ", " & FirstName AS DisplayName,
Area FROM Employees ORDER BY LastName, FirstName;
Save it as qryListEmployees
2) Make sure the magic wand button on the control toolbox is "down" and
then draw a combobox in the form header: this will be your *second*
dropdown list. If the wizard is working properly (mine isn't just
now<g>) it will help you set up the combobox so it (a) shows a list of
all the employees regardless of area and (b) when you select an employee
from the dropdown list, the form finds and displays that employee's
data. Name this one cboSelectEmployee
3) When that is working properly, add another combobox to the form
header. This will be your *first* one, for selecting areas. For this
one, the wizard will be less help. It needs to be unbound (ControlSource
property is empty). The RowSource should be a query that returns a list
of areas, so the dropdown list lets the user select an area. Name it
cboSelectArea.
4) The next thing is to make the selection in cboSelectArea control the
list in the "employees" one. In the cboSelectArea's AfterUpdate() event
procedure, use a VBA statement like this:
Me.cboSelectEmployee.RowSource = "SELECT * " _
& "FROM qryListEmployees WHERE Area = '" _
& Me.cboSelectArea.Value & "';"
5) With it set up this way, when the form first opens the Employees
dropdown will contain a list of all employees. But as soon as the user
selects an area the Employees dropdown will be filtered to just that
area.
