在具有断开的ADO记录集的Access窗体中，筛选器只能运行一次 [英] In Access form with disconnected ADO recordset, filter only works one time

问题描述

非常感谢您对以前的问题(感谢Hansup和Remou)，我基于在运行时提取的ADODB记录集制作了一个Access表单.这使它具有仅在内存中的复选框.该复选框很好.

Thanks to a great answer to a previous question (thanks Hansup and Remou), I've made an Access form based on a ADODB recordset that pulls at runtime. This enables it to have a checkbox that is only in memory. The checkbox is fine.

现在我被要求客户端"向表单添加一个下拉过滤器.很简单.我创建了一个下拉框，对控件源进行了一些查询，并在After_Update事件中添加了以下代码:

I've now been asked to add a drop-down filter by "Client" to the form. Easy enough. I made the drop-down box, made a little query for the control source, and in the After_Update event, added this code:

Private Sub Combo_PickClient_AfterUpdate()
    Me.Filter = "Client='" & Combo_PickClient.Value & "'"
    Me.FilterOn = True
    Me.Refresh
End Sub

出于测试目的，我选择了2个客户端.当我打开表单时，它显示了两个客户的数据(良好).当我选择一个客户端时，它成功过滤了数据(也很好).当我选择第二个客户时，它什么也没做(不是很好)

For testing purposes, I chose 2 clients. When I open the form, it shows both client's data (good). When I pick one client, it successfully filters the data (also good). When I pick the second client, it does nothing (not so good)

为什么此过滤器只能工作一次?它不会引发任何错误.屏幕只是刷新而已.

Why does this filter only work one time? It doesn't throw any error. The screen simply refreshes and that's it.

推荐答案

我的最佳猜测是，当您尝试修改或删除非空的.Filter属性时，Access尝试从数据提供程序重新加载表单的记录集.由于断开连接的记录集没有提供程序，因此该尝试失败.在测试中，我实际上触发了错误#31，"数据提供程序无法初始化".

My best guess is that Access tries to reload the form's recordset from the data provider when you attempt to modify or remove a non-empty .Filter property. Since the disconnected recordset doesn't have a provider, that attempt fails. In my testing, I actually triggered error #31, "Data provider could not be initialized" at one point.

在第一次尝试(成功)中，.Filter属性事先为空.我看到了相同的行为，我猜想Access可以在不重新访问数据提供程序的情况下将.Filter应用于未过滤的记录集.

In your first attempt (which succeeded), the .Filter property was empty beforehand. I saw the same behavior and I'm guessing Access can apply a .Filter to an unfiltered recordset without revisiting the data provider.

很抱歉，您的猜测.不幸的是，这是我能提供的最好的解释.

Sorry about the guesswork. Unfortunately, that's the best I can offer for an explanation.

无论如何，我放弃了尝试使用表单的.Filter属性来实现我认为想要的功能.我发现根据查询在其WHERE子句中包含.Filter字符串的查询来简单地重新加载断开连接的记录集会更容易.代码更改很小，运行时性能成本可忽略不计...更改组合框选择后，立即显示重新加载的记录集.

Anyway, I gave up trying to use the form's .Filter property to accomplish what I think you want. I found it easier to simply reload the disconnected recordset based on a query which includes your .Filter string in its WHERE clause. The code changes were minor, and the run time performance cost is neglible ... the reloaded recordset is displayed instantaneously after changing the combo box selection.

首先，我将构建断开记录集的代码从Form_Open移到了一个签名为...的单独函数中.

First I moved the code which builds the disconnected recordset from Form_Open to a separate function whose signature is ...

Private Function GetRecordset(Optional ByVal pFilter As String) _
    As ADODB.Recordset

该函数在SELECT查询的WHERE子句中合并了一个非空的pFilter参数，该子句为断开连接的记录集提供数据.

The function incorporates a non-empty pFilter argument into the WHERE clause of the SELECT query which feeds the disconnected recordset.

然后我将Form_Open更改为一条语句...

Then I changed Form_Open to one statement ...

Set Me.Recordset = GetRecordset()

因此，如果您的组合框和断开连接的记录集都在同一窗体上，则该组合的After Update过程可能为...

So, if your combo box and disconnected recordset are both on the same form, the combo's After Update procedure could be ...

Private Sub Combo_PickClient_AfterUpdate()
    Set Me.Recordset = GetRecordset("Client='" & _
        Me.Combo_PickClient & "'")
End Sub

在我的情况下，断开连接的记录集显示在子窗体中，并且组合框位于其父窗体上.因此，我在子窗体代码模块中创建了一个包装过程，并从组合的After Update中调用该过程:

In my case, the disconnected recordset is displayed in a subform and the combo box is on its parent form. So I created a wrapper procedure in the subform code module and call that from the combo's After Update:

Call Me.SubFormControlName.Form.ChangeRecordset("Client='" & _
    Me.Combo_PickClient & "'")

包装程序非常简单，但是我发现它很方便...

The wrapper procedure is very simple, but I found it convenient ...

Public Sub ChangeRecordset(Optional ByVal pFilter As String)
    Set Me.Recordset = GetRecordset(pFilter)
End Sub

这篇关于在具有断开的ADO记录集的Access窗体中，筛选器只能运行一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！