Controlling a subform recordsource

[alorenzini]

I have a form called Home which contains 4 subform which are each datasheets (see attached). I have recieved a new criteria in which I need to allow all records to be display if the user who is logged in has IsAdmin=Yes.

Right now the subforms are filtered by the cmbEmployee value from the combo-box at the top (see attached).

Let take the Active Client subform. The subform is based on a query called qryClientExtendedActive see definition below.

SELECT [Client Extended].ID, [Client Extended].StaffID, [Client Extended].DateOfIntake, [Client Extended].[Client Name], [Client Extended].Status
FROM [Client Extended]
WHERE [Client Extended].Status="Active";

The other subforms have basically the same queries supporting them.

But now, somehow I have to filter these queries based on the fact that the logged in user is an admin or not.

When the user logs in, I am capturing if the user is admin or in a variable called sLoggedinUserIsAdmin which is globally defined. Now if an admin logs in I need to bring all the records regardless of the staff who is assigned to them or if the user is not an admin I need to filtering them as they would normally. Clear as mud?

 

[MajP]

Lots of ways to do this. I am just not seeing how you are filtering currently. You say something about cmbEmployees but I do not see a field in the query that deals with Employees.
Do you apply a filter?
Is there a Link Master Fields, Link Child Fields properties in the subform controls?

The easiest would be to have an additional set of queries. So if the qryClientExtendedActive is somehow filtering only to a specific employee or staff, then make qryClientExtendedActiveAdmin which returns all records. When the form opens change the recordsource of the subforms. If you are filtering the subform using a Link Child Fields Link Master Fields you can remove this property through code. Bottom line not only can you change the recordsource of the subforms, but also how they link.