Results 1 to 8 of 8

Opening form based on user and password not working

  1. #1
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    66

    Opening form based on user and password not working

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









    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,308

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    Please use code tags when posting code to preserve indentation. Otherwise code is difficult to read and many responders will not want to spend the time trying to work it out.

    I suspect your problem is probably in this bit of code

    Code:
    '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 & "'")

    Your table does not include a 'login' field (or a securityLevelID) and the highlighted code does not look right.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,353
    You can shorten your code a lot should you care to.
    I 2nd the request re code tags (# on menu). Most times I ignore such posts. Old guys like me lack the patience
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  5. #5
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    66
    Thank you very much for your response. First, I am fairly new to Access and VBA coding. You stated to use code tags when posting code. Can you please define so I can learn and not repeat the mistake next time. If you are referring to using the indents, I originally posted it with a copy and paste from the VB application within access. Looked good until I posted the question and it lost all the indents. I use " ' " to define my objective. Please let me know what I am missing. Second, maybe I wasn't clear in my original posting. My tblEmployee contains the fields [Login] (which is their "User Name") [SecurityLevelID] and [EmployeeNumber] (which is their "Password'). Maybe this will clarify.

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    You stated to use code tags when posting code. Can you please define
    once you have copy/pasted your code, highlight the pasted code and click on the # button to the far right of the edit ribbon

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,554
    might help if you were consistent with your naming - you are saying userID, username and login are all the same. Similarly EmployeeNumber and password.

    Suggest you provide details of your tblEmployee - field names and values for each user - at least the ones that you have the problem with.

    this bit is clearly wrong

    If (IsNull(DLookup("Login", "tblEmployee", "Login ='" & Me.txtUserLogin.Value & "'"))) Or (IsNull(DLookup("EmployeeNumber", "tblEmployee", "EmployeeNumber ='" & Me.txtUserPassword.Value & "'"))) Then
    think about what this is saying - 'if username does not exist or password does not exist'. So you have user joe with password A and user harry with password B. A user enters joe and B. - joe exists, so does password B

  8. #8
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    66
    Ajax, you hit it right on the head. Thank you. Wasn’t looking at it that way. I clarified more parameters and rewrote the code. Switched the if then and now works perfectly. Thank you for the insight.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-28-2017, 03:10 PM
  2. Replies: 4
    Last Post: 08-25-2016, 10:57 AM
  3. Replies: 6
    Last Post: 05-04-2016, 05:30 PM
  4. Replies: 4
    Last Post: 12-16-2015, 02:41 PM
  5. User to upadate Password Form
    By libraccess in forum Forms
    Replies: 2
    Last Post: 11-29-2012, 10:41 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
  •  
Tech Forums: Microsoft Office Forums