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

  • 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.




    • Edited by M Pashalis Saturday, March 9, 2019 10:49 AM
    Friday, March 8, 2019 11:19 AM

Answers

  • 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

    • Marked as answer by M Pashalis Monday, March 11, 2019 11:32 AM
    Monday, March 11, 2019 12:27 AM

All replies

  • 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


    Sunday, March 10, 2019 6:54 PM
  • 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. A
    s 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? 

    Sunday, March 10, 2019 10:52 PM
  • 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

    • Marked as answer by M Pashalis Monday, March 11, 2019 11:32 AM
    Monday, March 11, 2019 12:27 AM
  • 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.
    Monday, March 11, 2019 11:32 AM