Form Recordset and Bookmarks

Introduction.

Bookmarks are stored in a form’s Recordset when the form is loaded into memory. For each record in the table or query linked to the form, a unique string-type identifier is generated and stored in the Bookmark property. When the form is closed, the Bookmark property values are cleared. Bookmarks are two-byte string values that cannot be displayed or printed directly—if printed to the screen, they appear as question mark symbols.

Not all Recordsets support bookmarks. This can be verified by checking the Bookmarkable property. If Bookmarkable = False, the recordset does not support bookmarks.

When you create a recordset clone in VBA from a form’s recordset (for example, Form-A), the clone and the form’s recordset will share identical bookmark values. You can use the StrComp() function to compare these bookmarks, with 0 (zero) specified as the third argument for a binary comparison.

However, if you load the same table into a different form (say, Form-B) at the same time, the bookmarks in the two forms’ recordsets will not be identical. Similarly, if you close and reopen a form that uses the same table, the bookmarks generated in each session will differ.

If a linked table does not have a primary key, then its recordset will not support bookmarks when opened in a form.

When a form has no RecordSource value, attempting to access its Bookmark property will trigger an error. But when a table or query is assigned as the form’s RecordSource, the form will maintain a Bookmark property for the current record. As you navigate through the records, you can read and store these bookmarks in variables, allowing you to return to specific records later through VBA.

Sample Bookmark-Based Trial Run.

Let us try a simple example to save the Bookmark of a record on the Form into a variable and use it later to come back to the bookmarked record.

1. Import the Employees Table from the Northwind sample database.

2. Create a Tabular Form for the Employees Table.

3. In the Footer Section of the Form, create two Command Buttons.

4. Select the first Command Button.

5. Display its Property Sheet (F4).

6. Change the Name Property value to cmdSave. 

7. Change the Name Property value of the second Command Button to cmdRestore.

8. Display the VBA Module of the Employees Form.

9. Copy and paste the following code into the VBA Module:

   Dim bkMark As Variant
   
   Private Sub cmdRestore_Click()
      Me.Bookmark = bkMark
      MsgBox "Bookmark Restored"
   End Sub
   
   Private Sub cmdSave_Click()
       bkMark = Me.Bookmark
       MsgBox "Bookmark saved" 
   End Sub
   
   

10. Save and Close the Form.

11. Open the Form in normal view, showing employee records.

12. Use the record navigation control to move to the 5th record.

13. Click on the Save Command button to save the Bookmark of the current record in the 'bkMark' Variable.

14. Now, move a few records forward on the Form.

15. Click on the Restore Command Button to quickly make the 5th record current on the Form, by copying the Bookmark from the 'bkMark' Variable into the Form’s Bookmark Property.  You can try this out with different records on the Form.

The following links will show you more tricks on this topic with interesting examples:

1. Form Bookmarks and Data Editing
2. Form Bookmarks and Data Editing-2
3. Form Bookmarks and Data Editing-3
4. Forms and Custom Properties
5. Saving Data on Forms, not in a Table