Controlling subform record source

  • Thread starter Thread starter dbalorenzini
  • Start date Start date
D

dbalorenzini

I have a form called Home which contains 4 subform which are each datasheets
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

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?
 
dbalorenzini said:
I have a form called Home which contains 4 subform which are each datasheets
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

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?
Click to expand...


I think you first need to create a little function in a
standard module to get the value of the global variable:

Public Function CheckAdmin() As Boolean
CheckAdmin = sLoggedinUserIsAdmin
End Function

Then you can modify the queries to use a WHERE clause like:

WHERE [Client Extended].Status="Active" OR CheckAdmin()
 
OK. I added your suggestions by adding the global function which looks like
it returning the correct value and updated the query as follows:

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")) OR (((CheckAdmin())<>False));

But it's still returning a filter list when the logged in user is an Admin
(sLoggedInUserIsAdmin = True) instead of all the records.
--
Thanks,
Art
Database Administrator
Yankton, SD


Marshall Barton said:
dbalorenzini said:
I have a form called Home which contains 4 subform which are each datasheets
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

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?
Click to expand...


I think you first need to create a little function in a
standard module to get the value of the global variable:

Public Function CheckAdmin() As Boolean
CheckAdmin = sLoggedinUserIsAdmin
End Function

Then you can modify the queries to use a WHERE clause like:

WHERE [Client Extended].Status="Active" OR CheckAdmin()
Click to expand...
 
dbalorenzini said:
OK. I added your suggestions by adding the global function which looks like
it returning the correct value and updated the query as follows:

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")) OR (((CheckAdmin())<>False));

But it's still returning a filter list when the logged in user is an Admin
(sLoggedInUserIsAdmin = True) instead of all the records.
Click to expand...


If sLoggedInUserIsAdmin has the value True, I don't see how
it's possible for that query to filter the records. Are you
sure that the query is getting True from the function? This
should be easy to check by adding a calculated field to the
query:

Expr1: CheckAdmin()
 
It's returning a -1.
--
Thanks,
Art
Database Administrator
Yankton, SD


Marshall Barton said:
dbalorenzini said:
OK. I added your suggestions by adding the global function which looks like
it returning the correct value and updated the query as follows:

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")) OR (((CheckAdmin())<>False));

But it's still returning a filter list when the logged in user is an Admin
(sLoggedInUserIsAdmin = True) instead of all the records.
Click to expand...


If sLoggedInUserIsAdmin has the value True, I don't see how
it's possible for that query to filter the records. Are you
sure that the query is getting True from the function? This
should be easy to check by adding a calculated field to the
query:

Expr1: CheckAdmin()
Click to expand...
 
It returns a 0 if it's a non-Admin user
--
Thanks,
Art
Database Administrator
Yankton, SD


Marshall Barton said:
dbalorenzini said:
OK. I added your suggestions by adding the global function which looks like
it returning the correct value and updated the query as follows:

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")) OR (((CheckAdmin())<>False));

But it's still returning a filter list when the logged in user is an Admin
(sLoggedInUserIsAdmin = True) instead of all the records.
Click to expand...


If sLoggedInUserIsAdmin has the value True, I don't see how
it's possible for that query to filter the records. Are you
sure that the query is getting True from the function? This
should be easy to check by adding a calculated field to the
query:

Expr1: CheckAdmin()
Click to expand...
 
dbalorenzini said:
It returns a 0 if it's a non-Admin user
Click to expand...


That sure seems to validate the function and the query's
where clause looks good, so I have to wonder if the
filtering is being done somewhere else.

At this point I have to ask if [Client Extended] is maybe a
query that is also filtering the data??

The only other thing I can think of is that the
LinkMaster/Child properties are doing something funny.
 
Back
Top