It looks like the error occurs if only one list box has selections.
You need to check if both list boxes have selections of just one list box has selections.
Currently you have a string to filter the report " ,strWhere & " And " & strThere"
If both variables have data, then the filter string looks like "[TypeCode] IN (1,2) And [MfgCode] IN (1,2)"
If only "strWhere" has data, then the filter string looks like " [TypeCode] IN (1,2) And " , which will throw an error
If only "strThere" has data, then the filter string looks like " And [MfgCode] IN (1,2)" , which will throw an error
So I modified your code......
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim varItm As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strThere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strCriteria As String 'String to use as the criteria to open report
'strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "PriceLetterNew"
'Loop through the ItemsSelected in the list box.
With Me.ProductTypeList
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[TypeCode] IN (" & Left$(strWhere, lngLen) & ")"
End If
' Debug.Print strWhere
With Me.MfgList
For Each varItm In .ItemsSelected
If Not IsNull(varItm) Then
'Build up the filter from the bound column (hidden).
strThere = strThere & strDelim & .ItemData(varItm) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strThere) - 1
If lngLen > 0 Then
strThere = "[MfgCode] IN (" & Left$(strThere, lngLen) & ")"
End If
' Debug.Print strThere
'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
' Debug.Print strWhere & " And " & strThere
'check to see if both strWhere and strThere have values
If Len(strWhere) > 0 And Len(strThere) > 0 Then
strCriteria = strWhere & " And " & strThere
ElseIf Len(strWhere) > 0 Then
'only strWhere has values
strCriteria = strWhere
ElseIf Len(strThere) > 0 Then
'only strThere has values
strCriteria = strThere
End If
'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, , strCriteria
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub