CODEWITHSUNDEEP
ms-accessvbams-access-2013
ugadawgs306
ugadawgs306

Reputation: 21

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

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.

Upvotes: 2

Views: 3367

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

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).

Upvotes: 2

Related Questions