Why clone an MS-Access Recordset?

Question

I am new to VBA and trying to understand the code of another.

Set rstClone = Me.RecordsetClone
rstClone.MoveFirst

Why should a recordset be cloned? Why can't the code be Me.Recordset.MoveFirst?

Answer 1

You might want to use a recordset because you don't want to influence the records displayed in the form that me.recordset.movefirst will execute.

Answer 2

First, the record set is not cloned - the Recordsetclone form exists as long as the record source exists, even if it does not contain records.

Secondly, recordetclone is an independent set of records that can be moved without affecting the form editing buffer, which has an independent set of record pointers (i.e. bookmarks).

However, it is pretty pointless to set a record set variable to a record set. Instead, just use the 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 

An alternative using setting a record set variable is as follows:

  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 

Also note that with Access 2000, the form also has a Recordset object in addition to RecordsetClone. This object gives you access to the actual form editing buffer, and when you navigate through it, the record pointer in the form changes. However, I would avoid using it, since the indirectness of using a separate identical object, which is a dynamic set of the same data, seems to be a useful layer of protection against performing any actions that should not be.

Answer 3

Keep in mind that record sets have a clone method. This is different than cloning a recordset.

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

If you are going to play and navigate records using the code on which the form is based, but you do not want the form display or GUI to follow you or jump, then your example is the correct and preferred way to accomplish this.

So, a record set clone is a copy of these forms. It allows you to move or move records in this record set, but the form (user interface) does not follow the movement of records.

Remember that in some cases, if you really want the form to move to the next record, you will not use the cloned record set, but use the actual record set.

For example:

 Set rstClone = me.recordset rstClone.movenext 

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

A typical situation is that you use subforms. If you want to summarize or cross 10 records in this subform, you can do this without affecting or creating the currently displayed record in which the sub-form is currently indicating a change. This allows you to do things backstage, so to speak.

However, if you just want to go to the forms of the next record, you do not need either reocrdset or recordet clone, you can simply execute a command that moves the form to the next record.

You can use the following typical command:

 DoCmd.GoToRecord acActiveDataObject, , acNext 

And you don’t need a recordset or recordsetClone, if you want to look at the values ​​in the code posted on the form, you can just go:

 me!nameOfCollumFromTable 

Answer 4

Using the recordset property can lead to inadvertent behavior. In particular (in Access 2010), if you reference me.recordset, this may unexpectedly cause the form editing buffer to be saved.

Example:.

 debug.print me.recordset.recordcount 

will save the record in the form. Using recordsetClone will not demonstrate this behavior [mis].