How do I make a record disappear from the combobox list once used!

[nycman]

Hi There,

Short and sweet, I have a table with 8 employees and a table with 3 supervisors. I have a form where I can select a supervisor (combobox) and also select the employees (combobox) that "belongs" to that supervisor. How do I make an employee "disappear" from the choice list once is assigned to a supervisor so I don't assign the same person to more than one supervisor.



thanks for your help

[rpeare]

Link the list of employees to the junction table that contains your supervisor/employee matrix and put a criteria of IS NULL in the supervisor field of the junction table field. This should filter out any person who has been assigned a supervisor.

[nycman]

You said "put a criteria of IS NULL in the supervisor field" - Does this mean that I should create a query and do this in the query?!. I can't find criteria field in the table? Sorry I'm very novice in this.

thanks

[rpeare]

Yes, base your combo box on a query, not a table. I'm assuming you have a structure like this:

tblEmployees (with a supervisor flag)
tblEmployeeAssignments (stores the employee ID of the supervisor and the employee ID of the supervisee)

Your first combo box would be a list of any person on tblEmployees that had the supervisor flag marked (a YES/NO field would work fine)
Your second box could contain the list of employees that belong to that supervisor AND the list of unassigned employees by creating a query where you are selecting all NON supervisors from tblEmployees linking it to the tblEmployeeAssignments going from the EMPLOYEEID of tblEmployees to the EMPLOYEEID of the supervisee. Then in the criteria of the EMPLOYEEID from tblEmployeeAssignments you would have IS NULL. Then go down one row in your criteria section and put a criteria under the SUPERVISORID from tblEmployeeAssignments you would have =[forms]![yourformname]![yoursupervisorcomboboxname].

[nycman]

I think I understand what are you saying. But what does "flag" means?

thank you so much

[rpeare]

by a flag I mean something like a YES/NO field where if the person is a supervisor it's set to YES so you can identify different tiers of employees (you could even have a 'rank' field with a foreign key to a hierarchy table with different ranks like 'CEO, VP, District Manager, Group manager, etc)