Full Version: Combo Box duplicate selection
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
kbuttars
In a training form for my employee database, I have a combo box listing all company employees. The employees are selected from the combo box to attend a specific class. What properties do I set to make sure that an employee is only selected one time from the combo box per class? What I would really like it to do is after an employee is selected - make them unselectable or remove the name from the list for the specific training class schedule. Can this be done?

Thanks so much!
Kathi
ScottGem
In the query behind the combo you filter out those already selected. The RowSource would look something like this:

SELECT EmployeeID, [Last] & ", " & [First] AS fullname FROM tblEmployees
WHERE EmployeeID NOT IN(SELECT EmployeeID FROM tblCLasses WHERE ClassID = Forms!formname!controlname)
ORDER BY last, First;

The subselect lists all employees who have been assigned to a Class shown in what referenced control. By using the NOT IN operator, you exclude those employees.
kbuttars
Scott -
Thank you! Your suggestion worked perfectly. I appreciate your help.

Kathi
doodle
Hiya guys,

Thank you Scott, your suggestion worked out great for another form I had roughly the same idea just with products, I didn't want the user to be able to select the same product twice in the same purchase order. I had a form in continous mode and had a series of combos, I resorted to convolted VBA code to get it to work ( with flaws), but your idea worked flawlessly with much less effort.

Thanks again.

David.
ScottGem
That's me. Do it the easiest way possible wink.gif
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.