Passing a list to use as Access 2016 Query criteria

16 views Asked by At

I have a list of users with different access levels (ie. 1,2,3) in one table called Managers. I want to get a list of users with an access level of, say, 2 (John, Sally,Bill) and make them the filter criteria of a form to limit their access to certain records. It sounds simple but I tried to use a listbox's itemSelected property to get the user list, but the listbox requires selecting each item when I really want all selected. I think that I may, instead, need to put the results of the access level query into a textbox and pass the textbox as criteria to the main form's query, but then I need to pad the results with "" and Ors between each user in the criteria area. There must be an easier way.

I tried this: Public Sub Form_Open(Cancel As Integer)

Dim varItem As Variant
Dim strSelected As String
Dim ctrl As Control

Set ctrl = Me.List5

If ctrl.ItemsSelected.Count > 0 Then
    For Each varItem In ctrl.ItemsSelected
        strSelected = strSelected & "," & ctrl.ItemData(varItem)
        
    Next varItem
    
    ' remove leading comma
    strSelected = Mid(strSelected, 2)
    ' assign value list to hidden text box control
    Me.TxtRMAccess = strSelected
    Debug.Print strSelected
    'DoCmd.OpenReport "frmMain"
   
End If

Me,TxtRMAccess is used as criteria in the frmMain query, but does not work correctly. I can only get one record selected which is not the desired result. I need all users with level 2 by name.

0

There are 0 answers