SuperUserAccess V's Filter

[Flynners]

Hi
I have Access 2007 db with tblusers, tbldept, tblrecord

I want most users to only see records pretaining to their department in forms and reports. That much I have sorted - based on queries that lookup user department.

However i want some to have superuser access. i want them to see all departments. is there a way i can do this without having to create separate versions of queries/forms and reports as there are many of them and it would be difficult to maintain if updates are required at later stages.

In this instance, is there a way i can give some users access to all departments without having to design new queries. My users are currently only members of one department. I think im restricted here as the login form looks up "txtusername" in tbluser/username and then updates hidden txtdept based on username lookup.

Is there a better way of doing this. Im not thinking very laterally today!

my tables are as follows
tblusers
userid (PK)
username
deptID
usergroup

tblDept
DeptID (PK)
Department

tblRecord
RecordID (PK)
ProjectName
Dept

 

[Isskint]

When a user logs in, do you 'track' that user ID? If so then all you need do is add a little VBA to the onOpen events of Forms, Reports etc that checks the Usergroup that user is in and either cancels the opening or allows the opening.

EG
Private Sub Form_Open(Cancel As Integer)

Dim USrGrp as String

UsrGrp = Dlookup("[UserGroup]","[tblusers]","[userid]='" & {useridtracking] & "'")
if UsrGrp <> GroupAllowedForThisForm then
msgbox "You do not have access to open this form",vbInformation,"Invalid Access"
cancel = true
exit sub
end if

 

[Flynners]

thanks for your reply. I dont think it is really what Im looking for. I do have usergroup rights on some forms if user has read only rights etc they cant open or amend. That element of it works fine.

My problem is that I want to filter data within forms so that users only see data relevant to them but still have some users with Superuser level. Its not read/write access but levels of data filtering based on department

I want the forms to open for all users, just filter the data differently depending on the department they are in, or to ignore department completely and show all if user is a superuser.

 

[Isskint]

Ah OK, understand your requirement a little better now

Do you allow users to filter ad hoc or do you provide pre set filter options?

With pre set filters (or parameter queries) you could do the same check before running that filter or query. I would provide a combobox with filter/query names for the use to 'run' and then check their usergroup in the comboboxes afterupdate event.

 

[Flynners]

I use
forms![frmlogon]![txtdept] as criteria for the department in the query the form is based on. This displays records for that users department only

I want a some users to have access to all departments not just one. But i do not want to have an event to lookup user and if in dept All then open form2 that does not have criteria. that would mean desiging a form2 (x all forms!)

I guess in english im looking for "OR" criteria. ie filter dept based on user dept, but if userdept = ALL then show all

So im not looking at usergroup in this instance, im looking at department user belongs to

 

[Isskint]

Work in progress

Ok, add an extra column to the query to return the UserGroup for the forms![frmlogon]![UserID]. Then on the line of the query criteria where you have forms![frmlogon]![txtdept] put <>"SuperUser" under the extra column. This way the forms![frmlogon]![txtdept] condition will only apply for users who are not SuperUsers. The SQL would look something like
WHERE ((([tblDept].[Department])=forms![frmlogon]![txtdept]) AND ((Expr1)<>'SuperUser'));

 

[Flynners]

I think that this has the opposite effect of what I want. If I add usergroup with criteria of <>"super" to my query and log in as user from "super" group I dont see any departments in my query, when i actually want to see ALL

My select/From is very long so ive cropped the sql

SELECT blah bhal bah FROM blah blah blah
WHERE (((dbo_tblUsers.usergroup)<>"super") AND ((dbo_tblDepartment.DeptId)=[forms]![frmlogon]![txtdepartment]));

 

[Flynners]

Ive attached a sample db of what i am trying to achieve
I have three users
1.jsmith password = test1
2.jbloggs password = test1
3.admin password = admin

user 1&2 are in two different departments and you can see when you open enquiries based on who is logged in you get different results.
I want admin to see all records when logged in