Dropdown list to allow distinct values

[Jenepher White]

I am creating a form with a dropdown list. I want it to remove a value from the dropdown once it's been selected in the form. To ensure no duplicates.

[ranman256]

Unclear. But if in a continuous form of records... If row 1, user picks A, then row 2, A can no longer be a picked.

IF so, you need a query with an IN clause . If tEntry.field with any rows , and tChoices.field NOT in tEntry.field

[ItsMe]

If you are talking about a Combobox, use WHERE criteria in the Combobox's RowSource that considers the User's input. Then, requery your combo to reflect changes made via the User's input. Me.ComboName.Requery

[richardm55]

If you are talking about a Combobox, use WHERE criteria in the Combobox's RowSource that considers the User's input. Then, requery your combo to reflect changes made via the User's input. Me.ComboName.Requery

Could you explain exactly how to do it? Could you write example syntax for WHERE criteria in Combobox's RowSource?

[June7]

Maybe this will work:

As example, say combobox lists employees and is to select an employee for a task. There is a table of Employees and a table of Tasks and a junction table EmployeeTasks that associates employees with tasks and the combobox is on a subform bound to EmployeeTasks and main form is bound to Tasks. Combobox RowSource:

SELECT EmpID FROM Employees WHERE NOT EmpID IN (SELECT EmpID FROM EmployeeTasks WHERE TaskID = [TaskID]);

Code in combobox GotFocus event:

Me.cboEmployee.Requery

[ItsMe]

SELECT EmpID FROM Employees WHERE NOT EmpID IN (SELECT EmpID FROM EmployeeTasks WHERE TaskID = [TaskID]);

That's pretty slick. This way you do not have to build a WHERE clause. Your subform is the WHERE clause.

@ richardm55
What is the process that the user goes through? Understanding the Business Logic/Rules might help us.