使用组合框来筛选拆分表格 [英] Using Combo Boxes to filter a Split Form

问题描述

我利用拆分表格的访问，我有三（3）下拉组合框中： ReportedLOB（在这个例子中使用） 帐户 StandCategory

I am utilizing a Split Form in Access and I have three (3) dropdown combo boxes: ReportedLOB (used in this example) Account StandCategory

所有这些组合框被绑定到所选的特定业务部门，因此它限制在组合框中的项目数量。我现在用的是下面的code为ReportedLOB：

Each of these combo boxes are tied to the specific Business Unit selected, therefore it limits the amount of items in the Combo box. I am using the following code for the ReportedLOB:

 Private Sub Combo_Reported_LOB_Selection_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo_Reported_LOB_Selection.Text) = "" Then
  Me.Form.Filter = ""
  Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo_Reported_LOB_Selection.ListIndex <> -1 Then
  Me.Form.Filter = "[ReportedLOB] = '" & _
                 Replace(Me.Combo_Reported_LOB_Selection.Text, "'", "''") & "'"
  Me.FilterOn = True
  End If
 End Sub

让我们说，有4个项目在下拉列表：MCD，比较，GRD，DRR 当我选择MCD，它过滤正确的MCD结果。然而，在审查的球队，他们希望删除过滤器MCD再次拥有所有的结果，所以要添加到列表中，我创建一个额外的表中加入了它的所有下拉菜单项。

Let's say there are 4 items in the drop down list: MCD, Comp, GRD, DRR When I select MCD, it filters correctly for MCD results. However, upon review with the team, they want to remove that filter for MCD to have all results again, so I created an extra table an joined it for a ALL dropdown item to be added to the list.

 SELECT DISTINCT dbo_ztblGAAP_Splits_TableA.ReportedLOB FROM
dbo_ztblGAAP_Splits_TableA WHERE (((dbo_ztblGAAP_Splits_TableA.BU)=[Forms]![Frm_Main]!
[Frm_Main_TextBox_Display_BU_Number_HIDDEN])) 
ORDER BY dbo_ztblGAAP_Splits_TableA.ReportedLOB
UNION ALL 
SELECT Top 10, "**ALL**" FROM  
dbo_tTbl_ADMIN_ForFiltering
ORDER BY ReportedLOB;

现在，百万美元的问题......当我选择所有从下拉这一切我的记录转变为所有对给我所有的原来的结果报告LOB。有没有人每天处理的呢？我已到处​​搜寻，试图拼凑这个code一起。

Now, the million dollar question......When I select ALL from the drop-down it changes all my records to All versus giving me all the original results for Reported LOB. Has anyone every dealt with this? I have searched everywhere to try to piece this code together.

推荐答案

现在，你有你的组合工作的行源查询，我会建议你使用组合的后更新事件驱动改变窗体的 .Filter 属性。

Now that you have a working row source query for your combo, I'll suggest you use the combo's After Update event to drive changes to the form's .Filter property.

Dim strFilter As String

With Me.Combo_Reported_LOB_Selection
    If IsNull(.Value) Or .Value = "**ALL**" Then
        ' If the combo box is cleared or ALL selected, clear the form filter.
        Me.Filter = vbNullString
        Me.FilterOn = False
    Else
        ' item other than ALL is selected, filter for an exact match.
        strFilter = "[ReportedLOB] = '" & _
            Replace(.Value, "'", "''") & "'"
        Debug.Print strFilter ' check this in Immediate window in case of trouble
                              ' you can use Ctrl+g to go to the Immediate window
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
End With

请注意，组合不具有焦点，在更新之后，因此它的。文属性不可用。因此，我们使用。价值代替。该。文属性是真正唯一有用的，你正在改变的价值，同时。而组合一直关注你让这些变化的同时，使。文可用，那么。 pretty的很多其他任何时间，使用。价值。

Notice that the combo does not have focus at After Update, so its .Text property is not available. So we use .Value instead. The .Text property is really only useful while you're changing the value. And the combo has focus while you're making those changes, so .Text is available then. Pretty much any other time, use .Value.

如果你真的preFER不断修改 .Filter 每个组合按键，你必须适应上述code为组合的变化事件。

If you really do prefer to continually change the .Filter with each combo keystroke, you will have to adapt the above code for the combo's Change event.

这篇关于使用组合框来筛选拆分表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！