Results 1 to 13 of 13
  1. #1
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6

    Filtering Combo Box by User

    Hi, I am new to Access and am trying to filter a combo box by user. I have multiple users entering and viewing employee records in this database and I do not want them to be able to view/enter information about themselves or employees at the same level as themselves, e.g. a supervisor can only view assistant supervisor and below and a manager can view only supervisor and below.



    I have a login in screen and a navigation page that will only show forms that a user has access to, but can't figure our how to filter a combo box in those forms to remove themselves and people as the same rank or higher. For example we have an attendance form and I want users to be able to select only employees below their rank. Everything I have tried hasn't worked.

    I have an employee information table, a user information table, user type table with access level numbers assigned.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What have you tried? Really need to show sample data. If you want to provide db for analysis follow instructions at bottom of my post.

    When user logs in do you save user info somewhere (global variable, TempVar, on a form that never closes) so it can be retrieved again?

    Cascading combobox is a very common topic.
    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.

  4. #4
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6

    Attached DB

    Quote Originally Posted by June7 View Post
    What have you tried? Really need to show sample data. If you want to provide db for analysis follow instructions at bottom of my post.

    When user logs in do you save user info somewhere (global variable, TempVar, on a form that never closes) so it can be retrieved again?

    Cascading combobox is a very common topic.
    I have attached the DB for reference. I mostly used the Steve Bishop video above to create the code I am using. I suspect I need to retain the user info, but I am still learning how to read/write vba and haven't figured it out yet. Educator Database - Copy.zip

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, retain user info somewhere so it can be referenced at any time. I set a textbox on form that never closes (MainMenu). Global variable and TempVar are alternative options. Whichever you prefer, set them during login procedure.
    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
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6
    Hi again, since I am stuck at home I finally have a chance to tackle this again. I feel like I am right on the cusp of understanding, but my knowledge of access/vba is too new. I have attached an updated file where I have the active user id on the main page, and I have created a few queries that I think will help filter the records properly to exclude that users info and any other records of someone the same rank or higher. I just can't seem to figure out how to achieve it. I am starting out on the attendance form.

    Quote Originally Posted by June7 View Post
    Yes, retain user info somewhere so it can be referenced at any time. I set a textbox on form that never closes (MainMenu). Global variable and TempVar are alternative options. Whichever you prefer, set them during login procedure.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Missing a space in front of "AND " - without it the concatenated string just 'runs together' and Access can't make sense of it.
    Code:
    UserAccess = Nz(DLookup("HasAccess", "UserAccess", "AccessLevel=" & TempVars("AccessLevel") & " AND FormName='" & FormName & "'"), False)
    


    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.

  8. #8
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6
    Thanks. I had that right, but my 1 year-old keeps mashing my keyboard. Fixed it. What I am struggling with is how to actually apply this to filter the combobox now. I think I have made the right queries, I just don't know how to combine them to make it work.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What login should I use?

    Why does combobox have a RowSource that is a table/query name that doesn't exist?

    Which table do you want to filter by user?

    If you want to restrict records by user level then likely each record needs to be assigned a level code - number would be best. Then depending on user level, filter records so only those records with level equal to or less (or greater - whichever way you want to work this) than level code will be retrieved.
    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.

  10. #10
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6
    The query rowsource was left over from something else and can be removed. Login in as one of the Assistant Supervisor users and the EducatorInformation table is what needs to be filtered. I went into that table and added a field called access level and assigned all the non leadership folks a level of 6. I can get the Admin to disappear, but not any other level.

    Quote Originally Posted by June7 View Post
    What login should I use?

    Why does combobox have a RowSource that is a table/query name that doesn't exist?

    Which table do you want to filter by user?

    If you want to restrict records by user level then likely each record needs to be assigned a level code - number would be best. Then depending on user level, filter records so only those records with level equal to or less (or greater - whichever way you want to work this) than level code will be retrieved.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not clear what you want to accomplish.

    Seems like all users are leadership types - they have Lead or Supervisor or Admin in title.

    If I login as an Assistant Supervisor with AccessLevel of 4, should I be able to only view records in EducatorInformation that have AccessLevel of 4 or less?
    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
    JesterSD is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    6
    Assistant supervisors are only allowed to see the Instructor, EDIII, II, I and should not be able to see Supervisors or above. Supervisors can see all those and assistant supervisors, but not other supervisors or leaders, etc. So AccessLevel 4 could see level 5 or 6(the level I added to the AccessLevel field I have added to the EducatorInformation table).

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider this code:
    Code:
    
    Private Sub Form_Open(Cancel As Integer)
    Dim strLevels As String
    Select Case TempVars("AccessLevel")
        Case 1
            strLevels = "something"
        Case 2
            strLevels = "something"
        Case 3
            strLevels = "something"
        Case 4
            strLevels  = "5,6"
        Case 5
            strLevels = "something"
        Case 6
            strLevels = "something"
    End Select
    Me.cbSAPID.RowSource = "SELECT * FROM EducatorInformation WHERE AccessLevel IN(" & SQL & ");"
    End Sub
    
    Instead of Select Case and hard-coded level sets, can have the level sets saved in UserType table text field.

    If Title and UserType are synonymous, there is no need to have both Title and AccessLevel fields in Users table. If you have one, the other can be looked up or retrieved in queries that join tables. However, there are some titles not in UserType table.
    Your code insists I change password when it is "password" but doesn't seem to save new password to user record.
    Last edited by June7; 03-29-2020 at 09:38 AM.
    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: 2
    Last Post: 06-17-2018, 12:07 AM
  2. Replies: 3
    Last Post: 07-04-2017, 07:22 AM
  3. Replies: 1
    Last Post: 07-12-2012, 08:39 AM
  4. Replies: 5
    Last Post: 03-12-2012, 02:58 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 PM

Tags for this Thread

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