Form Bookmarks and Data Editing

Introduction

You need to edit information on 25 records in the Form, selected at random. To begin, open the main data editing Form and use the Find control (Ctrl+F) to search for each record by entering its key value (for example, Employee Code or OrderID). Once the record is found, make the necessary edits. You’ll need to repeat this process for all 25 records the first time, as this is the only way to locate and modify them.

However, since the information you’ve changed is critical, even a small mistake could cause serious issues. Errors can easily slip in when working quickly. Therefore, it’s essential to review each record again to verify that all edits are accurate.

Unfortunately, repeating the same search-and-edit process for all 25 records—opening the Find dialog, typing the key values, and clicking Find Next each time—is far less appealing the second time around.

But what if you could revisit all those records—one by one, in the exact order you edited them—without repeating the entire cumbersome search process? That would certainly make the verification task much faster and easier.

I emphasized the term in the same order because you’ll likely have your source document or change log arranged in the sequence you first edited the records.

To make this possible, we’ll develop a simple yet powerful technique using the Form’s Bookmarks feature. This method will help application users navigate and review their edited records effortlessly.

FORM BOOKMARKS.

When you open a Form that’s linked to a Table, Query, or SQL statement, each record on that Form is automatically assigned a unique identifying tag by MS Access, known as a Bookmark (a two-byte string value). This happens every time the Form is opened with any of these data sources. However, Bookmarks are valid only during the current Form session — they are not stored permanently in Tables.

You can read the Bookmark of any record from the Form’s Bookmark property when that record is current, and store it in memory variables. These saved Bookmarks can then be used to quickly return to any record you previously visited.

To make this process easy to use, I’ve created a function called myBookMarks(). With just a Combo Box, a Command Button, and four lines of VBA code in the Form’s code module, you can implement this powerful navigation method on any Form that has an attached Recordset.

You’re sure to earn a pat on the back from your MS Access application’s users for adding this simple yet powerful feature. Let’s get started with our sample project.

SAMPLE PROJECT.

1. Open your VBA Editing Window (Alt+F11).

2. Create a new Standard Module (Insert -> Module), copy and paste the following Code of myBookMarks() Function into the Module, and Save it:

   Option Compare Database
   Option Explicit
   
   Public Const ArrayRange As Integer = 25
   Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer
   
   Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
   '-----------------------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : October-2009
   'URL    : www.msaccesstips.com
   'Remarks: All Rights Reserved by www.msaccesstips.com
   '-----------------------------------------------------------------
   'Action Code : 1 - Save Bookmark in Memory
   '            : 2 - Retrieve Bookmark and make the record current
   '            : 3 - Initialize Bookmark List and ComboBox contents
   '-----------------------------------------------------------------
   Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
   Dim j As Integer, msg As String, bkmkchk As Variant
   Dim strRowSource As String, strRStmp As String, matchflag As Integer
   Dim msgButton As Integer
   
   On Error GoTo myBookMarks_Err
   
   If ActionCode < 1 Or ActionCode > 3 Then
      msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
      msg = msg & "Valid Values : 1,2 or 3"
      MsgBox msg, , "myBookMarks()"
      Exit Function
   End If
   
   Set actvForm = Screen.ActiveForm
   Set ctrlCombo = actvForm.Controls(cboBoxName)
   Select Case ActionCode
       Case 1
           bkmk = actvForm.Bookmark
           'check for existence of same bookmark in Array
           matchflag = -1
           For j = 1 To ArrayIndex
              matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
              If matchflag = 0 Then
                  Exit For
              End If
           Next
           If matchflag = 0 Then
              msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
              msg = msg & "Already Exists. "
              MsgBox msg, , "myBookMarks()"
              Exit Function
           End If
           'Save Bookmark in Array
           ArrayIndex = ArrayIndex + 1
           If ArrayIndex > ArrayRange Then
             ArrayIndex = ArrayRange
             MsgBox "Boookmark List Full. ", , "myBookMarks()"
             Exit Function
           End If
           bookmarklist(ArrayIndex) = bkmk
   
           GoSub FormatCombo
   
           ctrlCombo.RowSource = strRowSource
           ctrlCombo.Requery
       Case 2
           'Retrieve saved Bookmark and make the record current
           j = ctrlCombo.Value
           actvForm.Bookmark = bookmarklist(j)
       Case 3
           'Erase all Bookmarks from Array and
           'Delete the Combobox contents
           msg = "Erase Current Bookmark List...? "
           msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
           If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
               Exit Function
           End If
           For j = 1 To ArrayRange
              bookmarklist(j) = ""
           Next
           ctrlCombo.Value = Null
           ctrlCombo.RowSource = ""
           ArrayIndex = 0
   End Select
   
   myBookMarks_Exit:
   Exit Function
   
   FormatCombo:
   'format current Bookmark serial number
   'and OrderID to display in Combo Box
   strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
   strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)
   
   'get current combobox contents
   strRowSource = ctrlCombo.RowSource
   
   'Add the current Bookmark serial number
   'and OrderID to the List in Combo Box
   If Len(strRowSource) = 0 Then
        strRowSource = strRStmp
   Else
        strRowSource = strRowSource & ";" & strRStmp
   End If
   Return
   
   myBookMarks_Err:
   MsgBox Err.Description, , "myBookMarks()"
   Resume myBookMarks_Exit
   End Function

