如何过滤表单中有多个组合框的Access子表单? [英] How do I filter an Access subform with multiple combo boxes in the form?

问题描述

我的表单中有多个组合框(acct_nbr，类型，team_aud).我正在寻找一种根据每个组合框的选择来过滤子窗体(作为数据表)的方法.如果筛选器中未使用组合框，则子窗体数据仅在其他两个组合框上进行筛选.

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.

推荐答案

将过滤器更改为此:

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

根据您的评论，我认为应该是:

As per your comment, I think it would be:

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

(我试图将其保留为注释，但由于SO的标记语言，星号被解释为斜体).

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

这篇关于如何过滤表单中有多个组合框的Access子表单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！