Multiple multiselect listboxes as filters for ListBox on a form

[MrMode]

I have 3 list boxes on a form.

Listbox1 is multiselect simple
Listbox2 is multiselect simple
Listbox3 is a listbox

I have code that works and allows me to populate listbox3 with multiple selections made in listbox1:

    Dim strSQLRowSource As String
    Dim strWhere As String
    Dim varItem

    strSQLRowSource = _
          "SELECT ... " & _
            "FROM ... " & _
           " WHERE ~ "
    strWhere = " table1.ID IN ("
    For Each varItem In List1.ItemsSelected
        strWhere = strWhere & Me.List1.ItemData(varItem) & ","
    Next
    strWhere = Left(strWhere, Len(strWhere) - 1) & ") "
    Me.List3.RowSource = Replace(strSQLRowSource, "~", strWhere)

What I can't work out is how to use criteria from two multiselect list boxes to filter the results in listbox3.

This is what I have been trying, but it throws "Invalid procedure call or argument":

    Dim strSQLRowSource As String
    Dim strSql As String
    Dim strWhere As String
    Dim strIN As String
    Dim strIN2 As String
    Dim varItem1 As Variant
    Dim varItem2 As Variant
      
   strSQLRowSource = _
        "SELECT ... " & _
        "FROM ... "
    
    For Each varItem1 In List2.ItemsSelected
        strIN = strIN & Me.List2.ItemData(varItem1) & ","
    Next
    
    For Each varItem2 In List33.ItemsSelected
        strIN2 = strIN2 & Me.List33.ItemData(varItem2) & ","
    Next
    
    strWhere = " WHERE tbl1.ID in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")" & _
               " AND tbl2.ID in " & _
               "(" & Left(strIN2, Len(strIN2) - 1) & ")" 

    strSQLRowSource = strSQLRowSource & strWhere

    Me.List12.RowSource = strSQLRowSource

 

[dhookom]

Are the ID fields both numeric?
Have you attempted to use:

Debug.Print strSQLRowSource

Add this line prior to the last line. You should then be able to paste the result into the SQL view of a blank query to quickly identify your issue.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Bizzare

It was throwing the exception, I checked that Listbox2 was multiselect simple, it wasn't. I changed it to multiselect simple and it works!

Thanks for forcing me to check the simple stuff first!