Good afternoon, I have a DB that I am working with to allow access to specific forms based on security level. Each employee has a user name and a password assigned to them in the employee table. The following VBA shows code based on the employee's security level. When the user places the user name and password, it opens correctly. My problem..They can put any password that is in the employee table in the field and the form will open. See table. In short, the user can open the forms using any password in the employee table password column. Not such a great idea. lol
User Name Password Result jdoe 1 (assigned) All forms and security accurate tbrown 2 (assigned) All forms and security accurate jdoe xxx This password does not exist and access denied jdoe 2 This password belongs to tbrown but all forms and security open correctly for jdoe
Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
Dim EmployeeName As String
Dim TempLogInID As String
Dim SecurityLevelID As Integer
Dim EmployeeID As Integer
'Process for IsNull Login fields
If IsNull(Me.txtUserLogin) Then
MsgBox "Please Enter User ID", vbInformation, "User ID Required"
Me.txtUserLogin.SetFocus
ElseIf IsNull(Me.txtUserPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required"
Me.txtUserPassword.SetFocus
Else
'Process for Is Not Null Login fields
If (IsNull(DLookup("Login", "tblEmployee", "Login ='" & Me.txtUserLogin.Value & "'"))) Or (IsNull(DLookup("EmployeeNumber", "tblEmployee", "EmployeeNumber ='" & Me.txtUserPassword.Value & "'"))) Then
MsgBox "Incorrect Login ID or Password"
Else
TempLogInID = Me.txtUserLogin.Value
EmployeeID = DLookup("EmployeeID", "tblEmployee", "Login = '" & Me.txtUserLogin.Value & "'")
EmployeeName = DLookup("EmployeeName", "tblEmployee", "Login = '" & Me.txtUserLogin.Value & "'")
SecurityLevelID = DLookup("SecurityLevelID", "tblEmployee", "Login = '" & Me.txtUserLogin.Value & "'")
'If Login is correct, then process the job
DoCmd.OpenForm "frmNavMain"
Forms![frmNavMain]![txtEmployeeName] = EmployeeName
Forms![frmNavMain]![txtEmployeeID] = EmployeeID
Forms![frmNavMain]![txtSecurityLevelID] = SecurityLevelID
Forms![frmNavMain]![txtLogin] = TempLogInID
Select Case SecurityLevelID
Case 1 'Admin
Forms![frmNavMain]!navHome.Enabled = True
Forms![frmNavMain]!navUserInfo.Enabled = True
Forms![frmNavMain]!navEmployees.Enabled = True
Forms![frmNavMain]!navCert.Enabled = True
Forms![frmNavMain]!navTA.Enabled = True
Forms![frmNavMain]!navMag.Enabled = True
Forms![frmNavMain]!navAdmin.Enabled = True
Forms![frmNavMain]!navExtra.Enabled = True
Forms![frmNavMain]!NavClose.Enabled = True
Case 2 'Director
Forms![frmNavMain]!navHome.Enabled = True
Forms![frmNavMain]!navUserInfo.Enabled = True
Forms![frmNavMain]!navEmployees.Enabled = True
Forms![frmNavMain]!navCert.Enabled = True
Forms![frmNavMain]!navTA.Enabled = True
Forms![frmNavMain]!navMag.Enabled = True
Forms![frmNavMain]!navAdmin.Enabled = True
Forms![frmNavMain]!navExtra.Enabled = True
Forms![frmNavMain]!NavClose.Enabled = True
Case 3 'Manager
Forms![frmNavMain]!navHome.Enabled = True
Forms![frmNavMain]!navUserInfo.Enabled = True
Forms![frmNavMain]!navEmployees.Enabled = True
Forms![frmNavMain]!navCert.Enabled = True
Forms![frmNavMain]!navTA.Enabled = True
Forms![frmNavMain]!navMag.Enabled = True
Forms![frmNavMain]!navAdmin.Enabled = False
Forms![frmNavMain]!navExtra.Enabled = True
Forms![frmNavMain]!NavClose.Enabled = True
Case 9 'Educator
Forms![frmNavMain]!navHome.Enabled = True
Forms![frmNavMain]!navUserInfo.Enabled = True
Forms![frmNavMain]!navEmployees.Enabled = True
Forms![frmNavMain]!navCert.Enabled = True
Forms![frmNavMain]!navTA.Enabled = False
Forms![frmNavMain]!navMag.Enabled = False
Forms![frmNavMain]!navAdmin.Enabled = False
Forms![frmNavMain]!navExtra.Enabled = True
Forms![frmNavMain]!NavClose.Enabled = True
Case 10 'Employee
Forms![frmNavMain]!navHome.Enabled = True
Forms![frmNavMain]!navUserInfo.Enabled = True
Forms![frmNavMain]!navEmployees.Enabled = False
Forms![frmNavMain]!navCert.Enabled = False
Forms![frmNavMain]!navTA.Enabled = False
Forms![frmNavMain]!navMag.Enabled = False
Forms![frmNavMain]!navAdmin.Enabled = False
Forms![frmNavMain]!navExtra.Enabled = False
Forms![frmNavMain]!NavClose.Enabled = True
Case Else
' handle unknown UserSecurity number
End Select
End If
End If
End Sub
Any help would be appreciated
Thank you