How do I filter an Access subform with multiple combo boxes in the form?

Question

I have multiple combo boxes in my form (acct_nbr, type, team_aud). I'm looking for a way to filter the subform (as a datasheet) based on the selection of each combo box. If a combo box is not used in the filter, the subform data just filters on the other two combo boxes.

Here is what I have so far:

Private Sub cboAccountFilter_AfterUpdate()
    Call FilterSubform
End Sub

Private Sub cboTypeFilter_AfterUpdate()
    Call FilterSubform
End Sub

Private Sub txtTeamAuditorFilter_AfterUpdate()
    Call FilterSubform
End Sub

Private Sub FilterSubform()
    Dim strWhere As String

    If Nz(Me.cboAccountFilter, "") <> "" Then
        strWhere = strWhere & "[acct_nbr] = '" & Me.cboAccountFilter & " ' AND "
    End If

    If Nz(Me.cboTypeFilter, "") <> "" Then
        strWhere = strWhere & "[Type] = '" & Me.cboTypeFilter & " ' AND "
    End If

    If Nz(Me.txtTeamAuditorFilter, "") <> "" Then
        strWhere = strWhere & "[team_aud] = '" & Me.txtTeamAuditorFilter & " ' AND "
    End If

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

I do not receive an error when I click on one of the combo boxes, but all the data is filtered out.

What happens when you eliminate the space before the single quote in each of these? --> " ' AND " (ie change those to "' AND ") — HansUp
You would also be wise to inspect the strWhere string as it exists when you assign it to Filter. Add Debug.Print strWhere, run the code, and view its output in the Immediate window. (Ctrl+g will take you there.) — HansUp

Johnny Bones Johnny Bones · Accepted Answer · 2016-02-19T18:58:36

Change your filters to this:

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

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

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

Edit:

As per your comment, I think it would be:

strWhere = strWhere & "[team_aud] LIKE *'" & Trim(Me.txtTeamAuditorFilter) & "'* AND "

(I tried to leave that as a comment, but the asterisks were interpreted as italics due to SO's markup language).

How do I filter an Access subform with multiple combo boxes in the form?

1 Answers

Edit: