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?

Hi Alorenzini

If you have an Admin log on then use code to update the subforms recordsource

replace

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";

with (e.g.) (I assume here that the Link Child Field is StaffID)

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

ie Forms![YourFormName]![YourSubFormName].Form.RecordSource = (Above SQL statement)

and Set the source for the Link Master Field = 1

HTH

Do I do this in the Form_Load event or where? So for each of my four subforms I would have code like this:

If sLoggedinUserIsAdmin = -1 then 'this would be a user who is NOT an Admin

Forms![frmClientDetails]![sfrmActiveIntakes].Form.RecordSource = (SELECT [Client Extended].ID, 1 as StaffID, [Client Extended].DateOfIntake, [Client Extended].[Client Name], [Client Extended].Status
FROM [Client Extended]
WHERE [Client Extended].Status="Active";)


End if

How do address the Link Master in code?

Hi Alorenzini

Basically yes, that is how you would update the subforms record source, however having thought about it, if you probably use the ID field and will require it when logged in as admin, so this is probably a better way:

1, Create a text box on your main form called (say) AdminLog, set it's control source to '=0' and make the text box property 'Visible' = No

2. Change the SQL for the subform to:
SELECT [Client Extended].ID, [Client Extended].StaffID, [Client Extended].DateOfIntake, [Client Extended].[Client Name], [Client Extended].Status, 0 AS AdminLog
FROM [Client Extended]
WHERE [Client Extended].Status="Active";

3. On the Forms 'On Activate ' Event


HTH

Edited by: pere_de_chipstick on Wed Mar 11 14:01:20 EDT 2009.