Access VBA - Using Windows Login to establish User Access Levels and login attempts

[nO_OnE]

Basically I pulled the windows login name from the API, I then compair the login name to my contact's list (Which I have updated with the field "WinID", and "Access Level"), then use the DLookup to get that user's access level. This database also uses a simple Append Query to log the Date/time, Windows User name, and access level (If any) when someone tries to "Login" to the "Login_Log" Table.

Then based on which level of access the user has it will open up a specific form. This is a sample database, when I actually use this I establish a "Menu" form for each user group that has links to the forms/reports that they need. Then everytime they close a form it re-runs the code to determine which "Menu" to put them back to.

This was intended for use on a fairly secured network where users cannot just randomly change their user name, and it will only work for a 32 bit environment. If someone has the codes to make this work with a 64 bit system as well please add them.

This is the Module that contains the code to get the User name from the API, and also contains the code that determines which form to open based on the user's access level:

Option Compare Database
Option Explicit

Declare Function wu_GetUserName Lib "advapi32.dll" Alias _
   "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
   As Long
   
Function ap_GetUserName() As Variant
   
   Dim strUserName As String
   Dim lngLength As Long
   Dim lngResult As Long

   '-- Set up the buffer
   strUserName = String$(255, 0)
   lngLength = 255

   '-- Make the call
   lngResult = wu_GetUserName(strUserName, lngLength)
   
   '-- Assign the value
   ap_GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1)
   
End Function

_________________________________________________________________
Function AcLevel()
Dim AccessLevel

    AccessLevel = DLookup("[Access Level]", "Contacts", "WinID=Forms!Login!UserID")
    
    If IsNull((AccessLevel)) Then
        MsgBox "You do not have access to this database.  Please contact your admin if you need access.", vbOKOnly, "Restricted"
        'DoCmd.CloseDatabase

    ElseIf (AccessLevel) = 1 Then
        DoCmd.OpenForm "Getting Started"
    ElseIf (AccessLevel) = 2 Then
        DoCmd.OpenForm "Contact Details"
    ElseIf (AccessLevel) = 3 Then
        DoCmd.OpenForm "Contact List"
    ElseIf (AccessLevel) = 4 Then
        MsgBox "You are the Admin, do as you please!!", vbOKOnly
    Else
        MsgBox "You do not have access to this database.  Please contact your admin if you need access.", vbOKOnly, "Restricted"
        'DoCmd.CloseDatabase
    End If
    
End Function

The following code is for the "Tmr" form, this is just a blank form and I am really only using it to simplify the timer. I used it this way because I prefered to hide the login form and refer to the login form for the access levels, if you setup a timer on a hidden form it will just keep running. For the timer I just used 2000 to give a 2 second delay.

Option Compare Database

Private Sub Form_Load()

    DoCmd.OpenForm "Login"
    
End Sub

Private Sub Form_Timer()

    AcLevel
    Forms![Login].Visible = False
    DoCmd.Close acForm, "Tmr"
    
End Sub

And the code on the "Login" Form:

Private Sub Form_Load()
    
    DoCmd.GoToRecord , , acNewRec
    
    [UserID] = ap_GetUserName
    [Date/Time] = Date + Time
    AccessLevel = DLookup("[Access Level]", "Contacts", "WinID=Forms!Login!UserID")

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Login_Append"
    DoCmd.SetWarnings True
    
End Sub

To test this sample just open it, open the "Contacts" Table, under the WinID type in your windows username for the computer you are on, then select an access level. Then open the "Tmr" Form.


I know there are many other ways to do this, but I found this fairly simple, and quite useful. Another idea would be to replace the access level with the form name that you want them to open to and use that in a string to open up that form.