Link to home
Create AccountLog in
Avatar of Kdankwah
KdankwahFlag for United States of America

asked on

User setup, limited to only their department infomation.

I have an MS Access 2010 database that I would like users to access, the problem is I would like each user to see only their department information.  Can someone point me to a code that I can use for this.  I am desperate.  

Avatar of Bill Ross
Bill Ross
Flag of United States of America image


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()


Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
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?
Avatar of Kdankwah


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.  

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.

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

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.


Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I like the answer given by TheHitechcoach