Why clone an MS-Access recordset?

Question

I'm a newbie at VBA and attempting to understand someone else's code.

Set rstClone = Me.RecordsetClone
rstClone.MoveFirst

Why does the recordset have to be cloned? Why can't the code be Me.Recordset.MoveFirst?

Swayback

Please note that .recordsetClone is only available with DAO recordsets. Calling this property on a form whith an ADODB recordset will lead to a bug at runtime (though not detected at compile time) – Chelseychelsie

Answer 1

You may wish to use the recordsetclone because you do not wish to affect the records displayed in the form, which me.recordset.movefirst would do.

Fossette

Answer 2

First off, the recordset is not cloned -- the form's Recordsetclone exists as long as there is a recordsource, even if it contains no records.

Second, the recordsetclone is an independent recordset that you can navigate and not have an effect on the form's edit buffer, which has an independent set of record pointers (i.e., bookmarks).

That said, it's pretty senseless to set a recordset variable to the recordsetclone. Instead, just use a WITH block:

  With Me.RecordsetClone
    .FindFirst "[MyPK]=" & Me!cmbFindByPK
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
  End With

The alternative using setting a recordset variable looks like this:

  Dim rs As DAO.Recordset

  Set rs = Me.RecordsetClone
  rs.FindFirst "[MyPK]=" & Me!cmbFindByPK
  If Not rs.NoMatch Then
     If Me.Dirty Then
        Me.Dirty = False
     End If
     Me.Bookmark = rs.Bookmark
  End If
  Set rs = Nothing

Note also that since Access 2000, the form also has a Recordset object in addition to the RecordsetClone. That object gives you access to the form's actual edit buffer, and navigation through it changes the record pointer in the form itself. I would avoid using it, though, as the indirection of using a separate identical object that is a dynaset of the same data seems a helpful layer of protection from doing things one oughtn't.

Sonneteer

Answer 3

Keep in mind that record sets have which called a clone method. This is different then the forms record set clone.

In your example and question we talking about the underlying data that the form is based on.

If you’re going to play with, and move through records using code that that a form is based on, but you don’t want the form display or graphical interface to follow you along or jump around then your example is the correct and preferred way to accomplish this.

So, record set clone is a copy of the forms data. It allows you to move or traverse records in that record set, but the form (the user interface) does not follow your moving through the records.

Remember, in some cases if you do in fact want the form to move to the next record, then you would not use records set clone, but Use the actual record set.

Eg:

Set rstClone  = me.recordset
rstClone.movenext

In the above, the form would then move to the next record.

A typical situation is when you’re using sub forms. If you wanted to total up or traverse the 10 records in that sub-form, you can do so without affecting or causing the current display record the sub form is currently pointing to change. It lets you do things Behind the scenes so to speak.

However, if you just wanted to move to the forms next record, then you don’t need either reocrdset, or recordset clone, you could just execute a command that moves the form to the next record.

You could use the following typical command :

DoCmd.GoToRecord acActiveDataObject, , acNext

And, you don't need recordset, or recordsetClone if you wanting to look at values in code placed in a form, you can just go:

me!nameOfCollumFromTable

Allveta

Answer 4

Using the recordset property can cause unintended behavior. Specifically (in Access 2010), if you refer to me.recordset, it can unexpectedly cause the form's edit buffer to be saved.

Ex.:

debug.print me.recordset.recordcount

will cause the form's record to be saved. Using recordsetClone will not exhibit this [mis]behavior.

Form