Combining multiple combo boxes with a textbox for filtering

[f15e]

I'm guessing this question has come up in some form or another but I couldn't find anthing that matched my case so I am hoping you guys can help me. Below is the scenario:
1) I have a datasheet subform that contains 20 columns. Each row (record) contains a document title with the rest of the columns providing additional info about that document.
2) I am using 12 combo boxes plus one (1) general search text box
3) The 12 combo boxes are directly related to 12 of the 20 columns. For example, I have a column named Document Extension that contains the extension of the document record (i.e. .pdf, .xls, .doc, etc..) and the combo box filters depending on the selection.
4) The text box is just a general keyword search where the user enters a term or phrase and clicks a button to filter to the users criteria.

So, now for the issue I'm having. I can filter using the combo boxes where I can user one or all of them to filter to my need with no problems. I can also enter a search keyword(s) and click the search button and it works as expected. This problem I have is when is use them together. For example, IF use 2 of the combo boxes and the keyword search textbox together to filter the datasheet, it doesn't work. I set the combo boxes where it filters correctly and then after I enter a search term in the textbox and click the search button, the filter ignores the combo box criteria and only filters to the textbox. Below is the code I'm using. If anyone could help with this I'd greatly appreciate it.

'************************************************* *************************
' For ComboBox DocExt_Cmbo
Private Sub DocExt_Cmbo_AfterUpdate()

Combine_CBO_Qrys ' call to the Sub that performs the filtering operation there are
' 11 more of these but only showing 1 to give the general idea

End Sub


' Filtering Sub
Private Sub Combine_CBO_Qrys()

On Error GoTo ErrorHandler



Dim strText As String
strText = Me.txtbx_Search.Value

Dim myFilt() As String
Dim i As Integer
i = -1

ComboBox_Sel = "SELECT Documents_DB_Tbl.Item, Documents_DB_Tbl.[PEMEX Filename], " _
& "Documents_DB_Tbl.[Document Category], Documents_DB_Tbl.Subcategory, " _
& "Documents_DB_Tbl.[Titles or Keywords], Documents_DB_Tbl.[Revision Number], " _
& "Documents_DB_Tbl.[Document Extension], Documents_DB_Tbl.[Well Number], " _
& "Documents_DB_Tbl.[Field Name], Documents_DB_Tbl.[Pipeline Service], " _
& "Documents_DB_Tbl.[PEMEX Pipe Route Description], Documents_DB_Tbl.[Nominal Pipe Diameter (in)], " _
& "Documents_DB_Tbl.[Other Diameter (in)], Documents_DB_Tbl.[Pipe Length (km)], " _
& "Documents_DB_Tbl.[SAP Developed Location], Documents_DB_Tbl.[Integrity / Inspection Locations], " _
& "Documents_DB_Tbl.[From Location], Documents_DB_Tbl.[To Location], " _
& "Documents_DB_Tbl.[Document Date], Documents_DB_Tbl.[Document Year] " _
& "FROM Documents_DB_Tbl WHERE "



If Me.DocCat_Cmbo <> "" And Not IsNull(Me.DocCat_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_DocCat_ID]=" & Me.DocCat_Cmbo & " "

If (SubCat_Cmbo <> "" And Not IsNull(SubCat_Cmbo)) Or (RevNum_Cmbo <> "" And Not IsNull(RevNum_Cmbo)) _
Or (DocExt_Cmbo <> "" And Not IsNull(DocExt_Cmbo)) Or (FieldName_Cmbo <> "" And Not IsNull(FieldName_Cmbo)) _
Or (PipeService_Cmbo <> "" And Not IsNull(PipeService_Cmbo)) Or (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) _
Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) _
Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) _
Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.SubCat_Cmbo <> "" And Not IsNull(Me.SubCat_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_SubCat_ID]=" & Me.SubCat_Cmbo & " "

If (RevNum_Cmbo <> "" And Not IsNull(RevNum_Cmbo)) Or (DocExt_Cmbo <> "" And Not IsNull(DocExt_Cmbo)) _
Or (FieldName_Cmbo <> "" And Not IsNull(FieldName_Cmbo)) Or (PipeService_Cmbo <> "" And Not IsNull(PipeService_Cmbo)) _
Or (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) _
Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) _
Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) _
Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.RevNum_Cmbo <> "" And Not IsNull(Me.RevNum_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_RevNum_ID]=" & Me.RevNum_Cmbo & " "

If (DocExt_Cmbo <> "" And Not IsNull(DocExt_Cmbo)) Or (FieldName_Cmbo <> "" And Not IsNull(FieldName_Cmbo)) _
Or (PipeService_Cmbo <> "" And Not IsNull(PipeService_Cmbo)) Or (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) _
Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) _
Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) _
Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.DocExt_Cmbo <> "" And Not IsNull(Me.DocExt_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_DocExt_ID]=" & Me.DocExt_Cmbo & " "

