combo box list based on another combo box and vice versa 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?

 

[joeKra]

I would suggest you to assign the row source [after update] so if you choose the employee first then assign the department and requery,in the same way when you assign the department and requery

EDIT:
by default leave the rowsource just joined but not filtered