Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

How to: Mark a Position in a DAO Recordset

Office 2007

A bookmark is a system-generated Byte array that uniquely identifies each record. The DAO Bookmark property of a Recordset object changes each time you move to a new record. To identify a record, move to that record and then assign the value of the DAO Bookmark property to a variable of type Variant. To return to the record, set the DAO Bookmark property to the value of the variable.

The following example illustrates how to find the product that makes the most and least revenue per category by using a bookmark to save the current record position. By using a bookmark, you can perform other operations on the Recordset object, and then return to the saved record position.

Sub GetProductStats()

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim rstCategories As DAO.Recordset
Dim varFirstMark As Variant
Dim varHighMark As Variant
Dim varLowMark As Variant
Dim curHighRev As Currency
Dim curLowRev As Currency
Dim strSQL As String
Dim strCriteria As String
Dim strMessage As String

On Error GoTo ErrorHandler

   Set dbsNorthwind = CurrentDb

   strSQL = "SELECT * FROM Products WHERE UnitsOnOrder >= 40 " & _
            "ORDER BY CategoryID, UnitsOnOrder DESC"
   Set rstProducts = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)
   If rstProducts.EOF Then Exit Sub

   StrSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
            "ORDER BY CategoryID"
   Set rstCategories = dbsNorthwind.OpenRecordset(strSQL, dbOpenSnapshot)

   'For each category find the product generating the least revenue
   'and the product generating the most revenue.
   Do Until rstCategories.EOF

      strCriteria = "CategoryID = " & rstCategories![CategoryID]
      rstProducts.FindFirst strCriteria
      curHighRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]

      If Not rstProducts.NoMatch Then

         'Set bookmarks at the first record containing the CategoryID.
         varFirstMark = rstProducts.Bookmark
         varHighMark = varFirstMark
         varLowMark = varFirstMark

         'Find the product generating the most revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] > _
            curHighRev Then
               curHighRev = rstProducts![UnitPrice] * _
                            rstProducts![UnitsOnOrder]
               varHighMark = rstProducts.Bookmark
            End If
            rstProducts.MoveNext
         Loop

         'Move to the first record containing the CategoryID.
         rstProducts.Bookmark = varFirstMark
         curLowRev = rstProducts![UnitPrice] * rstProducts![UnitsOnOrder]

         ' Find the product generating the least revenue.
         Do While rstProducts![CategoryID] = rstCategories![CategoryID]
            If rstProducts![UnitPrice] * rstProducts![UnitsOnOrder] < _
            curLowRev Then
               curLowRev = rstProducts![UnitPrice] * _
                           rstProducts![UnitsOnOrder]
               varLowMark = rstProducts.Bookmark
            End If
            rstProducts.MoveNext
         Loop

      End If

      'Set high and low bookmarks to build the message string.
      strMessage = "CATEGORY:  " & rstCategories!CategoryName & _
                   vbCrLf & vbCrLf
      rstProducts.Bookmark = varHighMark
      strMessage = strMessage & "HIGH: $" & curHighRev & "  " & _
                   rstProducts!ProductName & vbCrLf
      rstProducts.Bookmark = varLowMark
      strMessage = strMessage & "LOW:  $" & curLowRev & "  " & _
                   rstProducts!ProductName
      MsgBox strMessage, , "Product Statistics"
      rstCategories.MoveNext
   Loop

   rstProducts.Close
   rstCategories.Close
   dbsNorthwind.Close

   Set rstProducts = Nothing
   Set rstCategories = Nothing
   Set dbsNorthwind = Nothing

Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

A bookmark is particularly useful if a method fails because the current record position is undefined.

The LastModified property of the Recordset object provides a good illustration of how to use a bookmark. The LastModified property returns the bookmark of the last record in the Recordset to be added or modified. To use it, set the DAO Bookmark property equal to the LastModified property, as follows:

rstCustomers.Bookmark = rstCustomers.LastModified

This moves the current record position to the last record that was added or modified. This is particularly useful when adding new records, because after you add a new record, the current record is the one you were on before you added the record. With the LastModified property, you can move to the newly added record if that is what your application expects.

When you close a Recordset object, any bookmarks you saved become invalid. You cannot use a bookmark from one Recordset in another Recordset, even if both Recordset objects are based on the same underlying table or query. However, you can use a bookmark on the clone of a Recordset, as shown in the following example:

Dim dbsNorthwind As DAO.Database
Dim rstOriginal As DAO.Recordset
Dim rstDuplicate As DAO.Recordset
Dim varBookMark As Variant

   Set dbsNorthwind = CurrentDb

   'Create the first Recordset.
   Set rstOriginal = dbsNorthwind.OpenRecordset("Orders", dbOpenDynaset)

   'Save the current record position.
   varBookMark = rstOriginal.Bookmark

   'Create a duplicate Recordset.
   Set rstDuplicate = rstOriginal.Clone()

   'Go to the same record.
   rstDuplicate.Bookmark = varBookMark

   rstOriginal.Close

You can also use the DAO Bookmark property on the Recordset object underlying a form. With this property, your code can mark which record is currently displayed on the form, and then change the record that is being displayed. For example, on a form containing employee information, you may want to include a button that a user can click to show the record for an employee's supervisor.

Bb243795.vs_note(en-us,office.12).gif  Note
Dynasets based on certain linked tables, such as Paradox tables that have no primary key, do not support bookmarks, nor do forward-only-type Recordset objects. You can determine whether a given Recordset object supports bookmarks by checking the value of the Bookmarkable property.


Community Additions

ADD
Show:
© 2015 Microsoft