Multi Select List Box on a Search Form

[Vetgeorge]

I've found helpful information about using a multi select list box on a search form from Martin Green (http://www.fontstuff.com/access/acctut11.htm). I have followed the instructions he has as far as code and it works in my database. However, the way his example works is that a query opens up in a separate window. I would like the query to open as a subform on my search form. What do I have to add/change in the code to do this? The query is called qryMultiSelect and I have tried inserting it as a subform and making it requery in the code, but that doesn't do anything, unless I am doing it wrong in having it as part of my on click event (OK button).



Currently the code reads:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
    If Me!lstCategory.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstCategory.ItemsSelected
            strCriteria = strCriteria & "tblCombined.fldCategory = " & Chr(34) _
                          & Me!lstCategory.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "tblCombined.fldCategory Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tblCombined " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

.

[pbaldy]

I would set the subform's record source property to your SQL variable.