3. Import the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database:

   • Order Details
   • Products

   Note: We are not using the second table directly; the Order Details table references the Products table to display the product descriptions.

   Demo Form Design

   • Click on the Order Details table to select it.

   • From the Insert menu, choose Form, then select AutoForm: Tabular from the list.

   • MS Access will automatically create a tabular form.

   • Save the form with the name Order Details.

   A sample image of a Tabular Form in Design View is given below.

   

   Right-click to open Large Image in New Tab/Window.

4. Adding a Combo Box and Command Button

   1. Open the Order Details form in Design View.

   2. Expand the Form Header area and move the field headings down slightly to make room for a Combo Box and a Command Button, as shown in the sample design above.

   3. If the Toolbox is not visible, open it by selecting View → Toolbox.

   4. If the Control Wizards button on the Toolbox is currently selected, click it again to turn it off.

   5. Select the Combo Box tool from the Toolbox, then draw a Combo Box in the Header section of the form, following the layout shown earlier.

   6. With the Combo Box still selected, open its Property Sheet by choosing View → Properties.

5. Change the following Property Values as shown below:

   • Name = cboBMList
   • Row Source Type = Value List
   • Column Count = 2
   • Column Widths = .5";1"
   • Bound Column = 1
   • List Rows = 8
   • List Width = 1.5"

6. Change the Caption of the Child Label, attached to the Combo Box, to Bookmark List.

7. Create a Command Button on the right side of the Combo Box.

8. Display the Property Sheet of the Command Button.

9. Change the following Property Values as shown below:

   • Name = cmdReset
   • Caption = << Reset.

   Form Class Module VBA Code.

10. Display the Code Module of the Form (View -> Code).

11. Copy and Paste the following VBA Code into the Module; Save and Close the Form:

    Option Compare Database
    
    Private Sub Form_DblClick(Cancel As Integer)
        'Save Current Record's Bookmark in memory
        myBookMarks 1, "cboBMList", Me![OrderID]
    End Sub
    
    Private Sub cboBMList_Click()
        'Retrieve the bookmark using the
        'index number from the Combobox List
        'and make the respective record current
        myBookMarks 2, "cboBMList"
    End Sub
    
    Private Sub cmdReset_Click()
        'Initialize Bookmarks and Combobox contents
        myBookMarks 3, "cboBMList"
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)    
    'Remove all Bookmarks from Memory
        myBookMarks 3, "cboBMList"
    End Sub

    Perform a Trial Run

12. Open the Order Details Form in Normal View.

13. Double-click on one of the record selectors on the left side of the Form.

14. Click on the ComboBox Drop-down control to check whether the OrderID value of the Record that you have double-clicked is added to the ComboBox List with a sequence number in the first column or not.

15. Make a few more double-clicks on different Record selectors up or down in the form you like.

16. Inspect the Combo Box entries once again to ensure that all record references have been added correctly, each with a running serial number.

    Now, let’s test whether we can quickly navigate back to any of these previously visited records by selecting their reference from the saved Bookmarks List displayed in the Combo Box.

