Bookmark Property

Applies To

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 or sets the current record in a Recordset object to a valid bookmark.

Settings and Return Values

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

Remarks

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

When a Recordset object is created or opened, 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, inspect 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 create a copy of a Recordset object using the Clone method, 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 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.

To refresh the contents of a record, set the Bookmark property to itself. (This technique, however, also cancels any pending operations invoked by the Edit or AddNew methods.) For example:

rstCustomers.Bookmark = rstCustomers.Bookmark

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

Note

The Bookmark property doesn't apply to forward-only scrolling snapshots.

See Also

Bookmarkable Property, RecordCount Property.

Specifics (Microsoft Access)

When you write Visual Basic® code that uses the Bookmark property, you must include an Option Compare Binary statement in the Declarations section of the module. The 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 do not interfere with each other; you can have separate bookmarks on a form and on a Recordset object at the same time.

Example

This example shows how you can save your place in a Recordset by saving a bookmark. Once saved, this bookmark can be applied to the Bookmark property to reposition the current record pointer to any location in the Recordset. The FindYear function works with the Biblio.mdb database. It looks up all titles in a given Recordset published in a given year, and fills a string array with the bookmarks of any records that match.

Function FindYear (intYear As Integer, rstBooks As Recordset, _
    avarRecord() As Variant) As Integer
    Dim intCount As Integer
    rstBooks.FindFirst "[Year Published] = " & intYear

    If (rstBooks.NoMatch = True) Or (rstBooks.Bookmarkable = False) _
        Then
        FindYear = 0
        Exit Function
    Else
        Do Until rstBooks.NoMatch = True
            avarRecord(intCount) = rstBooks.Bookmark
            rstBooks.FindNext "[Year Published] = " & intYear
            intCount = intCount + 1
        Loop
    End If
    FindYear = intCount
End Function

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 the Declarations section of the 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 Database variable that points to current database.
    Set dbs = CurrentDb
    ' Open a table-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees")
    Set fld = rst.Fields!LastName
    ' Populate the Recordset object.
    rst.MoveLast
    rst.MoveFirst
    ' Redimension array with value of RecordCount as upper bound.
    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
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 onto 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
region = Application.InputBox("What state do you want data from?", _
    "Specify two letters (e.g. 'WA')", Type:=2)
If region = False Then    ' user cancelled InputBox
    Exit Sub
End If
criteria = "[REGION] = '" & region & "'"
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