Form Bookmarks and Data Editing.
Assume that you are about to edit information on 25 records in the Form, selected at random by searching for their ID keys. 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.
Open your VBA Editing Window (Alt+F11).
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 FunctionImport 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.
Adding a Combo Box and Command Button.
-
Open the Order Details form in Design View.
-
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.
-
If the Toolbox is not visible, open it by selecting View → Toolbox.
-
If the Control Wizards button on the Toolbox is currently selected, click it again to turn it off.
-
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.
-
With the Combo Box still selected, open its Property Sheet by choosing View → Properties.
-
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"
Change the Caption of the Child Label, attached to the Combo Box, to Bookmark List.
Create a Command Button on the right side of the Combo Box.
Display the Property Sheet of the Command Button.
Change the following Property Values as shown below:
- Name = cmdReset
- Caption = << Reset.
Form Class Module VBA Code.
Display the Code Module of the Form (View -> Code).
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 SubPerform a Trial Run.
Open the Order Details Form in Normal View.
Double-click on one of the record selectors on the left side of the Form.
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.
Make a few more double-clicks on different Record selectors up or down in the form you like.
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.
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.
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.
Important Points to Note.
I would like to remind you that the list of Bookmarks is saved in the
BookMarkListarray within themyBookMarks()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 IntegerYou 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 theReDim Preservestatement 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.
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.
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?
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 SubWhen you double-click the Record Selector of a record, the above Sub-Routine calls the main function myBookMarks() with the following parameters:
-
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 theBookMarkListarray. The array index is incremented using the variableArrayIndex. This Action Code is evaluated in theSelect Case ... End Selectblock of themyBookMarks()function. Note that the Bookmark value itself is not displayed anywhere. -
Combo Box Name:
"cboBMList"– This combo Box displays the index number of theBookMarkListarray. Providing the control’s name is sufficient to reference it on the active Form. -
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.
Saving, retrieving, and using Bookmarks for finding records is valid only for the current Form session.
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.
This method will not work on Forms attached to external Data Sources, linked to Microsoft Access, which doesn't support bookmarks.
Download SlideShow.zip
