Access 2016 - RecordsetClone Error on a form constructed with a virtual recordset opens "Select Data Source" dialog

Question

I've seen a number of posts trying to describe this bug but they haven't framed the problem correctly to be reproduced... or not set the scenario in the way that I've experienced the bug using a common technique.

The bug occurs when a form's recordset is set to a virtual recordset and then referred to by a DAO recordsetclone statement.

We most commonly use this to add a checkbox control to a detail form for a user to select one or more records for further processing. I've used this technique many times in many applications but now it fails.

I have confirmed that this code works correctly in Access 2010.

To set this up:

Create a new ACCDB database
Add the following references to the default references:
Microsoft ActiveX Data Objects 6.1 Library
Microsoft ADO Ext. 2.8 for DDL and Security

Create a testing table:
TestId, AutoNumber, PK
TestText, Short Text

Append about 10 rows to the table.

Create an unbound form with 3 controls:
Checkbox, Name: Selected, Control Source: Selected
Textbox, Name: TestId, Control Source: TestId
Textbox, Name: TestText, Control Source: TextText

In the form's header add a command button: Name: cmdTest, Caption: Test
Set the form Default View: Continuous

In the Form_Open call a sub SetRecordsource which creates a recordset and adds a column "Selected" for the user to check the records they want.

The command button cmdTest will attempt to reference the form's recordsource. It's while attempting to reference the form's recordsouce that the error occurs. Instead of the reference being made, the "Select Data Source" dialog pops up.

The complete form's VBA code:

Option Compare Database
Option Explicit
Private Sub cmdTest_Click()
On Error GoTo errHandler
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
' Using an ADODB recordset works but is an ugly solution
' To test comment out the Dim DAO and Set rs statements above and uncomment the next 2 lines.
'  Dim rs As ADODB.Recordset
'  Set rs = Me.Recordset
  rs.MoveFirst
  With rs
    Do While Not .EOF
      Debug.Print .Fields("Selected"), .Fields("TestId"), .Fields("TestText")
    .MoveNext
    Loop
  End With
  Set rs = Nothing
ExitSub:
  Exit Sub
errHandler:
  MsgBox "Error in " & Me.Name & ".SetRecordsource " & Err.Number & " - " & Err.Description
  Resume ExitSub
End Sub
Private Sub Form_Open(Cancel As Integer)
  SetRecordsource
End Sub
Private Sub SetRecordsource()
  Dim rs As ADODB.Recordset   'the virtual recordset to hold the source data plus the boolean Selected field
  Dim rsSource As DAO.Recordset  'dim the source recordset
 
  Set rs = New ADODB.Recordset
  With rs
    .Fields.Append "Selected", adboolean
    .Fields.Append "TestId", adInteger, , adFldKeyColumn
    .Fields.Append "TestText", adVarChar, 80
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
   
    Set rsSource = CurrentDb.OpenRecordset("Select TestId, TestText from Test", dbOpenDynaset)
    rsSource.MoveFirst
    Do Until rsSource.EOF
      .AddNew
      .Fields("Selected") = 0 'set the checkboxes to unchecked
      .Fields("TestId") = rsSource.Fields(0)
      .Fields("TestText") = rsSource.Fields(1)
      .Update
      rsSource.MoveNext
    Loop
  End With
  Set Me.Recordset = rs 'Set the form's recordset = to our virtual recordset
  Set rsSource = Nothing
  Set rs = Nothing
ExitSub:
  Exit Sub
err_handler:
  MsgBox "Error in " & Me.Name & ".SetRecordsource " & Err.Number & " - " & Err.Description
  Resume ExitSub
End Sub 'SetRecordsource

Open the form and click the Test command button to reproduce the error.

One solution proposed is to use an ADODB recordset and set it to Me.Recordset instead of Me.Recordsetclone. While this does work, it's an ugly solution since you are now operating on the form's recordsource and when looping through the records to find the rows where Selected = True moves the current record on the form. Not only does the current record pointer move but if there's more rows then the can show, the user sees the form's records scrolling.

This used to work in prior versions. Now? Not so much.

I'll be very interested in your feedback and testing results. Thanks in advance for any help.

Answer 1

There's an error in your code.  You say:

    Dim rs As DAO.Recordset
     Set rs = Me.RecordsetClone

