In an access form with an ADO record set disabled, the filter only works once

Question

Thanks to the excellent answer to the previous question (thanks to Hansup and Remou), I created an access form based on the ADODB recordset that pulls on at runtime. This allows you to have a flag that is only in memory. Check box is ok.

Now I was asked to add the Client drop-down filter to the form. Easy enough. I made a drop-down list, made a small request for the control source, and added this code in the After_Update event:

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

For testing purposes, I selected 2 clients. When I open the form, it shows how the client data (good). When I select one client, it successfully filters the data (also good). When I select the second client, he does nothing (not so good)

Why does this filter work only once? This does not cause any errors. The screen is just refreshing and what it is.

Answer 1

My best guess is that Access is trying to reload a set of form records from the data provider when trying to modify or delete a non-empty .Filter property. Since there is no provider in the disconnected record set, this attempt fails. In my testing, I actually caused error # 31, “The data provider could not be initialized” at some point.

In your first attempt (which succeeded), the .Filter property was empty in advance. I have seen the same behavior, and I assume that Access can apply .Filter to the unfiltered record set without revising the data provider.

Sorry for the guesswork. Unfortunately, this is the best I can offer for an explanation.

In any case, I refused to use the .Filter property to accomplish what you think you want. It was easier for me to simply reload the disconnected recordset based on the query, which includes the .Filter line in its WHERE . The code changes were minor, and the cost of executing the runtime was negligible ... the reloaded recordset is displayed instantly after changing the list selection.

First, I moved the code that 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 

The function includes a non-empty pFilter argument in the WHERE SELECT query, which passes the disconnected recordset.

Then I changed Form_Open to one statement ...

 Set Me.Recordset = GetRecordset() 

So, if your combo box and the disabled recordset are in the same form, the "After the update is complete" procedure may be ...

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

In my case, a disabled record set is displayed in a subform, and the combo box is in its parent form. Therefore, I created a wrapper procedure in the subform code module and called it from the After Update combo:

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

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

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