Multi-Select List Boxes to Filter Subform

[acarterczyz]

Hey all!

I'm having some issues getting this to work. I have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible, so if there is an easier way with a query, please let me know.

Here is the code I tried for filtering my subform (which is throwing an error when I call it).

Private Function MasterSearch()


On Error GoTo Error_MasterSearch
   
   Dim StrgSQL As String
   Dim WhereClause As String
   
   StrgSQL = "SELECT * FROM MasterTbl"
   
   If Len(Me.VP_ListBox.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[VP]='" & Me.VP_ListBox.Value & "'"
   End If

If WhereClause <> "" Then StrgSQL = StrgSQL & WhereClause

StrgSQL = StrgSQL & ";"

Forms("MainForm")("MasterTblSubF").Form.RecordSource = StrgSQL

Exit_MasterSearch:
  Exit Function
   
Error_MasterSearch:
  MsgBox "MasterSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Table Search Error"
   Resume Exit_MasterSearch

End Function

The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch". :banghead:

Any help with this would be MUCH appreciated!

 

[acarterczyz]

Actually - this works perfectly!

http://www.fontstuff.com/access/acctut11.htm