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.