Creating user level access based on department

[Bill]

This forum has been great and I really appreciate it. I'm looking to expand
my database a step further. We have a database for incident reporting which
tracks things within our facility. For every incident there is a department
assigned.

What I want to do is allow the department heads to open access and view only
their department(s). I am using the windows log in username and passing that
to access. I have a security script which determines the users level based
on a table of read, edit or admin.

Now I want to expand this to filter the reports to those in the department
they are assigned. In some cases, like me for example, I am over several
departments and need to view many. Others will only view one department.

The table everything is stored in is DataSheet and the column for the
department is titled Department.

Any help and guidance you can lend would be GREATLY appreciated!!! Thx.

 

[bhicks11 via AccessMonster.com]

If you already have the department captured, base the report on a query that
filters on department.

Bonnie
http://www.dataplus-svc.com

 

[Bill]

Sorry, I'm not trying to create a report. I need to restrict access to
various areas or tabs bassed on the department the user would be assigned.

 

[bhicks11 via AccessMonster.com]

The same query will work for browsing in a datasheet. Set the criteria for
the department based on the user. Are you using the FOSUsername() function?


Bonnie
http://www.dataplus-svc.com

Sorry, I'm not trying to create a report. I need to restrict access to
various areas or tabs bassed on the department the user would be assigned.

If you already have the department captured, base the report on a query that
filters on department.

[quoted text clipped - 20 lines]

 

[Bill]

ok. So I think I get your idea. So let me explain a little more just to
clarify. I have an incident system which when a incident needs to be created
a user logs in and creates it. The incident normally will reference a
department where it occurred. So I want to expand this function now and
allow the department head to log in and see their departments only.

So you are saying, grant the department head access (add a column to the
user table to show which department each person is associated with) then each
time a report or query is ran, it needs a VBscript to determine the
department the user is associated with and then filter the query or report
based on this?

So, I see the concept, but to be honest have I no idea how to create. Thx.

The same query will work for browsing in a datasheet. Set the criteria for
the department based on the user. Are you using the FOSUsername() function?


Bonnie
http://www.dataplus-svc.com

Sorry, I'm not trying to create a report. I need to restrict access to
various areas or tabs bassed on the department the user would be assigned.

If you already have the department captured, base the report on a query that
filters on department.

[quoted text clipped - 20 lines]

Any help and guidance you can lend would be GREATLY appreciated!!! Thx.

 

[bhicks11 via AccessMonster.com]

I would make another little table with Dept and Dept Login ID, etc. (not a
new field). Add that table to the Query grid, relate it on the Dept, add the
LoginID field to the grid and filter it for the user that's logged in.

When your user clicks the button or whatever, open the query which will be
filtered on his ID. I use the FOSUserName() function courtesy of Dev Ashish.

If this is over your level of expertise and you are trying to learn, the best
thing is to jump in and start trying. Don't do it in your actual application,
make a copy and have at it. Post questions when you get stuck.

Here's the code and you can just add it to a module and then filter on
FOSUserName().

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function

Bonnie
http://www.dataplus-svc.com

ok. So I think I get your idea. So let me explain a little more just to
clarify. I have an incident system which when a incident needs to be created
a user logs in and creates it. The incident normally will reference a
department where it occurred. So I want to expand this function now and
allow the department head to log in and see their departments only.

So you are saying, grant the department head access (add a column to the
user table to show which department each person is associated with) then each
time a report or query is ran, it needs a VBscript to determine the
department the user is associated with and then filter the query or report
based on this?

So, I see the concept, but to be honest have I no idea how to create. Thx.

The same query will work for browsing in a datasheet. Set the criteria for
the department based on the user. Are you using the FOSUsername() function?

[quoted text clipped - 10 lines]

 

[bhicks11 via AccessMonster.com]

Missed a line below:

"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Goes after the Private Declare line.

Bonnie
http://www.dataplus-svc.com

I would make another little table with Dept and Dept Login ID, etc. (not a
new field). Add that table to the Query grid, relate it on the Dept, add the
LoginID field to the grid and filter it for the user that's logged in.

When your user clicks the button or whatever, open the query which will be
filtered on his ID. I use the FOSUserName() function courtesy of Dev Ashish.

If this is over your level of expertise and you are trying to learn, the best
thing is to jump in and start trying. Don't do it in your actual application,
make a copy and have at it. Post questions when you get stuck.

Here's the code and you can just add it to a module and then filter on
FOSUserName().

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function

Bonnie
http://www.dataplus-svc.com

ok. So I think I get your idea. So let me explain a little more just to
clarify. I have an incident system which when a incident needs to be created

[quoted text clipped - 15 lines]