but your form's recordset, because of the way you set it in SetRecordsource(), is not a DAO recordset; it's an ADO recordset.  If this worked before, I don't know why.  I don't remember and can't find in the documentation whether the RecordsetClone method always returns a DAO recordset, or whether it will return an ADO recordset if the form's recordset is ADO.  In my quick test, though, this works:

    Dim rs As ADODB.Recordset
    Set rs = Me.Recordset.Clone  ' note that this calls the recordset's Clone method, NOT the form's RecordsetClone method

If you set rs to a Clone of the form's recordset, navigation operations on rs will not move the form's current record.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Thank you for your response. I have used this technique for years. Far be it for me to debate an MS Access MVP but this technique has beeen well documented. If you like, check 

https://www.databasejournal.com/features/msaccess/article.php/3846361/Create-In-Memory-ADO-Recordsets.htm

(this describes the exact reason I use it) or check other such sources. Further, the setting of the CursorLocation, CursorType, and LockType ADODB properties are crucial to it successfully being accepted as the form's recordset. To the best of my knowledge MS Access forms can ONLY be DAO. When a form's recordset is set to a created in-memory ADO recordset it converts it to DAO. As I said, this works in 2010. Unfortunately, I only have 2003, 2007, 2010 and 2016 to support my clients so I can't test 2013 but I'm confident it works there as well. In any case, I certainly didn't expect it to pop open a dialog and prompt me for a data source.

I am curious, did you happen to follow the steps to recreate the problem? 

Answer 3

Thank you for your response. I have used this technique for years. Far be it for me to debate an MS Access MVP but this technique has beeen well documented. If you like, check 

https://www.databasejournal.com/features/msaccess/article.php/3846361/Create-In-Memory-ADO-Recordsets.htm

(this describes the exact reason I use it) or check other such sources.

I think you must have misunderstood me.  I did not say there's some problem with binding a form to an ADO recordset, whether it's an in-memory recordset or a recordset opened from a table or query.  There's no problem binding forms to ADO recordsets, and I have a few of my own that I created many years ago as proofs of concept, though I haven't had any occasion to use them in production.

Where I believe you've gone wrong (whether it worked before or not) is here:

To the best of my knowledge MS Access forms can ONLY be DAO. When a form's recordset is set to a created in-memory ADO recordset it converts it to DAO. 

This is not correct.  Access forms can be bound either DAO or ADO recordsets, since (I think) Access 2000.  If you bind a form to an ADO recordset, it does not convert it to a DAO recordset.  You can test this easily enough by opening two forms, one bound to a DAO recordset and one bound to an ADO recordset, and using the Locals window to explore the properties of each form's recordset.  You'll see that the recordset of the form bound to the ADO recordset still has a number of properties that are specific to ADO recordsets, such as ActiveConnection, CursorType, and CursorLocation, while the recordset of the form bound to a DAO recordset does not have these properties.

As far as I recall from so long ago, when Microsoft introduced Access Data Projects (ADPs) as what they hoped would be the wave of the future, forms in ADPs were bound to ADO recordsets by default, while forms in MDBs were bound to DAO recordsets by default.  It was at that time that they changed some of the code samples in the help file to use the Clone method of the form's Recordset instead of the form's RecordsetClone property.  (As a note, they changed some of that documentation so that it was in fact wrong, but let's not digress.)

In any case, I certainly didn't expect it to pop open a dialog and prompt me for a data source.

Yes, that is interesting.  When I tested this, I found that any reference to the form's RecordsetClone property causes this dialog to pop up.  I can't say whether it did that in previous versions, because I never had occasion to do that.  

I am curious, did you happen to follow the steps to recreate the problem? 

I didn't follow your exact steps, because I already had a form of my own, in a test database, that was bound to an ADO recordset, though not an in-memory one.  I found that I could get the "Select Data Source" dialog to pop up by code that refers to the form's RecordsetClone property.  However, I could get the Clone of the recordset and do anything I wanted with it, by using code like this:

    Dim rs As ADODB.Recordset
    Set rs = Me.Recordset.Clone

Did you try it?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Dirk,
Thanks so much for sticking with this. I changed the code as suggested (Set rs = Me.Recordset.Clone), tested and all is well. You also taught me more about forms and the underlying recordset types. The pop up of the "Select Data Source" dialog is still odd but of no concern now that the code is functioning correctly. 
BTW... I miss ADP and didn't understand why it was abandoned.
Again, many thanks.