1 Attachment(s)
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:
Code:
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.
Code:
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:
Code:
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.
