Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31

    How To Set User Rights From Access - From Scratch

    Hi,



    I am not sure weather I have to post this question in this section of forum or in SQL Server or in General. Anyways.

    Ihave distributed my Access FE to 2 users on their PC. At the moment they havefull rights to access all the forms in db. Tables are getting connected to SQLServer based on DSN less connecting string when user log In thru a simple loginform. It is working smoothly. These 2 users have full permission to use all theforms so issue.

    Butif I want to deploy my FE copies to other users who are entitled to use onlyfew forms then I have to limit their access for few forms only. I searched butthe login form database sample doesn’t have forms rights included in. Or may beI missed to see any sample db

    Cansomeone guide me from scratch how to set up the rights. I believe I need tocreate a tbl that includes UserID, UserName, AccessLevel something like.

    Itried with reading machine id and to place it on main form (which is alwayskept open) somewhere in hidden text box and then accordingly each form shouldhave vba code on its OnOpen event to check if the machine id matches andaccordingly the form opens or display access denied msg. But is it messing up.Is this the right way I am going ahead.

    Isthere any other method or someone can guide me please...

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I do this, users table has levels:
    Manager
    Admin
    User
    (you could have more)


    tUsers table:
    bobS, M
    bethH, A
    danaM, U


    when user opens the app, grab the user's ID, then get their user 'rights' from the tUsers table:
    gvUserID= Environ("Username")
    vLevel = Dlookup("[Level]","tUsers","[UserID]='" & gvUserID & "'")


    when the form loads, enable/disable buttons according to level:


    Code:
    public gvUserID 
    
    
    sub form_load()
    dim vLevel
    
    
       gvUserID =  Environ("Username")      'get userID,visible in all forms
    
    
    'get level from user table
       vLevel = Dlookup("[Level]","tUsers","[userID]='" & gvUserID & "'")
    
    
    'now, enable/disable items on form
       select case vLevel
             case "A"  'admin 
                 'all is enabled
    
    
             case "U"  'normal user
                 txtBox1.enabled = false
                 txtManager.enabled = false
    
    
             case "M"  'manager
                 txtBox1.enabled = false
       end select
    end sub

  3. #3
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Thanks a lot Ranman256,

    I will start it and try to make it as yours. And let you know if there I face any difficulties.

  4. #4
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Just only one small quick question...

    Is this one table or 2 separate tables?

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. As stated, record the user access level in a table tblUsers. See Password Login - Mendip Data Systems for an example.
    2. Hide the navigation pane from all users so all interaction is done using forms. Add other security if you wish. See Improve Security - Mendip Data Systems
    3. Use code to determine which forms are open to standard users and those that aren't. Your main menu form should contain buttons to open other forms and which are all available to your admin users. However buttons for restricted forms should be either disabled or hidden to standard users. Use Select Case statements for this e.g.

    Code:
    Private Sub Form_Load()
    
    Select Case GetAccessLevel
    
    Case 1 'standard user
    Me.cmdForm1.Enabled=False
    Me.cmdForm2. Enabled=False
    
    Case 2 'admin user
    Me.cmdForm1.Enabled =True
    Me.cmdForm2.Enabled=True
    
    End Select
    
    End Sub

    If you have a lot of controls to manage, you can use the Tag property to group the controls and reduce the amount of code needed. See Set Controls - Mendip Data Systems
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please feel free to use my free utility http://forestbyte.com/ms-access-util...access-levels/ that allows you to set up your variable access down to individual controls on the forms.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Quote Originally Posted by Gicu View Post
    Please feel free to use my free utility http://forestbyte.com/ms-access-util...access-levels/ that allows you to set up your variable access down to individual controls on the forms.

    Cheers,
    Thanks GICU,

    I tried copying all your modules, hiddentbls and forms to my db and started to arrange it. Your step # 3 ..combo is generating all the controls of selected form. My login form has combo that generates user list. So I have selected CboEmployee in your 3rd combo. Then as per your step # 4, again I have to click on Set New Rule btn ?

    How come? if the first is not completed. Even I follow your step 4, the first entry I just made is disappearing and again we have to select Object type from first combo and repeating 1-3 steps. So can you explain where is wrong - I followed your steps...

    Another question: Let suppose I have 3 forms A, B & C and 3 Users 1, 2 & 3.
    Do you think that if I set form A for user 3 with full writes then the form A will not available for user 1 & 2 by default ? OR do I need to add user 1 & 2 for form A but check marks will be highlighted as negative or 0 something? In other words if I do not set rights for 1 & 2 user so can they open form A?

    THanks in advance...

  8. #8
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Quote Originally Posted by ranman256 View Post
    I do this, users table has levels:
    Manager
    Admin
    User
    (you could have more)


    tUsers table:
    bobS, M
    bethH, A
    danaM, U


    when user opens the app, grab the user's ID, then get their user 'rights' from the tUsers table:
    gvUserID= Environ("Username")
    vLevel = Dlookup("[Level]","tUsers","[UserID]='" & gvUserID & "'")


    when the form loads, enable/disable buttons according to level:


    Code:
    public gvUserID 
    
    
    sub form_load()
    dim vLevel
    
    
       gvUserID =  Environ("Username")      'get userID,visible in all forms
    
    
    'get level from user table
       vLevel = Dlookup("[Level]","tUsers","[userID]='" & gvUserID & "'")
    
    
    'now, enable/disable items on form
       select case vLevel
             case "A"  'admin 
                 'all is enabled
    
    
             case "U"  'normal user
                 txtBox1.enabled = false
                 txtManager.enabled = false
    
    
             case "M"  'manager
                 txtBox1.enabled = false
       end select
    end sub
    You code is no doubt clear and a bit easy but I feel it has limitation. i.e. when there are 2 Managers and one we need to provide diff form access but other manager do not need those form or including those form his requirement is more forms. So in such kind of situations it is a bit difficult to use your code. Then Select Case will be a lengthy context.

    Code is good for simillar right users.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi @ashfaque.
    Did you look at the examples linked in post #5?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi,
    After you populate the three combos you need to setup the rule for each of the user levels (in your example 1,2 and 3) that you want for that control (combo) or form. Then click Save rule and you will see the rule saved as individual records in the subform on the bottom of the form (you can afterwards edit those records directly). If you're adding similar rules for the same object's controls (i.e. form) you d not need to clcik the New Rule button, just change the control name in the combo then click Save Rule (if the rule for the control is the same as the one for the previous control).
    The levels have distinct rules, there is no inherited hierarchy (so 1 isn't higher or lower than 2, they have their own set of rules as defined in the usysAccessRule table.

    HTH.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Quote Originally Posted by isladogs View Post
    1. As stated, record the user access level in a table tblUsers. See Password Login - Mendip Data Systems for an example.
    2. Hide the navigation pane from all users so all interaction is done using forms. Add other security if you wish. See Improve Security - Mendip Data Systems
    3. Use code to determine which forms are open to standard users and those that aren't. Your main menu form should contain buttons to open other forms and which are all available to your admin users. However buttons for restricted forms should be either disabled or hidden to standard users. Use Select Case statements for this e.g.

    Code:
    Private Sub Form_Load()
    
    Select Case GetAccessLevel
    
    Case 1 'standard user
    Me.cmdForm1.Enabled=False
    Me.cmdForm2. Enabled=False
    
    Case 2 'admin user
    Me.cmdForm1.Enabled =True
    Me.cmdForm2.Enabled=True
    
    End Select
    
    End Sub

    If you have a lot of controls to manage, you can use the Tag property to group the controls and reduce the amount of code needed. See Set Controls - Mendip Data Systems
    Thanks isladogs,

    Today I will go thru it. I already downloaded. I will feedback soon.

    Thanks Again.

  12. #12
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Quote Originally Posted by isladogs View Post
    Hi @ashfaque.
    Did you look at the examples linked in post #5?
    Hi Isladogs,

    I have tested it all the way. It is working well. After using it I came to the conclusion that no doubt your user rights database is an excellent one. Thanks for sharing it with our access community. My personal thought is it would be more flexible and beneficial if there could be details rights are set. In my Geotechnical dept., I have 5 staff and they have assigned to for different task but all they are considered as Standard User of my db. Let us say I have some forms : Sales Invoice, Purchase Invoice, Store, Dispatch etc.
    These above tasks are performed by my staff. But I have assigned 1 staff for Sales invoicing only. Other 2 Staff for Purchase Inv and Store and rest 2 takes care of Dispatch. They are all considered as my Standard user. Now the problem is if they are considered StdUsr then we have only one facility of accessing form that is similar for all StdUsr.
    Where as in my case the staff who is handling Sales Invoice DO NOT have access to Purchase Inv Or Store. So my suggestion is if you could set your db enable to handle this situation, would be a more flexible solution.

    Because in general developers make Login Form and then Main form where all the switches are placed and from where all end users access their area. If we set StdUser rights in you current db, the same rules shall be applied for all std user. I hope I have made my point clear. But not doubt your current db is a great gift too all new developers like me.

    Regards,

  13. #13
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Hi Isladogs,

    Quote Originally Posted by Ashfaque View Post
    Thanks isladogs,

    Today I will go thru it. I already downloaded. I will feedback soon.

    Thanks Again.
    I imported all your objects in my db and when starts using FrmLogin, displyed error msg on below line
    ByteArray(x) = ByteArray(x) Xor (rb((rb(Y) + rb(Z)) Mod 256))

    Run-time error '9' - Subscript out of range

    I just noticed that this msg displayed bcz the tblUsers linked from SQL Server. And when I make it local table, the form is working. So let me know the solution to this please.

    Regards,
    Last edited by Ashfaque; 03-04-2021 at 02:14 AM. Reason: Server Tbl vs Local Tbl

  14. #14
    Ashfaque is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    31
    Thanks Isladogs,

    The error was bcz I had shifted your tbl to Server and there PK was removed. I set it again and working fine.....

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad to see you found the reason for the error.

    However, I was able to reproduce the subscript out of range error about a year ago.
    There is a newer version of the RC4 function on my web page: Encrypted Spilt No Strings - Mendip Data Systems
    One code line has been replaced to fix that error:

    Code:
    '''
       ByteArray() = StrConv(Expression, vbFromUnicode)
    
      'Next line changed after discussion with Chris Arnold as it causes error 9 - subscript out of range
       'arrays start at 0 so this needs to end with Len(Expression)-1
       ' For x = 0 To Len(Expression)
        For x = 0 To Len(Expression) - 1     'Colin Riddington - 14/03/2019
    
        Y = (Y + 1) Mod 256
    ...
    I suggest you modify the code in the Password Login app

    Regarding your comments in post #12, remember this is an example database and it is intended that anyone downloading it will be able to adapt it to meet their own needs.
    Whilst I could make modifications that would suit you, they wouldn't work for another developer.
    Last edited by isladogs; 03-04-2021 at 12:56 PM. Reason: Formatting of code comments
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hide a column in a report based on user rights
    By whisp0214 in forum Reports
    Replies: 6
    Last Post: 06-29-2017, 03:11 PM
  2. Log on form and user rights
    By HS_1 in forum Forms
    Replies: 18
    Last Post: 12-28-2016, 07:56 PM
  3. Problems with network rights and Access
    By Wayne311 in forum Security
    Replies: 3
    Last Post: 09-07-2011, 06:00 PM
  4. User always appear to connect with exclusive rights
    By kblinkhorn in forum Security
    Replies: 18
    Last Post: 09-02-2010, 01:07 PM
  5. Access Novice - Looking to learn from scratch.
    By Javaman in forum Database Design
    Replies: 1
    Last Post: 03-13-2010, 08:53 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