在访问表单上动态编码过滤器 [英] Dynamically Coding Filters on Access Form

问题描述

我有一个连续的表格，可以在标题中使用3个组合框来过滤数据.目前，我有一个相当长的嵌套IF来检查正在使用多少组合框并覆盖所有8个渗透.有3个盒子，这还不错，但我可能会加个第4甚至第5个，在这种情况下，它会变得可笑.

I have a continuous form in access with 3 combo boxes in the header to filter the data. Currently I have a fairly lengthy nested IF to to check how many combo boxes are being used and cover all 8 permeatations. With 3 boxes it's not too bad but I may add a 4th or even 5th in which case it is going to get ridiculous.

是否有更简单的方法来设置连续表格的组合框过滤器?

Is there an easier way to set up combo box filters for continuous forms?

当前代码

Private Sub filters()
    Dim fstr As String, rgS As String, piS As String, hcS As String
    rgS = "research_group_id = " & Me.fRG
    piS = "pi_id = " & Me.fPI
    hcS = "healthcat_id = " & Me.fHC
    If IsNull(Me.fRG) Then
        If IsNull(Me.fPI) Then
            If IsNull(Me.fHC) Then
                ' Do Nothing
            Else
                fstr = hcS
                Call filton(Me.Name, fstr)
            End If
        Else
            If IsNull(Me.fHC) Then
                fstr = piS
                Call filton(Me.Name, fstr)
            Else
                fstr = piS & " AND " & hcS
                Call filton(Me.Name, fstr)
            End If
        End If
    Else
        If IsNull(Me.fPI) Then
            If IsNull(Me.fHC) Then
                fstr = rgS
                Call filton(Me.Name, fstr)
            Else
                fstr = rgS & " AND " & hcS
                Call filton(Me.Name, fstr)
            End If
        Else
            If IsNull(Me.fHC) Then
                fstr = rgS & " AND " & piS
                Call filton(Me.Name, fstr)
            Else
                fstr = rgS & " AND " & piS & " AND " & hcS
                Call filton(Me.Name, fstr)
            End If
        End If
    End If
End Sub

更新每个组合框后，将触发上述代码.

The above code is fired after update of each combo box.

Public Function filton(frmname As String, fstr As String)
        With Forms(frmname)
            .FilterOn = False
            .Filter = fstr
            .FilterOn = True
        End With
End Function

推荐答案

将每个条件以及"AND" 添加到您的过滤器字符串中.然后，从该字符串中丢弃前导"AND" .这种方法应该可以轻松满足任何数量的条件.

Add each criterion, with " AND ", to your filter string. Afterward, discard the leading " AND " from that string. This approach should easily accommodate any number of criteria.

不确定我是否在下面的代码中正确匹配了所有内容，但希望模式是清楚的.

Not sure if I matched everything up properly in the code below, but hopefully the pattern is clear.

If Not IsNull(Me.fRG) Then
    fstr = fstr & " AND " & rgS
End If
If Not IsNull(Me.fPI) Then
    fstr = fstr & " AND " & piS
End If
If Not IsNull(Me.fHC) Then
    fstr = fstr & " AND " & hcS
End If
If Len(fstr) > 0 Then
    ' discard leading " AND "
    fstr = Mid(fstr, 6)
End If

这篇关于在访问表单上动态编码过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！