Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
  1. #16
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This can be quite complex. There are lots of ways to achieve this - some of the more elegant ones involve using a hidden form to store the current users Name and Access Level.


    You can then check this hidden forms data when you open a form and set your forms controls accordingly.

    If you get very clever with it you could store the access level, controls, Control status and form names in a table and use that to set form level control locking.
    It's pretty involved but ultimately much easier to manage, than hard coding each form.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  2. #17
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Im not sure if it was the easy or harder way to do it but just tried to add to that previous code from a utube search wasnt able to get it going though, i built a department and a security level table and been at it 15hours today trying to get this user name and password running... so pretty knackered ill give it another crack tomoz.
    If theres any info you could flick my way much appreciated and or if ya got any ideas im all ears.

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you only have a few forms and feel the approach outlined by minty is too much for your needs then do something like this

    1. Add a field to your user table with user access level e.g admin
    2. Create function GetUserStatus to look up the value
    3. In the form load event of each form, add code like
    Code:
    if GetUserStatus="Admin" Then
    'code here to disable or hide controls
    ElseIf GetUserStatus="Office" Then
    ... etc
    Better still use Select Case statements

    You can also group controls using the Tag property so all with say tag A are disabled as a group ... etc
    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

  4. #19
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    not thats it to much im just not experienced enough with coding and how it works....im def giving it a crack though
    Yeah i have added a securitylevel in the employees table..... To easy cheers ill give that a crack tomorrow

  5. #20
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    This can be quite complex. There are lots of ways to achieve this - some of the more elegant ones involve using a hidden form to store the current users Name and Access Level.
    You can then check this hidden forms data when you open a form and set your forms controls accordingly.

    If you get very clever with it you could store the access level, controls, Control status and form names in a table and use that to set form level control locking.
    It's pretty involved but ultimately much easier to manage, than hard coding each form.
    I like this idea Minty do you have any code to start me off in the right direction?

  6. #21
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	12 
Size:	109.7 KB 
ID:	32243

    is this this roughly what you meant by create table with form level control locking

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I can't work out the logic in your examples ....

    Surely each level of security includes the level below it
    So Workshop has limited security clearance ; Office has that plus some more ; Admin has Office security + a bit more; Director has Admin + all the rest
    In which case, users only need one security description
    If as I suggest, users can only have one of these levels, a single text field UserSecurity will suffice with possible values: Director/Admin/Office/Workshop

    However if it is possible for users to have more than one of these security levels, keep the 4 fields but use boolean (Yes/No) datatype for each
    This will give you a checkbox for each field - tick if true
    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

  8. #23
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by ridders52 View Post
    I can't work out the logic in your examples ....

    Surely each level of security includes the level below it
    So Workshop has limited security clearance ; Office has that plus some more ; Admin has Office security + a bit more; Director has Admin + all the rest
    In which case, users only need one security description
    If as I suggest, users can only have one of these levels, a single text field UserSecurity will suffice with possible values: Director/Admin/Office/Workshop

    However if it is possible for users to have more than one of these security levels, keep the 4 fields but use boolean (Yes/No) datatype for each
    This will give you a checkbox for each field - tick if true
    yeah I wasn't sure if yes/no would be ok when used with vba

  9. #24
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If your levels are as simple as the sample you posted I would go with Ridders example of a simple 1,2,3,4 method.
    You can store someones access level in the employees table, look that up at login, and simply enable controls as per the level.

    As mentioned you can use the Tag property on controls and simply set it to 0 or null = always available or 1, 2, 3, or 4 depending on requirements.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #25
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    If your levels are as simple as the sample you posted I would go with Ridders example of a simple 1,2,3,4 method.
    You can store someones access level in the employees table, look that up at login, and simply enable controls as per the level.

    As mentioned you can use the Tag property on controls and simply set it to 0 or null = always available or 1, 2, 3, or 4 depending on requirements.
    yeah that would work, would the code look like this for look up at login?

    I have replaced usersecurity with AccessLevelID in the database....

    IF AccessLevelID = DLookup("[AccessLevelID]", "tblEmployees", "[LoginID] = '" & Me.txtLoginID & "'") Then

    im a little confused as to where to put it and and if that's all I need for what your saying.

  11. #26
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    If your levels are as simple as the sample you posted I would go with Ridders example of a simple 1,2,3,4 method.
    You can store someones access level in the employees table, look that up at login, and simply enable controls as per the level.

    As mentioned you can use the Tag property on controls and simply set it to 0 or null = always available or 1, 2, 3, or 4 depending on requirements.
    Private Sub cmdAdmin_Click()
    If DLookup("[AccessLevelID]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 Then
    DoCmd.OpenForm "Admin"
    Else
    'MsgBox "You are Not Authorised, See Admin!", vbOKOnly
    End If
    End Sub

    I have gotten this to work just a bit of time selecting every cmd and changing to suit... although I can get the message box to come up...

  12. #27
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    On form load cycle through the controls using something like

    Code:
    Dim iAccessLvl as Interger
    Dim ctl as Control
    
    iAccessLvl =  DLookup("[AccessLevelID]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) 
    
    For Each ctl In Controls            
               If ctl.Tag > iAccessLvl Then
                    ctl.Locked = True
                    Debug.Print ctl.Name
                End If
    Next ctl
    Does that make sense - You might need to play with the control tags values to allow for not locking them, but that should be obvious once you see it working.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #28
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Minty View Post
    On form load cycle through the controls using something like

    Code:
    Dim iAccessLvl as Interger
    Dim ctl as Control
    
    iAccessLvl =  DLookup("[AccessLevelID]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) 
    
    For Each ctl In Controls            
               If ctl.Tag > iAccessLvl Then
                    ctl.Locked = True
                    Debug.Print ctl.Name
                End If
    Next ctl
    Does that make sense - You might need to play with the control tags values to allow for not locking them, but that should be obvious once you see it working.
    im a little confused sorry... on load on the form or.... what do you mean through the controls?

  14. #29
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Click image for larger version. 

Name:	Untitled1.jpg 
Views:	11 
Size:	127.6 KB 
ID:	32245

    getting this error from where I have put it

  15. #30
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's because I can't type.

    It should be
    Dim iAccessLvl as Integer
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Runtime error 3464
    By gunterbrink in forum Programming
    Replies: 15
    Last Post: 12-29-2016, 07:58 PM
  2. Syntax Error 3464 Need help
    By draalderks in forum Forms
    Replies: 13
    Last Post: 06-09-2015, 10:11 AM
  3. error 3464 - Password change form
    By Sheba in forum Forms
    Replies: 14
    Last Post: 10-14-2014, 11:48 AM
  4. Execution error 3464
    By Trisha in forum Access
    Replies: 3
    Last Post: 03-03-2014, 01:03 PM
  5. 3464 error received
    By TEN in forum Programming
    Replies: 10
    Last Post: 07-08-2009, 07:25 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