Filtering records based on user

[Secret Squirrel]

I want to set up a filter where a specific user can only see certain records
within my tblEmployees. I have a table (tblDepartments) that is linked to my
tblEmployees. I also have a table (tblUsers) that is linked to tblDepartments
as well. Each user has a specific department they are responsible for so I
only want them to see the records in the tblEmployees that are within their
department. How would I design a query to link all this together so it
filters correctly based on the department? I assume I'll also have to link my
tblUsers to the ULS so it knows which user is logged in, right? Any ideas?

 

[Jeff Boyce]

You don't mention how these are 'linked', so I'll assume you mean your
tables have foreign key fields that point back at their "parents" records.

By the way, I'll also assume you don't have any "mesh" or "shared"
assignments, in which a single Employee answers to more than one Department.

You are asking a "how" question ... how depends on what ... it all starts
with the data. And I'm guessing you'll need so code/procedures also, not
just "a query to link all this together".

FYI, you can use a dynamically-constructed SQL statement in a procedure to
serve as a query. I'd probably take an approach like this to first identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

That should have read

"... you'll need code/procedures also,..."

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Armen Stein]

FYI, you can use a dynamically-constructed SQL statement in a procedure to
serve as a query. I'd probably take an approach like this to first identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.

Jeff's right.

But remember that if the back-end database is in Access, you won't be
able to ensure that someone can't see records outside their
department. There are plenty of ways to circumvent the application
and get to the underlying data directly. So make sure that you're
doing this for convenience, not for sensitive or personal data.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Secret Squirrel]

Hi Jeff,

Thanks for the help. What I'm thinking is this...

I create a case select on my form's load event to lookup the CurrentUser()
and match it to the username in my tblUsers then have it take the value from
the field "dept" also in my tblUsers and whichever the value is use a case
select to run a SQL function to load the correct query. How does that sound?

 

[Secret Squirrel]

Actually this is to filter records so that managers can only see their
employees. So it's kind of both for convenience and sensitivity purposes. The
database will be secure and users won't be able to get to the BE. This is
just an internal security measure on top of having ULS set up.

 

[Secret Squirrel]

I took another approach but didn't realize that this type of query isn't
updateable. Or is it and I just have it written wrong? I still want to be
able to add new records but I have a feeling I can't if I'm filtering the
records.

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees, tblUsers
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active")) OR
((([Forms]![frmEmployeeMain]![chkInactive])=-1));

 

[Secret Squirrel]

Ok I've got it working now but I have one question. In my tblUsers in the
field Dept I want to put "All" and have this query show all records for that
specific user that has "all" in the dept field. How do I write that into my
query?


SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active")) OR
(((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE CurrentUser() =
tblUsers.UserName)) AND (([Forms]![frmEmployeeMain]![chkInactive])=-1));

 

[Jeff Boyce]

Code. Consider creating a procedure that dynamically sets the WHERE clause
of your SQL statement. If the <ALL> choice is made, don't use ANY clause
for WHERE!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ok I've got it working now but I have one question. In my tblUsers in the
field Dept I want to put "All" and have this query show all records for
that
specific user that has "all" in the dept field. How do I write that into
my
query?


SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active"))
OR
(((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE CurrentUser() =
tblUsers.UserName)) AND (([Forms]![frmEmployeeMain]![chkInactive])=-1));

You don't mention how these are 'linked', so I'll assume you mean your
tables have foreign key fields that point back at their "parents"
records.

By the way, I'll also assume you don't have any "mesh" or "shared"
assignments, in which a single Employee answers to more than one
Department.

You are asking a "how" question ... how depends on what ... it all starts
with the data. And I'm guessing you'll need so code/procedures also, not
just "a query to link all this together".

FYI, you can use a dynamically-constructed SQL statement in a procedure
to
serve as a query. I'd probably take an approach like this to first
identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Armen Stein]

Actually this is to filter records so that managers can only see their
employees. So it's kind of both for convenience and sensitivity purposes. The
database will be secure and users won't be able to get to the BE. This is
just an internal security measure on top of having ULS set up.

Okay, you're using ULS and the database will be secure.

Are you using OwnerAccess queries, with users having no permissions to
the BE tables themselves? If so, how do you ensure that the
OwnerAccess queries will never access records outside the user's
department?

Is the BE database encrypted? How do you mean that the users won't be
able to "get to the BE" - they'll need read/write permission to the
folder, so they'll be able to take the BE home on a USB drive if they
want to.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com