If (FieldName_Cmbo <> "" And Not IsNull(FieldName_Cmbo)) Or (PipeService_Cmbo <> "" And Not IsNull(PipeService_Cmbo)) _
Or (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) _
Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) _
Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) _
Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.FieldName_Cmbo <> "" Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_FieldName_ID]=" & Me.FieldName_Cmbo & " "

If (PipeService_Cmbo <> "" And Not IsNull(PipeService_Cmbo)) Or (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) _
Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) _
Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) _
Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.PipeService_Cmbo <> "" And Not IsNull(Me.PipeService_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_PipeService_ID]=" & Me.PipeService_Cmbo & " "

If (NomDiam_Cmbo <> "" And Not IsNull(NomDiam_Cmbo)) Or (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) _
Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) _
Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) _
Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.NomDiam_Cmbo <> "" And Not IsNull(Me.NomDiam_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_NomDiam_ID]=" & Me.NomDiam_Cmbo & " "

If (Year_Cmbo <> "" And Not IsNull(Year_Cmbo)) Or (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) _
Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) _
Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.Year_Cmbo <> "" And Not IsNull(Me.Year_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_Year_ID]=" & Me.Year_Cmbo & " "

If (SAP_DevLoc_Cmbo <> "" And Not IsNull(SAP_DevLoc_Cmbo)) Or (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) _
Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) _
Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.SAP_DevLoc_Cmbo <> "" And Not IsNull(Me.SAP_DevLoc_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_SAP_DevLoc_ID]=" & Me.SAP_DevLoc_Cmbo & " "

If (FromLoc_Cmbo <> "" And Not IsNull(FromLoc_Cmbo)) Or (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) _
Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.FromLoc_Cmbo <> "" And Not IsNull(Me.FromLoc_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_FromLoc_ID]=" & Me.FromLoc_Cmbo & " "

If (ToLoc_Cmbo <> "" And Not IsNull(ToLoc_Cmbo)) Or (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) _
Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.ToLoc_Cmbo <> "" And Not IsNull(Me.ToLoc_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_ToLoc_ID]=" & Me.ToLoc_Cmbo & " "

If (WellNum_Cmbo <> "" And Not IsNull(WellNum_Cmbo)) Or (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If
End If


If Me.WellNum_Cmbo <> "" And Not IsNull(Me.WellNum_Cmbo) Then

ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_WellNum_ID]=" & Me.WellNum_Cmbo & " "

