Bookmarks and Recordset Clones

If you're anything like my 10-year-old daughter, you will read Prisoner of Ascaban in three days. As she was reading so constantly, the book was hardly out of her hands, so there was little need for a bookmark to keep her place so that she could return to it at a later time. In software terms, a Bookmark is the same thing.

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, if you wanted 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.

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 Jet tables always support Bookmarks, not all recordset types do. Recordsets based on external data sources may not allow them, for example, those based on linked Paradox tables that have no primary key. For that reason, you should always check the Recordset object's Bookmarkable property before attempting to use Bookmarks on non-Jet 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 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

0 0

Post a comment