I have a form which allows the user to make multiple selections for the criteria (Labor Cat) when the report is run. The report is running fine and all the selection criteria is passing to the report and subreports.
I know this is basic but I can't get the users selections to display in the report title. I want the user to have a record of what is contained in the report. Currently it is only picking up one selection but not all of them.
My title looks like this LABEL [Labor Category Report for] txtLaborCat [selections made from the form]
txtLaborCat - Control Source is LaborCat. I have tried pulling the selections using the !Forms! argument but that didn't work.
I know this is pretty basic but I'm stumped.
Code:
Private Sub cmdPerfIssuesLaborCatRpt_Click()
On Error GoTo Err_cmdPerfIssuesLaborCatRpt_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstLaborCatSelection.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstLaborCatSelection
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
Debug.Print strWhere
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptPerfIssuesbyLaborCat", acPreview, , "LaborCat IN (" & strWhere & ")"
Exit_cmdPerfIssuesLaborCatRpt_Click:
Exit Sub
Err_cmdPerfIssuesLaborCatRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPerfIssuesLaborCatRpt_Click
End Sub