Clone Method

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object, Table-Type Recordset Object.

Description

Creates a duplicate Recordset object that refers to the original object.

Syntax

Set duplicate = original.Clone( )

The Clone method syntax has these parts.

Part	Description

duplicate	A variable of an object data type identifying the duplicate Recordset object you're creating.
original	A variable of an object data type identifying the Recordset object you want to duplicate.

Remarks

Use the Clone method to create multiple, duplicate Recordset objects. Each recordset can have its own current record. Using Clone by itself doesn't change the data in the objects or in their underlying structures. Using the Clone method, you can share bookmarks between two or more recordsets because their bookmarks are interchangeable.

Note

When used on a table-type Recordset object, the Index property setting is not cloned on the new copy of the Recordset. You must copy the Index property setting manually.

You can use Clone when you want to perform an operation on a recordset that requires multiple current records. This is faster and more efficient than creating a second recordset.

A recordset you create with Clone initially lacks a current record. To make a record current before you use the recordset object specified by duplicate, you must set the Bookmark property or use one of the Move methods, one of the Find methods (for dynaset- and snapshot-type Recordset objects only), or the Seek method (for table-type Recordset objects only).

Cloning a recordset that resulted from running a QueryDef object doesn't run the query again.

Using the Close method on either the original or duplicate object doesn't affect the other object. For example, using Close on the original Recordset does not close the clone.

Note

You can't use the Clone method with forward-only-scrolling snapshots.

See Also

Bookmark Property.

Specifics (Microsoft Access)

If you use the Bookmark property of a Recordset object in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Example

This example creates a Recordset object based on the Orders table in the Northwind database and uses the Clone method to create a second Recordset object. Each Recordset object has its own current record that can be moved independently of the other. A bookmark is used to initially make the same record current in both Recordset objects.

Dim dbsNorthwind As DatabaseDim rstOriginal As Recordset, rstDuplicate _     As RecordsetDim strPosition As String

Set dbsNorthwind = DBEngine.Workspaces(0).Databases("Northwind.mdb")
' Create first Recordset.
Set rstOriginal = dbsNorthwind![Orders].OpenRecordset(dbOpenDynaset)
strPosition = rstOriginal.Bookmark    ' Save current record position.
rstDuplicate = rstOriginal.Clone ()    ' Create duplicate. Recordset.rstDuplicate.Bookmark = strPosition    ' Go to same record.

Example (Microsoft Access)

The following example creates a Recordset object using an SQL statement on an Employees table and then uses the Clone method to create a clone of the Recordset object so that bookmarks can be shared between the two objects. This technique is especially useful when you have to compare the results of a query from more than one point at the same time.

The following function creates a dynaset-type Recordset object on an Employees table, then creates a clone of that Recordset object. The function reads and stores in a string variable the value of the Bookmark property for the current record, in this case the second record in the original Recordset object. The Bookmark property of the duplicate Recordset object is set to this string, making the second record the current record in this Recordset object as well. The current values of the LastName field for both recordsets are identical, as you can see once they are printed in the Debug window.

Sub CreateClone()
    Dim dbs As Database
    Dim rstEmployees As Recordset, rstDuplicate As Recordset
    Dim fldName As Field, strBook As String

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Open dynaset-type Recordset object.
    Set rstEmployees = dbs.OpenRecordset("SELECT * FROM Employees " & _
        " ORDER by [LastName]")
    ' Clone Recordset object.
    Set rstDuplicate = rstEmployees.Clone
    Set fldName = rstEmployees.Fields!LastName
    ' Set current record.
    rstEmployees.MoveFirst
    ' Move to second record.
    rstEmployees.MoveNext
    ' Get Bookmark value and print current field value.
    If rstEmployees.Bookmarkable Then
        strBook = rstEmployees.Bookmark
        Debug.Print fldName.value
    Else
    ' If Recordset object doesn't support bookmarks, exit procedure.
        Exit Sub
    End If
    ' Set Bookmark property of clone to obtained value.

    rstDuplicate.Bookmark = strBook
    Debug.Print fldName.value
End Sub

Example (Microsoft Excel)

This example displays a custom dialog box containing the lists of data from the CONTACTS and CUSTMR_ID fields in the Customer recordset of the NWINDEX.MDB database.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs1 = db.OpenRecordset("SELECT * FROM Customer" _
    & " WHERE [REGION] = 'WA' ORDER BY [CUSTMR_ID];")
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
Set list2 = theDialog.ListBoxes.Add(183, 42, 84, 80)
Set rs2 = rs1.Clone()
rs2.MoveFirst
Do Until rs1.EOF
    list1.AddItem (rs1.Fields("CONTACT").Value)
    rs1.MoveNext
Loop
Do Until rs2.EOF
    list2.AddItem (rs2.Fields("CUSTMR_ID").Value)
    rs2.MoveNext
Loop
rs1.Close
rs2.Close
db.Close