Bookmark Property

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Sets or returns a bookmark that uniquely identifies the current record in a Recordset object.

Settings and Return Values

The setting or return value is a string expression or variant expression that evaluates to a valid bookmark. The data type is a Variant array of Byte data.

Remarks

For a Recordset object based entirely on Microsoft Jet tables, the value of the Bookmarkable property is True, and you can use the Bookmark property with that Recordset. Other database products may not support bookmarks, however. For example, you can't use bookmarks in any Recordset object based on a linked Paradox table that has no primary key.

When you create or open a Recordset object, each of its records already has a unique bookmark. You can save the bookmark for the current record by assigning the value of the Bookmark property to a variable. To quickly return to that record at any time after moving to a different record, set the Recordset object's Bookmark property to the value of that variable.

There is no limit to the number of bookmarks you can establish. To create a bookmark for a record other than the current record, move to the desired record and assign the value of the Bookmark property to a String variable that identifies the record.

To make sure the Recordset object supports bookmarks, check the value of its Bookmarkable property before you use the Bookmark property. If the Bookmarkable property is False, the Recordset object doesn't support bookmarks, and using the Bookmark property results in a trappable error.

If you use the Clone method to create a copy of a Recordset object, the Bookmark property settings for the original and the duplicate Recordset objects are identical and can be used interchangeably. However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created by using the same object or the same SQL statement.

If you set the Bookmark property to a value that represents a deleted record, a trappable error occurs.

The value of the Bookmark property isn't the same as a record number.

See Also Bookmarkable property, RecordCount property.

Specifics (Microsoft Access)

When you write Visual Basic code that uses the DAO Bookmark property, you must include an Option Compare Binary statement in the Declarations section of the module. The DAO Bookmark property sets and returns a bookmark, which is a Variant array of Byte data. The string comparison method for the module must therefore 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.

Note Don't confuse this property with the Microsoft Access Bookmark property, which applies to a Form object and stores a bookmark for a particular record in the table or query underlying the form. These two properties don't interfere with each other; you can have separate bookmarks on a form and on a Recordset object at the same time.

Example

This example uses the Bookmark and Bookmarkable properties to let the user flag a record in a Recordset and return to it later.

Sub BookmarkX()

    Dim dbsNorthwind As Database
    Dim rstCategories As Recordset
    Dim strMessage As String
    Dim intCommand As Integer
    Dim varBookmark As Variant

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstCategories = _
        dbsNorthwind.OpenRecordset("Categories", _
        dbOpenSnapshot)

    With rstCategories

        If .Bookmarkable = False Then
            Debug.Print "Recordset is not Bookmarkable!"
        Else
            ' Populate Recordset.
            .MoveLast
            .MoveFirst

            Do While True
                ' Show information about current record and get
                ' user input.
                strMessage = "Category: " & !CategoryName & _
                    " (record " & (.AbsolutePosition + 1) & _
                    " of " & .RecordCount & ")" & vbCr & _
                    "Enter command:" & vbCr & _
                    "[1 - next / 2 - previous /" & vbCr & _
                    "3 - set bookmark / 4 - go to bookmark]"
                intCommand = Val(InputBox(strMessage))

                Select Case intCommand
                    ' Move forward or backward, trapping for BOF
                    ' or EOF.
                    Case 1
                        .MoveNext
                        If .EOF Then .MoveLast
                    Case 2
                        .MovePrevious
                        If .BOF Then .MoveFirst

                    ' 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

        End If

        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example moves through the records of the Employees table from the beginning of the file to the end and stores the value of the Bookmark property for each record in an array.

' Include this statement in Declarations section of module.
Option Compare Binary

Sub RecordPositions()
    Dim dbs As Database, rst As Recordset, fld As Field
    Dim intI As Integer
    ' Declare array to hold bookmarks.
    Dim varRecord() As Variant

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees")
    ' Return reference to LastName field.
    Set fld = rst.Fields!LastName
    ' Redimension array with value of RecordCount property as upperbound.
    ReDim varRecord(0 To rst.RecordCount - 1)
    intI = 0
    ' Check Bookmarkable property of Recordset object.
    If rst.Bookmarkable Then
        Do Until rst.EOF
            ' Populate array with bookmarks.
            varRecord(intI) = rst.Bookmark
            ' Increment counter.
            intI = intI + 1
            rst.MoveNext
        Loop
    End If
    rst.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example prompts the user for a two-letter abbreviation for a state. The example uses this value to find up to 101 matching records in the Customer recordset in the Nwindex.mdb database. It then marks each record with a bookmark and copies the values of the first and third fields to Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim Found(100)
i = 0
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
Sheets("Sheet1").Activate
regionWanted = Application.InputBox("Input state you want data from", _
    "Specify two letters (e.g. 'WA')", Type:=2)
If regionWanted = False Then        ' user cancelled InputBox
    Exit Sub
End If
criteria = "[REGION] = '" & regionWanted & "'"
rs.FindFirst criteria
If rs.NoMatch Or rs.Bookmarkable = False Then
    MsgBox "No records for this state"
    Exit Sub
Else
    Do Until rs.NoMatch = True
        i = i + 1
        Found(i) = rs.Bookmark
        rs.FindNext criteria
    Loop
End If
For n = 1 To i
    rs.Bookmark = Found(n)
    Cells(n + 1, 1).Value = rs.fields(0).Value
    Cells(n + 1, 2).Value = rs.fields(2).Value
Next
MsgBox "There are " & i & " records from this region"
rs.Close
db.Close