拆分表格中的快速过滤器 [英] Quick Filters in a Split Form Datasheet

问题描述

创建工作访问数据库.用户将使用仅显示数据表的拆分表格来查看和处理数字数据.在表单上，​​我建立了由文本框组成的快速筛选器，其中的文本框使用带有单击事件的箭头按钮来提高或降低值.我目前有链接到记录源查询条件的文本框.

Creating an access database for work. Users will use a split form with only the datasheet visible to review and manipulate numeric data. On the form I have built in quick filters that consist of of textboxes in which the values are either raised or lowered with arrow buttons that have on-click events. I currently have the text boxes linked to the recordsource query criteria.

以上所述，我遇到的问题是我需要过滤器以下列方式运行:

With all of this stated, the problem that I am having is that I need the filter to act in the following manner:

如果文本框中的值等于0，我想查看所有记录.如果该值大于0，则我希望显示所有大于或等于文本框值的记录.最后，如果文本框中的值小于0，我想查看所有小于或等于0的值.

If the value in the text box equals 0 I want to see all records. If the value is greater than 0, I want all records greater than or equal to the text box value to show. Finally, if the value in the text box is less than 0, I want to see all values less than or equal to 0.

我已经考虑过尝试使用多个sql语句，但通常每个表单上都有大约3个这些快速过滤器，而我的项目最终将有大约20个表单.那是很多可能会搞乱的sql语句.

I have considered trying to use multiple sql statements but I typically have about 3 of these quick filters on each form, and my project will eventually have about 20 forms. That is a lot of sql statements to potentially mess up.

你们有什么主意来解决这个问题?我真的需要帮助.

What ideas do you guys have to solve this problem? I really need help.

推荐答案

您可以调用一个辅助函数，该函数包含一组数组并动态构建和设置过滤器:

You could call a helper function which holds a set of arrays and builds and sets the filter dynamically:

Private Sub Filter0_AfterUpdate()

    SetFilter

End Sub


Private Sub Filter1_AfterUpdate()

    SetFilter

End Sub


Private Sub Filter2_AfterUpdate()

    SetFilter

End Sub


Private Sub SetFilter()

    Dim FieldNames()    As Variant
    Dim TextboxNames()  As Variant
    Dim Criteria()      As String

    Dim Index           As Integer
    Dim Value           As Long

    ' Specify the field names to filter on.
    FieldNames = Array("Quantity", "Stock", "Size")
    ' Specify the names of the textboxes to enter filter values.
    TextboxNames() = Array("Filter0", "Filter1", "Filter2")

    ReDim Criteria(LBound(TextboxNames) To UBound(TextboxNames))

    For Index = LBound(Criteria) To UBound(Criteria)
        Value = Val(Nz(Me(TextboxNames(Index)).Value))
        If Value < 0 Then
            Criteria(Index) = FieldNames(Index) & " <= 0"
        ElseIf Value > 0 Then
            Criteria(Index) = FieldNames(Index) & " >= " & CStr(Value)
        Else
            Criteria(Index) = "True"
        End If
    Next

    ' Assemble and apply the filter.
    Me.Filter = Join(Criteria, " And ")
    Me.FilterOn = True

    Debug.Print Me.Filter

End Sub

这篇关于拆分表格中的快速过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！