If (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then

ComboBox_Sel = ComboBox_Sel & " AND "

End If

End If


If Me.txtbx_Search <> "" Then And Not IsNull(Me.txtbx_Search)

Dim txtbox_srch As String
txtbox_srch = "([Item] Like ""*" & strText & "*"") OR ([PEMEX Filename] Like ""*" & strText & "*"") OR ([Document Category] Like ""*" & strText & "*"") OR " _
& "([Subcategory] Like ""*" & strText & "*"") OR ([Titles or Keywords] Like ""*" & strText & "*"") OR " _
& "([Revision Number] Like ""*" & strText & "*"") OR ([Document Extension] Like ""*" & strText & "*"") OR " _
& "([Well Number] Like ""*" & strText & "*"") OR ([Field Name] Like ""*" & strText & "*"") OR " _
& "([Pipeline Service] Like ""*" & strText & "*"") OR ([PEMEX Pipe Route Description] Like ""*" & strText & "*"") OR " _
& "([Nominal Pipe Diameter (in)] Like ""*" & strText & "*"") OR ([Other Diameter (in)] Like ""*" & strText & "*"") OR " _
& "([Pipe Length (km)] Like ""*" & strText & "*"") OR ([SAP Developed Location] Like ""*" & strText & "*"") OR " _
& "([Integrity / Inspection Locations] Like ""*" & strText & "*"") OR ([From Location] Like ""*" & strText & "*"") OR " _
& "([To Location] Like ""*" & strText & "*"") OR ([Document Date] Like ""*" & strText & "*"") OR " _
& "([Document Year] Like ""*" & strText & "*"")"

ComboBox_Sel = ComboBox_Sel & txtbox_srch

End If

Me.DB_Docs_Search_SubForm.Form.RecordSource = ComboBox_Sel
Me.DB_Docs_Search_SubForm.Form.Requery

ExitHandler:
Exit Sub

ErrorHandler:
MsgBox "An error occurred:" & vbCrLf & _
"Error " & Err.Number & ": " & Err.Description

End Sub

[John_G]

Can you clarify the logic a bit?

You are "AND"ing all the combo boxes together, I can see that, so that your selected records need to match all combo's where you specified a value.

But if a text value is specified too, what is supposed to happen? Does it mean that selected records must match on all the combos with ADDITIONAL filtering on the text, or does it mean "Match on all combos OR any one of the text values" ? There is a big difference.

It might be as simple as some additional brackets.

[f15e]

Yes, combo boxes filter the records in addition to a textbox keyword search but the user has the option to use only the combo boxes, only the keyword search textbox, or use the combo boxes and textbox together to filter the records to the user's liking. So let's say there are 1000 records and the user selects an item in combo box 1 where the records are filtered down to 800. Additionally, the user selects an item in combo box 2 where the records are then filtered to 400. The user decides to further filter using a keyword in the textbox, again in addition to Combo boxes 1 and 2, and clicks search where the records are filtered down to 100. The other 10 combo boxes could be used to filter further, but I think you see my point.

Thanks John

[John_G]

OK. I'm going to assume then that any text search should apply to only those records the combo boxes identify. You are missing some brackets, because all the textbox "OR"'s should be in one bracketed epression, like this:

combo1 AND combo2 AND combo3 AND combo4 AND ( text1 OR text2 OR text3 OR text4)

So your code might look like this (edited for brevity):

Private Sub Combine_CBO_Qrys()

    On Error GoTo ErrorHandler
    
    Dim strText As String
    strText = Me.txtbx_Search.Value
    
    Dim myFilt() As String
    Dim i As Integer
    i = -1
            
    ComboBox_Sel = "SELECT Documents_DB_Tbl.Item, Documents_DB_Tbl.[PEMEX Filename], " _
            & "Documents_DB_Tbl.[Document Category], Documents_DB_Tbl.Subcategory, " _
            & "Documents_DB_Tbl.[Titles or Keywords], Documents_DB_Tbl.[Revision Number], " _
            & "Documents_DB_Tbl.[Document Extension], Documents_DB_Tbl.[Well Number], " _
            & "Documents_DB_Tbl.[Field Name], Documents_DB_Tbl.[Pipeline Service], " _
            & "Documents_DB_Tbl.[PEMEX Pipe Route Description], Documents_DB_Tbl.[Nominal Pipe Diameter (in)], " _
            & "Documents_DB_Tbl.[Other Diameter (in)], Documents_DB_Tbl.[Pipe Length (km)], " _
            & "Documents_DB_Tbl.[SAP Developed Location], Documents_DB_Tbl.[Integrity / Inspection Locations], " _
            & "Documents_DB_Tbl.[From Location], Documents_DB_Tbl.[To Location], " _
            & "Documents_DB_Tbl.[Document Date], Documents_DB_Tbl.[Document Year] " _
            & "FROM Documents_DB_Tbl WHERE "

    
'
'  Code snipped
'


    If Me.WellNum_Cmbo <> "" And Not IsNull(Me.WellNum_Cmbo) Then
    
        ComboBox_Sel = ComboBox_Sel & "Documents_DB_Tbl.[DB_WellNum_ID]=" & Me.WellNum_Cmbo & " "
        
        If (txtbx_Search <> "" And Not IsNull(txtbx_Search)) Then
        
           ComboBox_Sel = ComboBox_Sel & " AND "
           
        End If
        
    End If
    
        
    If Me.txtbx_Search <> "" Then And Not IsNull(Me.txtbx_Search)
'
'   Add an opening Bracket
'
         ComboBox_Sel = ComboBox_Sel & " ( "
        Dim txtbox_srch As String
        txtbox_srch = "([Item] Like ""*" & strText & "*"") OR  ([PEMEX Filename] Like ""*" & strText & "*"") OR ([Document  Category] Like ""*" & strText & "*"") OR " _
            & "([Subcategory] Like ""*" & strText & "*"") OR  ([Titles or Keywords] Like ""*" & strText & "*"") OR " _
            & "([Revision Number] Like ""*" & strText &  "*"") OR ([Document Extension] Like ""*" & strText & "*"") OR " _
            & "([Well Number] Like ""*" & strText & "*"") OR  ([Field Name] Like ""*" & strText & "*"") OR " _
            & "([Pipeline Service] Like ""*" & strText &  "*"") OR ([PEMEX Pipe Route Description] Like ""*" & strText &  "*"") OR " _
            & "([Nominal Pipe Diameter (in)] Like ""*" & strText  & "*"") OR ([Other Diameter (in)] Like ""*" & strText &  "*"") OR " _
            & "([Pipe Length (km)] Like ""*" & strText &  "*"") OR ([SAP Developed Location] Like ""*" & strText & "*"")  OR " _
            & "([Integrity / Inspection Locations] Like ""*" &  strText & "*"") OR ([From Location] Like ""*" & strText &  "*"") OR " _
            & "([To Location] Like ""*" & strText & "*"") OR  ([Document Date] Like ""*" & strText & "*"") OR " _
            & "([Document Year] Like ""*" & strText & "*"")"
            
        ComboBox_Sel = ComboBox_Sel & txtbox_srch & " )"   ' Added closing bracket here
            
    End If
    
    Me.DB_Docs_Search_SubForm.Form.RecordSource = ComboBox_Sel
    Me.DB_Docs_Search_SubForm.Form.Requery
    
ExitHandler:
   Exit Sub

ErrorHandler:
   MsgBox "An error occurred:" & vbCrLf & _
          "Error " & Err.Number & ": " & Err.Description

End Sub

[f15e]

That was it John! I needed to add the parenthesis to enclose the entire expression. I really appreciate your help!