17. Click on the drop-down control of the Combo Box to display the list of Bookmarks and click on one of the items from the list.

    

    Right-click to open Large Image in New Tab/Window.

    The record corresponding to the selected Order ID will become the current record on the Form. Even if multiple records share the same OrderID, this method will correctly navigate to the exact record you visited earlier because it uses the Bookmark, not a search based solely on the OrderID value. If you relied only on the OrderID, the Form would stop at the first matching record instead of the specific one you previously accessed.

    Try it out by selecting other items from the Combo Box list. This method can also be implemented on Forms using a Columnar layout.

    

    Right-click to open Large Image in New Tab/Window.

    
    

    Important Points to Note.

    I would like to remind you that the list of Bookmarks is saved in the BookMarkList array within the myBookMarks() function in the Standard Module. The items in the ComboBox are populated from this array. We have dimensioned the array to hold 25 elements, though not all elements may be filled. The index number of each filled Bookmark in the array is stored in the first column of the ComboBox.

    Check the following declarations of the Function in the Global area of the Module:

    Public Const ArrayRange As Integer = 25
    Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer

    You can adjust the value 25 to a higher or lower number according to your specific needs. Alternatively, you can redimension the BookMarkList() array dynamically using the ReDim Preserve statement to increase its size while keeping the existing values intact.

    Let us continue by adding a few more distant record bookmarks to the existing list.

18. Scroll down the vertical scrollbar of the Form and double-click on the Record-Selectors for a few more records from the distant area of the Recordset.

19. Now, try to reach any of these Bookmarks we have added to the list by selecting them one by one from the Combo Box List.

    Isn't it very easy to revisit all those records a second time?

20. If you want to erase all those Bookmarks from the BookmarkList Array in memory, click on the << Reset Command Button. After this, you can create a fresh list of Bookmarks.

The OrderID Field Value added to the Combo Box, along with the Index Number of the Bookmark Array, can be used to cross-check with the retrieved record value to ensure correctness.

Review of VBA Code

Let us review the Sub-Routines we have copied into the Form Module and check what they are doing.

Private Sub Form_DblClick(Cancel As Integer)

    myBookMarks 1, "cboBMList", Me![OrderID]

End Sub

When you double-click the Record Selector of a record, the above Sub-Routine calls the main function myBookMarks() with the following parameters:

1. Action Code: 1 – indicates that the current Bookmark (a two-byte string containing displayable and non-displayable characters) should be fetched from the active Form and saved in memory in the BookMarkList array. The array index is incremented using the variable ArrayIndex. This Action Code is evaluated in the Select Case ... End Select block of the myBookMarks() function. Note that the Bookmark value itself is not displayed anywhere.

2. Combo Box Name: "cboBMList" – This combo Box displays the index number of the BookMarkList array. Providing the control’s name is sufficient to reference it on the active Form.

3. Record Field Value: OrderID – displays the record field value in the Combo Box alongside the BookmarkList array index. You can use any field from your table, as long as it helps verify that the correct record is retrieved using the Bookmark.

The third parameter of the myBookMarks() function is optional and is omitted when calling the function to retrieve a Bookmark or to clear the Bookmark list, as shown in the following three SubRoutines.

Private Sub cboBMList_Click()
myBookMarks 2, "cboBMList"

End Sub

Private Sub cmdReset_Click()
  myBookMarks 3, "cboBMList"

End Sub

' Erases the Bookmarks when the Form is closed

Private Sub Form_Unload(Cancel As Integer)

  'Remove all Bookmarks from Memory

  myBookMarks 3, "cboBMList"

  End Sub

NB: Since the main Function myBookMarks() references the Active Form, you can implement this method on any Form without directly passing any Form Name to the Function.

1. Saving, retrieving, and using Bookmarks for finding records is valid only for the current Form session.

2. Re-querying the Form's contents (not Refreshing) recreates the Bookmarks for the Recordset on the form, and saved Bookmarks may not be valid after that. You must create a fresh Bookmark List to use correctly.

3. This method will not work on Forms attached to external Data Sources, linked to Microsoft Access, which doesn't support bookmarks.

• Detail and Summary from the Same Report
• Hiding Report Lines Conditionally-3
• Hiding Report Lines Conditionally-2
• Hiding Report Lines Conditionally
• Network and Report Page Setup-3