I am stuck!
I am attempting to set up a scenario that will allow me to restrict viewing of certain records depending on which user is accessing the system. I am not using the built in MS Access workgroups function.
Instead i am having a user table that requires a user to have a username that is the same as their windows system login. I am then filtering the query by fOSUserName.
I think this approach works fine when allowing one person to view records directly related to them. However I need to allow people to view records that might not directly be them.
For instance. If a new secretary is hired. And we want her to view only those records associated with the managers that she supports.
How can I accomodate this. I would like to when creating the the new user specify which records they can see and which they cant.
To give the project some more context, it is a task tracking database. So a task is created and assigned to one or more people. Those people need to be able to see the records they have been assigned. Plus their secretaries need to be able to see it. A secretary is never directly assiged a task.
Any suggetsions would be a life saver!

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I think it would be important to know the entire business rule to be able to give you an applicable suggestion. It's possible that you could create another table that stores the usernames that users have access to, or maybe implement some sort of "group" access permission in your database.

Just my 2 cents...

you should not be letting anyone see tables directly, or you cannot accomplish this

one way to do this, is to determine a set of tables that might work, and add into the records some flag that would enable a query to test one against the other - but this may not be so easy.

the other way is to have a function say "showthisitem" that returns a boolean, based on who the user is, and the owner/manager of the record/member of staff - you only select rows where the function returns "true". this is much more easily done, but does require every record to be examined - and therefore as time goes on, you may find you need a way or archiving or deleting old stuff, as the selection may slow down a bit.

the first way lets you use indexes more easily, and is less affected by table size - but may be much harder to achieve

Click to view attachmentClick to view attachment
I am using Access2003-2007
I have attached a screenshot of my relationships.
There is not currently any user table in the relationships.
Tell me if you think this would work.
Create a new table for users (tbl_users)
This table would have emp_username (system login name), password, access (to determine buttons on control panel), name, and persons_assigned (a one to many, to allow multiple personID to be assiged to them)
The i could make a query for assignments based off the personID that the user curently logged in has been assigned to?

Here is the business case:

There is an office with a head project manager. That project manager enters all assignments and assigned them to one or more people. Those people need to be able to log in and view the assignments that they have been assigned. There are 15 people currently that could be assigned something. In addition to these main people there is also a pool of admin assistants that need to be able to log in and see what their supervisor has been assigned. Each admin assistant could support several people.
It is important that only those people only can see those assignments that relate to them, except for the head project manager that should be able to see everything.

First you need to identify the worstation login, and then you can make whatever event coding necessary to restrict access: I don't remember which forum or website I found this on.)



Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Function GetLogonName() As String

' Dimension variables
Dim lpBuff As String * 255
Dim ret As Long

' Get the user name minus any trailing spaces found in the name.
ret = GetUserName(lpBuff, 255)

If ret > 0 Then
GetLogonName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
Else
GetLogonName = "Not found" ' vbNullString
End If
End Function

You will need a parent child relationship in the employee table to map the line management chain.

You can then use this relationship to see tasks assigned to the parent of the employee (Manager) and also children of the employee (Subordinates)