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.
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.
- Create a clone of the form's recordset.
Set rsClone = Me.RecordsetClone
- Search for the record using the clone (leaves the original recordset untouched).
rsClone.FindFirst "[CustNo] = """ & strCustNo & """"
- 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
- Change the form's Bookmark.
Me.Bookmark = rsClone.Bookmark
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields