User setup, limited to only their department infomation.

Hi,

Try these steps:

1. Create a table (tUsers) with users names, userids and departments.
2. Get the users network userid with the following code:

new module...

Option Compare Database
Option Explicit
Declare FunctionAPI_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetUserName() As String
   
    Dim lpBuf As String
    Dim ret As Long
   
    lpBuf = String(255, 0)
    ret = API_GetUserName(lpBuf, Len(lpBuf))
    GetUserName = UCase(Left(lpBuf, InStr(lpBuf, Chr(0)) - 1))
   
End Function

... end of module


3.  On your forms/reports join the user table tUser with the department selected by the userid = GetUserName()

Regards,

Bill

Can someone point me to a code that I can use for this.

It takes a lot more that just code to do this. Every query must be updates to filter the records. If form's or report are based on tables then they will have to be updates to use a query that filters the records.

Implement this type of security in Access is something that should be done at the very beginning. It needs to be integrated into everything. It is possible to bolt in onto an existing Access Application but it is a lot of work and possible redesign.

Do you already have user tracking created?
Does your table design and relationships allow you to easily filter everything by department?

Thanks TheHiTechCoach, the database will have only one table and will not be updatable by the users.  Its going to be read only and for informational purposes.  

I will update the database at the backend.  Its only for users to see their reports.  

I really understand your concern if users were going to update the database.  


Thanks

Create a public variable

public glbDept as string '--- or Long depending on what type of field the dept is.

Now, create a funcion to return this value so you can refer to it ANYWHERE...

FUNCION get_glbDept () as string '---- or long depending on what type....
get_glbDept = glbDept
End Function

Create a logon routine that looks up your users and determines their dept (might be a homemade routine).
Assign the User's department to the public variable:    glbDept = {Users Department}



Now, if you are binding your forms straight to tables... make queries instead.  In the query design, in the DEPT column....     = get_glbDept()   for criteria.

Scott C

Further.... Except for VBA code, you cannot reference a variable value straight up.  But.... you can reference a FUNCTION that returns the value.   Using this technique allows you to actually use public variables to control entire systems.

I do it all the time....

Scott C

What if a user (a Director) is allowed to see two or three departments.  How would you create the code for that?.  

Should I have the Director in the users table multiple times based on what department they are allowed to see or have separate fields for each department, like department 1, department 2 etc.  
Another option I am thinking if doing is to have one department field and add the department numbers separated by comma.

Thanks

Create a "child" table to maintain each user's departments (each record is a department number and userid.   Put these in a single query and link this query to your other queries... including all the departments per user.

Instead of public variable for the dept.... do the same thing for the UserID.  Put the "=get_userid()" function in the query that marries both the userid and table of departments.

Scott C

Hi,

As Scott says it's not too difficult but you will need to set up the process.

Here is an article (from a different Scott) that might give you some insight.

http://www.accesssecurityblog.com/author/Scott-Diamond.aspx

Regards,

Bill

I like the answer given by TheHitechcoach