Results 1 to 6 of 6
  1. #1
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40

    Clarifications on User-level access

    I've read the recent threads on user-level access and implemented a few things, but I'm stuck on other portions. I think most of it is a lack of understanding and lack of experience with code rather than a coding error. My database needs are surprisingly similar to the Firefighter database here:https://www.accessforums.net/showthr...ecific-records, but again, my understanding isn't at the same level as that user.

    I've got a login form similar to this Datapig tutorial: http://www.datapigtechnologies.com/f...loginform.html
    The "Authorized" table holds:
    • USERID (key)
    • PWD
    • a lookup to the employee's name in the employee data table (not sure if this is necessary, but I thought it might be useful for access levels)
    • a combobox to choose the attributes/access level (Admin, Developer, Manager, User), field name: Attributes

    Some of the examples have had the access levels in a separate table, and each entry in the login/Authorized table is linked to a level in the separate table. What are the benefits of doing it in a separate table as opposed to doing the combobox?

    The two main goals I have for the login authentication is to:
    1. Hide buttons on the landing page and all other forms depending on access level
    2. Filter results of a subform based on access level


    For the first part, my approach was to capture the USERID inputted in the login form, and put it in a hidden textbox on the landing page that the a successful login takes you to (frmOwners). I thought the best way to hide the buttons would be to to a DLOOKUP of the Attributes field in the Authorized table based on the USERID, and set buttons to hidden or visible based on a Select Case of the result. I don't have any of that code working, that was simply the theory. I ran into my first problem when I couldn't get a MsgBox to display showing the result of the DLOOKUP when I used code like the following:


    Code:
    Private Sub Form_Load()
    
    Dim myword As String
    myword = DLookup("Attributes", "Authorized", "USERID = '" & Me.txtUserID.Value & "'")
    MsgBox (myword)
    
    
    End Sub
    I don't fully understand the Form_Load and Form_Open, but I'm not sure where to look for an appropriate explanation.

    For the second part, the frmOwners has a combobox that links to employee names in the tblOwners. The tblResourceAllocation has a field that looks up the names in the tblOwners for a one to many relationship. Therefore, I have a subform on frmOwners that displays all the tblResourceAllocation records tied to that particular owner when his name is selected in the drop down. What I want to do is restrict the names in the drop down form based on the access level of the user:
    • If Admin or Developer, show all choices
    • If Manager, only show that manager's name in the drop down and all the records tied to him in the subform
    • If User, don't show frmOwners, and take them to a report instead.


    To implement this, the code would need to be on the login form, correct? Would it be a case statement with the Dlookup result of the attributes, and then specify a Where condition in the DoCmd.OpenForm for the Managers but open a report for the Users? This seems like the easier of my two issues, but I'm stuck on the first at the moment.

    I've followed a lot of the links that June7 has provided in the past, but feel free to link them again if I glossed over a solution. Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    MsgBox has two syntax structures. One is a function and the other is a simple Okay response popup. The function syntax must be part of an expression. Example:

    If MsgBox("Do you want to continue?", vbYesNo) = vbNo Then
    Exit Sub
    Else
    'do this
    End If

    Okay response example:
    If x < 0 Then MsgBox "Value is less than 0. Invalid entry. Try again."

    Code in the login form would determine whether to open the form or report.
    If UserLevelValue = "User" Then
    'open report
    Else
    'open form
    End If

    Code in the frmOwners Load or OnCurrent event would set the RowSource of the combobox. This could be If Then structure.
    If UserLevelValue = "Manager" Then
    Me.comboboxname.RowSource = "SELECT ..."
    Me.comboboxname.Requery
    End If

    The question is where will the UserLevelValue come from? It can be reference to textbox on Login form, or value passed in OpenArgs argument, or a DLookup.
    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
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40
    The UserLevelValue is stored in txtUserID, which is a text box that is filled with the UserID that was inputted in the Login form. It does this currently with the following code that will need to be modified for your suggestions above:
    Code:
    If Me.qpwd.Value = DLookup("PWD", "Authorized", "USERID = '" & Me.quserid.Value & "'") Then    DoCmd.OpenForm "Owners", acNormal
        stUserID = Forms.LogInForm.quserid.Value
        Forms.Owners.txtUserID.Value = stUserID
        DoCmd.Close acForm, "LogInForm"
    Thanks for the info on the MsgBox and the login form code, I'm going to fix that up right now. Could you provide further explanation on using the load or OnCurrent events? If I'm populating the UserLevelValue in the way mentioned above, is one event better than the other or provides more implementation options?

    Also, what about hiding the buttons? I'm guessing that would take place at the same time as the RowSource change in the Load or OnCurrent event?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That code is one way to go. I presume txtUserID is an unbound textbox.

    Use the OnCurrent event if you allow record navigation as it will fire when moving to new record and if it is necessary to reset status of controls in accordance with some data in record. If the form is opened filtered to a single record OnCurrent is still good but Load should work as well and even Open event is sometimes suitable.

    You can set the buttons as Visible No and then use code to make visible if condition met or vice versa.
    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.

  5. #5
    need_help12 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    58
    I'm looking to do the same thing and keep running into various issues. Did this approach work for you?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Possiby Datech will never visit this thread again. Start a new thread on your issue and we can try to help. I am sure it can be make to work.
    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: 13
    Last Post: 11-18-2013, 02:20 PM
  2. Replies: 7
    Last Post: 02-22-2013, 04:57 PM
  3. User-level access to specific records
    By gbonnaville in forum Security
    Replies: 7
    Last Post: 03-29-2012, 11:14 AM
  4. Replies: 8
    Last Post: 06-30-2010, 10:57 PM
  5. user level security and splitting access db
    By tomClark in forum Security
    Replies: 3
    Last Post: 02-06-2010, 04:28 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