Results 1 to 13 of 13
  1. #1
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22

    Login to Access as "Admin" or "User" allowing different Editing, Additions and Deletions permission

    Hi all,



    I'm very new to access. I really need your help figuring this out.

    After logging in as admin, I would like the Admin to be able to add, edit and modify in the Form Fields
    Whereas for users, I would like the Users only be able to add and view the form.

    It's really simple, but I can't figure out where I am going wrong. Please assist.


    Private Sub Command1_Click()
    Dim UserLevel As String
    If IsNull(Me.txtLogin_ID) Then
    MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
    Me.txtLogin_ID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
    Else
    'process the job
    If (IsNull(DLookup("[User Login]", "Security Level", "[User Login] ='" & Me.txtLogin_ID.Value & "' And Password = '" & Me.txtPassword.Value & "'"))) Then
    MsgBox "Incorrect Login ID or Password"
    Else
    UserLevel = DLookup("[User Security]", "Security Level", "[User Login] ='" & Me.txtLogin_ID.Value & "'")
    DoCmd.Close
    If UserLevel = "Admin" Then
    'MsgBox "Login ID and Password correct:"
    DoCmd.OpenForm "Client Details"
    With frm
    Me.AllowAdditions = True
    Me.AllowEdits = True
    Me.AllowDeletions = True
    DoCmd.Close (acForm)
    DoCmd.OpenForm "Switchboard"
    ElseIf UserLevel = "user" Then
    With frm
    Me.AllowAdditions = True
    Me.AllowEdits = True
    Me.AllowDeletions = True
    DoCmd.Close (acForm)
    DoCmd.OpenForm "Switchboard"
    Else
    DoCmd.OpenForm "Switchboard"
    End If
    End If
    End If
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Missing End With lines. Reference variable frm but it is not declared nor set. Probably other issues. Step debug.

    This is a common topic. Here is one thread https://www.accessforums.net/showthread.php?t=23585

    Should post lengthy code between CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    thank you for your reply.

    Could you help me with a code that you think would work?

  4. #4
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Still not working


    Code:
    Private Sub Command1_Click()Dim UserLevel As String
    If IsNull(Me.txtLogin_ID) Then
        MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
        Me.txtLogin_ID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please enter password", vbInformation, "Password Required"
        Me.txtPassword.SetFocus
    Else
        'process the job
        If (IsNull(DLookup("[User Login]", "Security Level", "[User Login] ='" & Me.txtLogin_ID.Value & "'  And Password = '" & Me.txtPassword.Value & "'"))) Then
            MsgBox "Incorrect Login ID or Password"
        Else
            UserLevel = DLookup("[User Security]", "Security Level", "[User Login] ='" & Me.txtLogin_ID.Value & "'")
            DoCmd.Close
                If UserLevel = "Admin" Then
                    'MsgBox "Login ID and Password correct:"
                    DoCmd.OpenForm "Client Details"
                        Me.AllowAdditions = True
                        Me.AllowEdits = True
                        Me.AllowDeletions = True
                    DoCmd.Close (acForm)
                    DoCmd.OpenForm "Switchboard"
                ElseIf UserLevel = "user" Then
                        Me.AllowAdditions = True
                        Me.AllowEdits = True
                        Me.AllowDeletions = True
                    DoCmd.Close (acForm)
                    DoCmd.OpenForm "Switchboard"
                Else
                    DoCmd.OpenForm "Switchboard"
                End If
        End If
    End If
    End Sub

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you setting properties the same for both conditions? AllowEdits and AllowDeletions should be False for "user". The Me qualifier is acting on the Login form not Client Details.

    I would not try to change properties and save form. As explained in referenced thread, I would save the user info to a form that never closes (in your case that is Switchboard). Then code behind Client Details would set its own properties based on user info on Switchboard.

    Advise not to use spaces in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Oh yea!!! I see what you are saying... That makes sense!!! Will try to work on it... Can i direct message you?


    Quote Originally Posted by June7 View Post
    Why are you setting properties the same for both conditions? AllowEdits and AllowDeletions should be False for "user". The Me qualifier is acting on the Login form not Client Details.

    I would not try to change properties and save form. As explained in referenced thread, I would save the user info to a form that never closes (in your case that is Switchboard). Then code behind Client Details would set its own properties based on user info on Switchboard.

    Advise not to use spaces in naming convention.

  7. #7
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    June... This is the db.

    I am trying to get user

    Me.AllowAdditions = True
    Me.AllowEdits = False
    Me.AllowDeletions = False

    and user can do data entry also....

    And for Admin
    Me.AllowAdditions = True
    Me.AllowEdits = True
    Me.AllowDeletions = True

    and ofcourse can do data entry too...
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I never use the Switchboard wizard nor macro coding. Most experienced programmers avoid both. There is a reason MS has deprecated that form structure.

    The following is code I would use and I would not use Switchboard wizard to set up my 'main menu' form. I would use VBA behind buttons. I have not looked at the macro code to see how it might impact this process.

    Code behind Login form.
    Code:
    Private Sub Command1_Click()
    Dim strUser As String
    If IsNull(Me.txtLogin_ID) Then
        MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
        Me.txtLogin_ID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please enter password", vbInformation, "Password Required"
        Me.txtPassword.SetFocus
    Else
        'process the job
        strUser = Nz(DLookup("[User Login]", "Security Level", "[User Login] ='" & Me.txtLogin_ID.Value & "'  And Password = '" & Me.txtPassword.Value & "'"), "")
        DoCmd.Close
        If strUser = "" Then
            MsgBox "Incorrect Login ID or Password"
        Else
            'MsgBox "Login ID and Password correct:"
            DoCmd.OpenForm "Switchboard", , , , , , strUser
        End If
    End If
    End Sub
    Create a textbox on Switchboard named tbxUser - can be not visible. Then code behind Switchboard.
    Code:
    Private Sub Form_Load()
    Me.tbxUser = Me.OpenArgs
    End Sub
    Code behind Client Details.
    Code:
    Private Sub Form_Load()
    If Forms!Switchboard.tbxUser = "user" Then
        Me.AllowAdditions = True
        Me.AllowEdits = False
        Me.AllowDeletions = False
    End If
    End Sub
    All code modules should have the following two lines in header.
    Option Compare Database
    Option Explicit

    To force the Option Explicit as default, in the VBE window > Tools > Options > check Require Variable Declaration.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    It worked brilllliantly!!!!!

    THANK YOU SOOO MUCH June7!!!!!! You are a genius!!!

  10. #10
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Hi June7,

    Just a few queries. Everything is working perfectly, but there are a few hiccups.

    When we login now as user, all the fields are now locked, which is exactly what we wanted, but I wanted two fields in which i could make further edits.
    - I would like the user to be able to make edits in two fields "Notes" & "txtsearch"
    Like
    Me.AllowEdits = False
    except field.txtsearch & field.Notes


    Also, I would like the user to be able to Insert Object. As Admin, I can make all the changes, but not able to do so with the User login.

    Please help.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Then instead of setting the form AllowEdits property to False, would have to individually lock each control you want to prevent edit in by setting Locked or Enabled properties to True or False.
    Last edited by June7; 10-01-2018 at 02:37 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    rubs34 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Okay, Noted. Though We require the use of admin and user login.

    Also, I would like the user to be able to Insert Object in one field. As Admin, I can right-click and insert object, but not able to do so with the User login.
    Can you help me with this?

    Quote Originally Posted by June7 View Post
    Then instead of setting the form AllowEdits property to False, would have to individually lock each control you want to prevent edit in by setting Locked or Enabled properties to False.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    As I said, instead of code setting AllowEdits property, set the Locked or Enabled properties.

    If Forms!Switchboard.tbxUser = "user" Then
    Me.AllowDeletions = False
    Me.yourfirstcontrolname.Locked = True '(or set Enabled to False, which might be better because users cannot even click into the control and TabStop will not apply)
    etc
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-12-2018, 11:26 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  4. Replies: 2
    Last Post: 01-03-2014, 09:35 AM
  5. Replies: 6
    Last Post: 04-25-2013, 10:08 AM

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