Microsoft Office 2000/Visual Basic Programmer's Guide

Using Bookmarks

A bookmark is a system-generated value that uniquely identifies each record in a scrolling Recordset object. The Bookmark property of the Recordset object specifies the value of the current record. You can use the Bookmark property to store the bookmark of the current record and then return to that record from any other record in the Recordset object. To store the bookmark for a record, move to that record, and then assign the value of the Bookmark property to a Variant variable. To return to the bookmarked record after moving to a different record, set the Bookmark property to the value of that variable.

If you use the Clone method to create a copy of a Recordset object, the Bookmark property settings for the original and cloned Recordset objects are identical and you can use them interchangeably. However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created from the same source or command.

The following procedure shows how you can use a bookmark to save the current record position and then return to it from any record in the Recordset object.

Sub BookmarkRecord()
   Dim rst   As ADODB.Recordset
   Dim strMessage As String
   Dim intCommand As Integer
   Dim varBookmark As Variant

   ' Open Recordset object with data from Employees table.
   Set rst = New ADODB.Recordset
   With rst
      .Open Source:="SELECT LastName, FirstName FROM Employees " & _
            "ORDER BY LastName", _
            ActiveConnection:=CurrentProject.Connection, _
            CursorType:=adOpenStatic, _
            Options:=adCmdText
      .MoveFirst

      Do While True
         ' Display information about current
         ' record and get user input.
         strMessage = _
            "Employee: " & !FirstName & " " & !LastName & vbCr & _
            "(record " & .AbsolutePosition & _
            " of " & .RecordCount & ")" & vbCr & vbCr & _
            "Enter command:" & vbCr & _
            "1 = Next" & vbCr & _
            "2 = Previous" & vbCr & _
            "3 = Set Bookmark" & vbCr & _
            "4 = Go to Bookmark"
         intCommand = Val(InputBox(strMessage))

         Select Case intCommand
            ' Move forward, trapping for EOF.
            Case 1
               .MoveNext
               If .EOF Then
                  MsgBox "You tried to move past the last record." & _
                     vbCr & "Try again."
                  .MoveLast
               End If
            ' Move backward, trapping for BOF.
            Case 2
               .MovePrevious
               If .BOF Then
                  MsgBox "You tried to move past the first record." & _
                     vbCr & "Try again."
                  .MoveFirst
               End If
            ' Store the bookmark of the current record.
            Case 3
               varBookmark = .Bookmark
            ' Go to the record indicated by the stored bookmark.
            Case 4
               If IsEmpty(varBookmark) Then
                  MsgBox "No Bookmark set!"
               Else
                  .Bookmark = varBookmark
               End If
            Case Else
               Exit Do
         End Select
      Loop
      .Close
   End With
   Set rst = Nothing
End Sub

The BookmarkRecord procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.