Results 1 to 8 of 8
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Advanced Security limiting Access based on Access Levels

    I am hoping someone can help me. I am attempting to set up security and access levels within a database. I have successfully created frmLogin (which has the following vba). The intent is to have a Navigation form with limited access depending on their UserSecurity. (Some of the tabs will be disabled) so far, successful. Then based on their UserAccessLevel, I would like for them to just see their employees. This is where I am stuck. My Navigation form is automatically populated with the Security and AccessLevel in unbound fields based on initial information in the login.

    Code:
    Private Sub cmdOK_Click()
       Dim UserName As String
       Dim TempLogInID As String
       Dim UserAccessLevel As Integer
       Dim UserSecurity As Integer
       If IsNull(Me.txtUserLogin) Then
          MsgBox "Please Enter User ID", vbInformation, "User ID Required"
          Me.txtUserLogin.SetFocus
       ElseIf IsNull(Me.txtPassword) Then
          MsgBox "Please Enter Password", vbInformation, "Password Required"
          Me.txtPassword.SetFocus
       Else
          'process the job
          If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtUserLogin.Value & "'"))) Or (IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then
             MsgBox "Incorrect Login ID or Password"
          Else
             TempLogInID = Me.txtUserLogin.Value
             UserName = DLookup("UserName", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
             UserSecurity = DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
             UserAccessLevel = DLookup("UserAccessLevel", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
             DoCmd.Close
             DoCmd.OpenForm "frmNavigation"
             'Open different form according to user security level
             If UserSecurity = 1 Then   'Level 1 is Director
                Forms![frmNavigation]![txtUserName] = UserName
                Forms![frmNavigation]![txtUserAccessLevel] = UserAccessLevel
                Forms![frmNavigation]![txtUserSecurity] = UserSecurity
                Forms![frmNavigation]![txtUserLogin] = TempLogInID
    
                Call Security(SecurityLevel, AccessLevelID)
    
             End If
          End If
       End If
    End Sub
    
    
    Sub Security(SecurityID As Integer, AccessLevel As Integer)
       Select Case UserAccessLevel
          Case1   'Director_No Restrictions
    
          Case2   'Manager1_CCST_IR_VAT_MRI_CPRehab
    
          Case3   'Manager2_CDL
    
          Case4   'Manager3_CVL
    
          Case5   'Manager4
    
          Case6   'Educator1_CCST_IR_VAT_MRI
    
          Case7   'Educator2
    
          Case8   'Educator3
    
       End Select
    End Sub


    I am having a very hard time getting past Call Security(SecurityLevel, AccessLevelID) as I am getting a ByRef argument type mismatch. I am fairly inexperienced in writing vba and am self taught. Can anyone help me? Very frustrated!!!
    Last edited by RuralGuy; 07-30-2018 at 08:14 AM. Reason: Added code tags and formatting

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Call Security(SecurityLevel, AccessLevelID)
    Nowhere in the sub "cmdOK_Click" are SecurityLevel and AccessLevelID defined (Dim). Are they both controls on the form?
    What values should SecurityLevel and AccessLevelID have when the sub "Security" is called?


    And what is the sub "Security" supposed to do?

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also your case statement is using an undeclared variable

    Sub Security(SecurityID As Integer, AccessLevel As Integer)
    Select Case UserAccessLevel

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ack! missed that!!

    Good catch, Ajax.

  5. #5
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you for your reply. I am very new to VBA and am struggling a bit. I think I fixed the issues, but am still struggling with the UserAccessLevel (this allows the individuals who sign in to have limitiations on editing and viewing) It appears I fixed the SecurityLevel issues (This limits the use of the navigation page based on thier security level). I have attached a stripped down version of my db if someone can help.
    Attached Files Attached Files

  6. #6
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    I forgot to mention, the forms frmLogin, frmNavMain,frmUserInfo, frmAdminEmployee and frmHome are working for the security level. I have included the trial forms that have all the new codes for review.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Error -> You have used special characters in table and field names (ex. table tblLogT&A, two fields in the table - T&ALogID and Hours#)
    Error -> There is a reserved word "Password" in table "tblUser".
    Error -> None of the code modules have the line "Option Explicit" at the top. (IMHO)


    There are lookup FIELDS in the tables. See The Evils of Lookup Fields in Tables (I would remove all lookup FIELDS in the tables)
    Referential Integrity is not set for the relationships in the relationships window. Not a major thing, but in tblEmployee.PositionID, you cannot set RI because somehow a value (7) has been entered that is not in tblPostion.PositionID (at this time, in the dB you posted).


    I modified your log in form code a little.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdOK_Click()
        Dim UserName As String
        Dim TempLogInID As String
        Dim UserAccessLevel As Integer
        Dim UserSecurity As Integer
    
        If IsNull(Me.txtUserLogin) Then
            MsgBox "Please Enter User ID", vbInformation, "User ID Required"
            Me.txtUserLogin.SetFocus
        ElseIf IsNull(Me.txtPassword) Then
            MsgBox "Please Enter Password", vbInformation, "Password Required"
            Me.txtPassword.SetFocus
        Else
            'process the job
            If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtUserLogin.Value & "'"))) Or (IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then
                MsgBox "Incorrect Login ID or Password"
            Else
                TempLogInID = Me.txtUserLogin.Value
                UserName = DLookup("UserName", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
                UserSecurity = DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
                UserAccessLevel = DLookup("UserAccessLevel", "tblUser", "UserLogin = '" & Me.txtUserLogin.Value & "'")
                
                DoCmd.Close acForm, Me.Name  'close log in form
                DoCmd.OpenForm "frmNavMain"
                Forms![frmNavMain]![txtUserName] = UserName
                Forms![frmNavMain]![txtUserAccessLevel] = UserAccessLevel
                Forms![frmNavMain]![txtUserSecurity] = UserSecurity
                Forms![frmNavMain]![txtUserLogin] = TempLogInID
                
                Select Case UserSecurity
                    Case 1    'Level 1 is Admin
                    'all available
                    Case 2 To 9
                        Forms![frmNavMain]!navAdmin.Enabled = False
                    Case Else
                    ' handle unknown UserSecurity number
                End Select
            End If
        End If
    End Sub
    I would write a UDF to check/get the values instead of using DLookups.

    All of my form have queries as the record source. Easier (IMHO) to limit records, sort, filter.
    I never use the navigation form - I am not that familiar with it.
    But if you want to limit employees to a specific supervisor based on the UserAccessLevel, you would need to filter the form on the SupervisorID value. So if supervisor "Ebony" loggen in, the form record source for "My Employees" would need to be filtered on "SupervisorID" = 8.

    The same method would be used on the other forms. You have not specified all the ways you eant to limit usage, but you should be able to figure it out from here.

  8. #8
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you very much for your reply. It was very helpful and set me on the right track.

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

Similar Threads

  1. Different levels of access to same database.
    By Ekhart in forum Security
    Replies: 2
    Last Post: 06-27-2016, 06:42 AM
  2. Replies: 2
    Last Post: 01-16-2015, 06:43 PM
  3. Create Login With 2 Access Levels
    By WickidWe in forum Forms
    Replies: 2
    Last Post: 12-30-2013, 02:38 AM
  4. Security Levels - GetUser()
    By vickan240sx in forum Access
    Replies: 4
    Last Post: 06-13-2012, 11:38 AM
  5. Form for users to set security levels
    By smahdih in forum Forms
    Replies: 2
    Last Post: 11-14-2011, 06:22 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
  •  
Other Forums: Microsoft Office Forums