MS-Access / Getting Started

---

Bookmarks and Recordset Clones

A recordset Bookmark is a special marker that you place in your recordset so you can quickly return or refer to it at some later stage. For example, to move from your current position in the recordset to check or change a value in some other part of the same recordset, you could set a Bookmark, move to the other spot, make your changes, and then return to where you were in the first place.

In terms of recordsets, a clone is a functional replica of the original. A clone of a recordset points to the same data as the recordset it was copied from. Changes made to the data in the clone are reflected in the original recordset. The difference is primarily in navigation. Using a cloned recordset, you can navigate or search for data without moving the cursor in the original recordset. For example, you might want to search for data in a form without changing the record position of the form. Using a clone, you can perform the search, and then when you find the data you're looking for, save the current Bookmark for the clone. Once the Bookmark has been set, then set the Bookmark in the original recordset to move its cursor.

Using Bookmarks

When you open a recordset, every row is automatically assigned a unique internal Bookmark, and as you will soon see, creating a reference to a Bookmark is simply a matter of setting the value of a variable. So there is really no practical limit to the number of bookmarks you can set. When you close the recordset, the internal Bookmarks are lost, and any Bookmarks you have set become invalid.

Although recordsets based entirely on Access tables always support Bookmarks, not all recordset types do. Recordsets based on external data sources may not allow them. For example, recordsets based on linked Paradox tables that have no primary key do not support bookmarks. For that reason, you should always check the Recordset object's Bookmarkable property before attempting to use Bookmarks on non-Access recordsets.

Using Bookmarks is much faster than using the other recordset navigation methods. The following procedure demonstrates how to use Bookmarks for record navigation:

Public Sub UsingBookmarks()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varBookmark As Variant

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)

    If rst.AbsolutePosition > -1 Then
	'Force the entire recordset to load
	rst.MoveLast
	rst.MoveFirst

	'Move to the middle of the recordset, and print
	'the current cursor position, for reference
	rst.PercentPosition = 50
	Debug.Print "Current position: " & rs.AbsolutePosition

	'Set the bookmark
	varBookmark = rst.Bookmark

	'Move to the last record, and print its position
	rst.MoveLast
	Debug.Print "Current position: " & rs.AbsolutePosition

	'
	'Do whatever you came here to do
	'

	'Now move back, and verify the position
	rst.Bookmark = varBookmark
	Debug.Print "Current position: " & rs.AbsolutePosition
    End If

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Now What About Those Clones?

As mentioned earlier, a clone is a functional replica of the original. Now let's take a closer look at how to use them. There are two clone methods: Clone and RecordsetClone. Clone is a method of the Recordset object, whereas RecordsetClone is a property of the Access Form object. Both are identical in function, except that you can't set the Filter or Sort properties for recordsets created using the RecordSetClone property.

Microsoft states in the online help that the recordset returned by the Clone method has no current position when it is first created. Calling AbsolutePosition straight after creating the clone indicates that it does; however, I'm inclined to take Microsoft at its word and not rely on a clone having a current position until after I've executed one of the Move methods.

If you use the Clone or RecordsetClone method to create a copy of the original recordset, all the bookmarks are identical because rather than creating a new recordset from scratch, the two clone methods simply point an object variable at the original set of rows. The clone operates on exactly the same data as the original, so any changes made in one are reflected in the other. But (and here's the nifty part), although the data and bookmarks are identical, you can operate on the clone independent of the original; that is, you can change the cursor position in the clone (by using any of the navigation methods) and have no effect on the cursor position in the original. It is for this reason that recordset clones and bookmarks are usually mentioned together.

Let's say you are designing a data entry form for customers and that you want to allow the users to type in a customer number, and have the form immediately display the record for the customer with that number. There are several ways to do this, not all of them satisfactory.

You could use DoCmd.ApplyFilter or reopen the form using a filter with DoCmd.OpenForm, but at best, they would return only one record, and your form navigation buttons would be useless. At worst, they would return an empty recordset. The solution is to use a bookmark and recordset clone together. In the AfterUpdate event of your Customer Number text box, you could add the following code:

Private Sub txtEnterCustNo_AfterUpdate()
    Dim rstClone As DAO.Recordset
    Dim strCustNo As String

    'Remove leading and trailing spaces
    strCustNo = Trim(Me.txtEnterCustNo)

    'Check that the text box contains a value
    If strCustNo <> "" Then

	'Create a clone of the form's recordset
	Set rstClone = Me.RecordSetClone

	'Search for the customer's record
	rstClone.FindFirst "[CustNo] = """ & strCustNo & """"

	'The FindFirst method is explained in the following section
	'Test the result of the search
	If rstClone.NoMatch Then

	    'NoMatch returned True (not a match)
	    MsgBox "Customer not found."
	Else
	    'NoMatch returned False (found)
	    'The clone's bookmark is now set to its current position
	    'which is the row returned by the FindFirst method
	    'Move the form's current cursor position
 	    'to the one pointed to by the clone's bookmark
	    Me.Bookmark = rstClone.Bookmark
	End If
    End If

    'Clean up
    On Error Resume Next
    rstClone.Close
    Set rstClone = Nothing

End Sub

Examining the code, you can see that the real work is done in no more than four lines.

1. Create a clone of the form's recordset.
   Set rsClone = Me.RecordsetClone
2. Search for the record using the clone (leaves the original recordset untouched).
   rsClone.FindFirst "[CustNo] = """ & strCustNo & """"
3. Check if the search failed. If so, you return a message box to inform the user. If the search passes, you execute line 4.
   If rsClone.NoMatch Then
4. Change the form's Bookmark.
   Me.Bookmark = rsClone.Bookmark