Results 1 to 1 of 1
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2010

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

    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"
        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
            MsgBox "You do not have access to this database.  Please contact your admin if you need access.", vbOKOnly, "Restricted"
        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()
        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.
    Attached Files Attached Files
    Last edited by nO_OnE; Nov 28th, 2010 at 06:18 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.