Dear All,

I am creating a multi select choice for users. It is using a Form with three Subforms. The plan is to make users a selection (simple or mutiple) in three lists and then create a Report from their choice.

This is my VBA. It is working on a single list but it is not working when I am starting to make the filters inside the subform...

Private Sub cmdPreview4_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

Dim strClient As String
Dim strSupplier As String
Dim strOwner As String

strDelim = """"
strDoc = "PO"

With Me.lstCategory1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strSupplier = strSupplier & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strSupplier = "[Supplier] IN (" & Left$(strWhere, lngLen) & ")"


lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "update Relance Client" & Left$(strDescrip, lngLen)
End If
End If




With Me.lstCategory3
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then

strClient = strClient & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strClient = "[Supplier] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "update Relance Client" & Left$(strDescrip, lngLen)
End If
End If


With Me.lstCategory2
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then

strOwner = strOwner & strDelim & .ItemData(varItem) & strDelim & ","

strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

ngLen = Len(strWhere) - 1
If lngLen > 0 Then
strOwner = "[Owner] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "update Relance Owner" & Left$(strDescrip, lngLen)
End If
End If


If strSupplier > "" Then strWhere = strWhere & strSupplier & " And "
If strClient > "" Then strWhere = strWhere & strClient & " And "
If strOwner > "" Then strWhere = strWhere & strOwner & " And "
If strWhere > "" Then strWhere = Left(strWhere, Len(strWhere) - 5)





If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

DoCmd.OutputTo acOutputReport, strDoc, acSpreadsheetTypeExcel9, "c:\temp\text.xls"
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview1_Click"
End If
Resume Exit_Handler

End Sub