Combo box list based on another combo and vice vera back

[cbrxxrider]

I have two combo boxes department and employee. I had it set up so that the user would select the department and only those employees within that department would show in the second combo. So for example, user selects IT and only those employees within IT are listed in the second combo box.

I put this in the employee combo.

SELECT Employee.EmployeeID, Employee.[First Name],
Employee.[Last Name], Department.Department
FROM Department INNER JOIN


Employee ON Department.Department = Employee.Department
WHERE
(((Employee.[Manager/Supervisor])=No))
ORDER BY Employee.[First
Name];

If the user does not know the department but the employees name I would like for them to have the option to choose the employee name first and
then the department combo selects the correct department or at least only shows that in the list.

So I thought it was basically just the opposite... I put this in the department combo.

SELECT Department.Department
FROM
Department INNER JOIN Employee ON Department.Department =
Employee.Department
WHERE
(((Employee.EmployeeID)=[forms]![CompletedGoalForm]![EmployeeID]));

Now this allows the user to select the employee first and the department combo lists the department to click on.

The problem now is the department combo is blank unless an employee is chosen. The user can no longer choose a department first and then have the employees under that department listed.

Any
ideas?

[June7]

I don't know if a circular dependency like this can work at all, but try LIKE operator with wildcard.

WHERE
(((Employee.EmployeeID) LIKE [forms]![CompletedGoalForm]![EmployeeID] & "*"));

[cbrxxrider]

I don't know if a circular dependency like this can work at all, but try LIKE operator with wildcard.

WHERE
(((Employee.EmployeeID) LIKE [forms]![CompletedGoalForm]![EmployeeID] & "*"));

Thanks, it almost worked, unfortunately it listed all the departments multiple times, 1 for each employee listed in the second combo box. Maybe I can play with it further off what you said and get it to kill the duplicates.

[ItsMe]

Why don't you use an if then statement to see if the other combobox is empty? If there is not a value in the other combox then rowsource = A else rowsource = B

[June7]

Does this help:

SELECT DISTINCT Department.Department ...