Limit list of values in Combo box

[falguy]

I couldn't find a solution to this in earlier posts, so I'll try to explain my problem here. Here goes...

I have a form based on a table of Projects. It has a subform based on a group of Users who are working on each Project. This subform is based on entries in a table called UserProjects. This part is fine. As you scroll through the Projects form, the subform displays the appropriate Users of that Project. I want to add the ability to assign new users to a Project. I want to create a combo box which lists all Users EXCEPT for those already assigned to the Project. If I'm looking at Project "X" and User "A", "B", and "D" are currently assigned to Project "X" (they'll be showing in the subform), the Combo Box should have User "C", "E", etc to choose from.

I believe this should be straightforward and I am just having a brain cramp, but can't figure out how to do this easily. I tried various combinations of queries for the combo box record source, but haven't found the right combination.

Any assistance is appreciated.

 

[Jack Cowley]

You could add an "Assigned" Yes/No field to the table and check this box when that person is assigned to a project. Show only records that do not have the Assigned field checked in your combo box.

 

[falguy]

Thanks for the reply. A user can be assigned to multiple projects at the same time, so I don't see how an "Assigned" flag will be of use.

 

[falguy]

Thank you for the help. It worked perfectly.