restricting query to results based on record in another table
Posted on 2011-02-11
I have a table with a field in it (ResponsibleFor) that is a multiselect field. It holds a list of departments the staff member is responsible for. This table also holds the department that the staff member works in.
I have a separate table with personal notes for all staff members.
I have another table listing the departments of course, which populates the multiselect list boxes for the ResponsibleFor field.
I want to limit the results of a query (and in turn the subform I have made) to show only those results that a member of staff is responsible for. IE, TeamLeader1 is logged in (I can get the username from a variable) so the results of the query should display only staff in the first table that TeamLeader1 is responsible for.
Right now, if I run my subform standalone, and manually enter a team that exists in the popup it creates, it works as expected - restricting the results to the team members who are in that departments personal notes.
However, when I embed that subform in the main form I want to use it in, I cannot get my head around how to make it show only results (ResponsibleFor) based on who's currently logged in - ie, "TeamLeader1" or whatever. This variable is global so I can grab this at any time.
I appreciate that I may not have been that clear, but I hope I've given you enough to go on.
Any assistance gratefully received.