Command Button for multi-select listbox form

Question

I would like to generate a report that shows the criteria picked from a combo box and multiple selection listbox by a command button on a form. I already have the listbox set up as simple to allow for multiple choices, but I am having trouble with the coding for the form command button.

Combo Box Name: CboRegion

list box Name: lstCategory

Command Button Name: cmdCreateReport2

query/report name: Multiple Category by Region

form name: Multiple Categories By Region

Region List Table Name: tblRegionList = this is a numerical field > my combo box is built of this

Category List Table Name: tblRegionList = this a text field > my listbox is built of this

[ID] = this is my primary key field, which is numerical

This is what I've tried so far:

Private Sub cmdCreateReport2_Click()

Dim varItem As Variant

Dim strFilter As String

For Each varItem In Me.lstCategory.ItemsSelected

strFilter = strFilter + "'" & Me.lstCategory.ItemData(varItem) & "', "

Next varItem

'remove trailing comma and space

strFilter = Left$(strFilter, Len(strFilter) - 2)

strFilter = "[ID] IN (" & strFilter & ")"

DoCmd.OpenReport "Multiple Category by Region", acViewNormal, , strFilter

End Sub

I'm not sure what I am doing incorrectly. If I did just the combo box i know it would be

Private Sub cmdCreateReport2_Click()

DoCmd.OpenReport "Multiple Category by Region", acViewPreview, , strWhere

DoCmd.Close acForm, "Multiple Categories By Region"

End Sub

Any help would be greatly appreciated.

Thanks!

Answer 1

You also need to include the region combo box in the criteria:

Private Sub cmdCreateReport2_Click()

    Dim varItem As Variant

    Dim strFilter As String

If Me.lstCategory.ItemsSelected.Count > 0 Then

    For Each varItem In Me.lstCategory.ItemsSelected

    strFilter = strFilter & "," & Me.lstCategory.ItemData(varItem)

 Next varItem

 'remove leading comma and space

      strFilter = Mid(strFilter,2)

   strFilter = "[ID] IN (" & strFilter & ")"

End If

If Len(strFilter) > 0 And Not IsNull(Me.cboRegion) Then

strFilter = strFilter & " And "

End If

If Not IsNull(Me.cboRegion) Then

strFilter = strFilter & "Region ID = " & Me.cboRegion

    End If

   DoCmd.OpenReport "Multiple Category by Region", _

View:=acViewNormal, _

WhereCondition:=strFilter

End Sub

I've assumed that the BoundColumn of the cboRegion control is a numeric column RegionID.

Answer 2

If ID is a number field, your list box should have a number field as hidden first column, and you shouldn't place single quotes around the values:

strFilter = strFilter & Me.lstCategory.ItemData(varItem) & ", "