Exclude some values from subform dropdown?

  • Thread starter Thread starter Charlotte
  • Start date Start date
C

Charlotte

I have a form and a subform. The form represents a change
request and the subform people assigned to work on the
change request.
In the subform, new people can be assigned, but in the
person drop-down, I dont want people already assigned to
appear. How can I handle this? The drop-down is currently
populated by a query that lists everyone.
I could change the query to exclude the people already
assigned but how would I refer to the current change
request in the query?
 
In the subform, new people can be assigned, but in the
person drop-down, I dont want people already assigned to
appear. How can I handle this? The drop-down is currently
populated by a query that lists everyone.
I could change the query to exclude the people already
assigned but how would I refer to the current change
request in the query?
Click to expand...

You're on the right track! Just use

=Forms!yourformname!textboxname

as a criterion in the unmatched values query; something like

SELECT DISTINCT PersonID, <other fields>
FROM People
WHERE PersonID NOT IN(SELECT PersonID FROM assignments WHERE
assignments.PersonID = Forms!yourformname!textboxname)
ORDER BY LastName, FirstName;
 
Back
Top