Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form Recordset and Bookmarks


Bookmarks are stored on the record of a Form's Recordset when the Form is loaded into memory.  When a Table or Query linked to a Form is open, a unique Id (of String Type) is generated and stored in the Bookmark Property of each record.  When you close the Form, the Bookmark Property value is cleared.  Bookmarks are two Byte data of String Type.  They are not displayable or printable characters, when printed on the screen, it simply displays a question mark symbol.

Not all Recordsets are Bookmarked and this can be checked by reading its Bookmarkable Property Value.  If the Bookmarkable Property value is False, then this Recordset doesn’t have bookmarks.

If you create a Recordsetclone in VBA from a Form’s (say Form-A) Recordset the Recordsetclone’s bookmark and Form’s Recordset bookmarks will be identical. You can use the StrComp() Function to compare Bookmarks.  Use 0 (zero) as the third argument of the StrComp() Function.

But, if you load the same Table on a different Form (say Form-B) at the same time both form’s Recordset bookmarks will not be identical.  When you close and open the Form with the same table a second time both session’s bookmarks of records will not be identical.

When a Linked Table having no Primary Key is open in a Form, then that Recordset will not have any bookmarks.

When Form doesn’t have a Record Source Value then addressing Form’s Bookmark Property will trigger an error.  But, when a Table or Query is loaded into the Record Source property of the Form, the Form will have a Bookmark Property only for the Current Record.  You can move the records on the Form and read their bookmarks and save them into different Variables to come back to those 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 Employees Table.

  3. On 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 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 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 Table

No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code