Listbox to filter for query

[tmcrouse]

Ok, so now I have coded what I think should work for my listboxes and it does not. When I click it does nothing. No processing of anything.

I have 11 multi-listboxes and want to collect what the user selects from each one and then use it to run my query. The query only uses 4 of the listbox items and then 4 other items that I have in the query. I want to be able to display this in the same form or perhaps a subform. Here is my code:

Private Sub command8_click()
    ' Update the record source
    If BuildFilter = "" Then
    Me.frmQual_Sub.Form.RecordSource = "select * from qualq1 where " & BuildFilter
    End If
    
    'Requery the subform
    Me.frmQual_Sub.Requery
    End Sub
    
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
     
    varWhere = Null  ' Main filter
        
If Me.List1 > "" Then
        varWhere = varWhere & "[lob] LIKE """ & Me.List1 & "*"" AND "
    End If
        
If Me.List2 > "" Then
        varWhere = varWhere & "[yr] LIKE """ & Me.List2 & "*"" AND "
    End If
    
If Me.List3 > "" Then
        varWhere = varWhere & "[mth] LIKE """ & Me.List3 & "*"" AND "
    End If
        
If Me.List4 > "" Then
        varWhere = varWhere & "[st_cd] LIKE """ & Me.List4 & "*"" AND "
    End If
        
If Me.List5 > "" Then
        varWhere = varWhere & "[bus_unit] LIKE """ & Me.List5 & "*"" AND "
    End If
        
If Me.List6 > "" Then
        varWhere = varWhere & "[prod_nm] LIKE """ & Me.List6 & "*"" AND "
    End If
       
If Me.list7 > "" Then
        varWhere = varWhere & "[category_condition] LIKE """ & Me.list7 & "*"" AND "
    End If
        
If Me.list8 > "" Then
        varWhere = varWhere & "[measure] LIKE """ & Me.list8 & "*"" AND "
    End If
    
If Me.list9 > "" Then
        varWhere = varWhere & "[sub_measure] LIKE """ & Me.list9 & "*"" AND "
    End If
       
If Me.List10 > "" Then
        varWhere = varWhere & "[comm_lvl] LIKE """ & Me.List10 & "*"" AND "
    End If
        
If Me.List11 > "" Then
        varWhere = varWhere & "[comm_type] LIKE """ & Me.List11 & "*"" AND "
    End If
       
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = "''"
    Else
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
     BuildFilter = varWhere
     End Function

 

[pbaldy]

This method can help debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

 

[tmcrouse]

Incase anyone needs a solution for multiple multi-listboxes on a form they are using to filter a query that is not attached to the multi-listboxes but only the form and they want end-users to have the ability to filter and see the results, MajP at Tek Tips helped me solve the problem I posted on here. Here is the information to help people out.

1) Use continuous form.
2) You multi-listboxes need to be in the header selection so after selection your results are in the details.
2) Have a command buton to see results and a command button for clearing what an end-user selected.
3) Use functions to build what you need for the filters.
4) Put your labels you use to search your query in the header section as well and then the textboxes to display results in the details section. This way you labels will not continuously appear. Only the data will.

Private Sub cmdReset_Click()
  Dim ctrl As Access.Control
  Dim itm As Variant
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acListBox Then
      If ctrl.MultiSelect = 0 Then
        ctrl = Null
      Else
        For Each itm In ctrl.ItemsSelected
            ctrl.Selected(itm) = False
        Next
      End If
    End If
  Next ctrl
  Me.Filter = ""
  Me.FilterOn = False
End Sub
Private Sub cmdResults_Click()
   Dim FormFilter As String
   FormFilter = GetFilterFromListBoxes
   Debug.Print FormFilter
   Me.FilterOn = False
   Me.Filter = FormFilter
   Me.FilterOn = True
End Sub
Public Function GetFilterFromListBoxes() As String
  Dim lst As Access.ListBox
  Dim ctrl As Access.Control
  Dim fieldName As String
  Dim fieldType As String
  Dim TotalFilter As String
  Dim ListFilter As String
  Dim itm As Variant
  'Each listbox needs a tag property with the  field name and the field type
  'Seperate these with a ;
  'The types are Text, Numeric, or Date
  For Each ctrl In Me.Controls
     If ctrl.ControlType = acListBox Then
       fieldName = Split(ctrl.tag, ";")(0)
       fieldType = Split(ctrl.tag, ";")(1)
       For Each itm In ctrl.ItemsSelected
       If ListFilter = "" Then
         ListFilter = GetProperType(ctrl.ItemData(itm), fieldType)
       Else
         ListFilter = ListFilter & "," & GetProperType(ctrl.ItemData(itm), fieldType)
       End If
       Next itm
       If Not ListFilter = "" Then
          ListFilter = fieldName & " IN (" & ListFilter & ")"
       End If
       If TotalFilter = "" And ListFilter <> "" Then
         TotalFilter = ListFilter
       ElseIf TotalFilter <> "" And ListFilter <> "" Then
         TotalFilter = TotalFilter & " AND " & ListFilter
       End If
       ListFilter = ""
     End If
  Next ctrl
  GetFilterFromListBoxes = TotalFilter
End Function
Public Function GetProperType(varItem As Variant, fieldType As String) As Variant
  If fieldType = "Text" Then
    GetProperType = sqlTxt(varItem)
  ElseIf fieldType = "Date" Then
    GetProperType = SQLDate(varItem)
  Else
    GetProperType = varItem
  End If
End Function
Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

My form is linked to my query.
My multi-listboxes are linked to the tables within my database that house all the information. Such as State would house all the states so end-users would be able to see all the states. This does not mean the end-user will see all the states in the results. They will only see what is in my query. My query is querying fields from my main table I want to show on my results based on what they filter for. Hope this makes sense.

I hope this helps those out there that have requests for multipe multi-listboxes.............Was a 2wk challenge for me trying to figure this out and I thank MajP so assisting in the solution.