Filtering subform based on multiple comboboxes

[jlb4350]

Hello all. I have a form that has a subform and 10 combo boxes that relate to 10 columns in the subform. What I'm trying to do is automatically filter the subform based on the combobox selections, but I'm having trouble. Here is my code:

Option Compare Database
Private Sub txtClear_Click()
Me.sfrmsearch.Form.FilterOn = False
Me.txtSearch.Value = ""
Me.cboChangeType.Value = ""
Me.cboDescription.Value = ""
Me.cboDocSpecType.Value = ""
Me.cboDocSubType.Value = ""
Me.cboDocType.Value = ""
Me.cboDocSource.Value = ""
Me.cboDrugProduct.Value = ""
Me.cboSupplier.Value = ""
Me.cboToll.Value = ""
Me.cboVersion.Value = ""
End Sub
Private Sub cboChangeType_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDescription_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDocSpecType_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDocSubType_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDocType_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDocSource_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboDrugProduct_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboSupplier_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboToll_AfterUpdate()
    Call SearchFilter
End Sub
Private Sub cboVersion_AfterUpdate()
    Call SearchFilter
End Sub

'Private Sub txtFieldName_AfterUpdate()
'USED TO ADD A TEXT FILTER
'    Call sfrmsearch
'End Sub

Private Sub SearchFilter()
    Dim strWhere As String

    If Nz(Me.cboChangeType, "") <> "" Then
        strWhere = strWhere & "[ChangeType] = '" & Trim(Me.cboChangeType) & " ' AND "
    End If

    If Nz(Me.cboDescription, "") <> "" Then
        strWhere = strWhere & "[Description] = '" & Trim(Me.cboDescription) & " ' AND "
    End If

    If Nz(Me.cboDocSpecType, "") <> "" Then
        strWhere = strWhere & "[DocumentationSpecificType] = '" & Trim(Me.cboDocSpecType) & " ' AND "
    End If
    
    If Nz(Me.cboDocSubType, "") <> "" Then
        strWhere = strWhere & "[DocumentationSubType] = '" & Trim(Me.cboDocSubType) & " ' AND "
    End If
    
    If Nz(Me.cboDocType, "") <> "" Then
        strWhere = strWhere & "[DocumentationType] = '" & Trim(Me.cboDocType) & " ' AND "
    End If
    
    If Nz(Me.cboDocSource, "") <> "" Then
        strWhere = strWhere & "[DocumentSource] = '" & Trim(Me.cboDocSource) & " ' AND "
    End If
    
    If Nz(Me.cboDrugProduct, "") <> "" Then
        strWhere = strWhere & "[DrugProduct] = '" & Trim(Me.cboDrugProduct) & " ' AND "
    End If
    
    If Nz(Me.cboSupplier, "") <> "" Then
        strWhere = strWhere & "[Supplier] = '" & Trim(Me.cboSupplier) & " ' AND "
    End If
    
    If Nz(Me.cboToll, "") <> "" Then
        strWhere = strWhere & "[TollFacility] = '" & Trim(Me.cboToll) & " ' AND "
    End If
    
    If Nz(Me.cboVersion, "") <> "" Then
        strWhere = strWhere & "[VersionStatus] = '" & Trim(Me.cboVersion) & " ' AND "
    End If

    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
        Me.sfrmsearch.Form.Filter = strWhere
        Me.sfrmsearch.Form.FilterOn = True
    Else
        Me.sfrmsearch.Form.Filter = ""
        Me.sfrmsearch.Form.FilterOn = False
    End If
End Sub

When I select something in either the cboChangeType or cboDescription pulldowns, the filter works for those two boxes, provided both of those fields have the selected data. However, when I select something in any of the other pulldowns to further filter the subform, simply nothing happens.

Thank you in advance for any ideas. They are most appreciated.

 

[theDBguy]

Hi. I can't see the attachments.

Just a suggestion, try adding the Debug.Print line in your code to see what you're trying to apply. For example:

     If strWhere <> "" Then
         strWhere = Left(strWhere, Len(strWhere) - 5)
         Me.sfrmsearch.Form.Filter = strWhere
         Me.sfrmsearch.Form.FilterOn = True
       [B]Debug.Print strWhere[/B]
     Else

 

[jlb4350]

Hi. I can't see the attachments.

Just a suggestion, try adding the Debug.Print line in your code to see what you're trying to apply. For example:

     If strWhere <> "" Then
         strWhere = Left(strWhere, Len(strWhere) - 5)
         Me.sfrmsearch.Form.Filter = strWhere
         Me.sfrmsearch.Form.FilterOn = True
       [B]Debug.Print strWhere[/B]
     Else

Thank you for the suggestion, but I found the issue just after I posted this. I've been trying fix this for days and it just came to me.

Here is my resolution: Even though I had the code built, I had [Event Procedure] entered into the "After Update" properties box only on the first two boxes. All I did was pull the dropdown box in the "After Update" properties of all the other comboboxes and they all started working.

Thank you again for your suggestion and I hope this post is helpful to someone else!

 

[theDBguy]

Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.

 

[jlb4350]

Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.

Thank you!