Hi All,
I'm after some advice as a newbie as I cant figure out how to achieve the below.
I have a DB which using a listbox on a form to lookup entitlements from a table and filter a report based on the listbox selection. The report returns the same user ID which have the entitlements selected on the listbox from the table on a report.
Everything works as expected until I change the list box to multi select which I believe is creating null values and the report is blank.
Can someone help with the code required to select all records when multi select is turned on as there are lots of entitlements so I cant leave it off to select one by one.
Code below
Thanks,
Lee
I'm after some advice as a newbie as I cant figure out how to achieve the below.
I have a DB which using a listbox on a form to lookup entitlements from a table and filter a report based on the listbox selection. The report returns the same user ID which have the entitlements selected on the listbox from the table on a report.
Everything works as expected until I change the list box to multi select which I believe is creating null values and the report is blank.
Can someone help with the code required to select all records when multi select is turned on as there are lots of entitlements so I cant leave it off to select one by one.
Code below
Code:
Dim varItem As Variant
Dim strWhere As String
'make sure a selection has been made
If Me.Entitlements.ItemsSelected.Count = 0 Then
MsgBox "You must choose at least 1 entitlement"
Exit Sub
End If
'add selected values to string
Set ctl = Entitlements
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "ToxicAnalysisReport", acPreview, , "ID IN(" & strWhere & ") and [UserID] In (SELECT [UserID] FROM [ApplicationACL1] As Tmp GROUP BY [UserID] HAVING Count(*)>1 )"
Thanks,
Lee