October 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

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.

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 Function

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.
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.
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 Sub

Perform 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.
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.
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 Sub

When 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 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.
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.
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.

Option Compare Database Dim actForm As String, actControl As String, idletime As Integer Dim oldForm As String, oldControl As String, imageNo As Integer Dim s_path As String Private Sub Form_Activate() Me.TimerInterval = 1000 End Sub Private Sub Form_Deactivate() Me.TimerInterval = 0 Me.ImageFrame.Visible = False End Sub Private Sub Form_Load() DoCmd.Restore idletime = 0 imageNo = 0 s_path = CurrentProject.Path Me.ImageFrame.Visible = False Me.TimerInterval = 1000 End Sub Private Sub Form_Timer() Dim txt As String txt = "Slide-Show Starts In: " actForm = Screen.ActiveForm.Name actControl = Screen.ActiveForm.ActiveControl.Name If actForm = oldForm And actControl = oldControl And actForm = "SlideShow" Then idletime = idletime + 1 If idletime > 15 And idletime < 31 Then Me.Lblup.Visible = True Me.Lblup.caption = txt & (30 - idletime) & " Seconds." Else Me.Lblup.Visible = False End If Else oldForm = actForm oldControl = actControl idletime = 0 Me.ImageFrame.Visible = False Me.Employees.Visible = False DoEvents End If If idletime > 30 Then Me.Lblup.Visible = False DoEvents If idletime Mod 5 = 1 Then Me.ImageFrame.Visible = True Me.Employees.Visible = True imageNo = imageNo + 1 imageNo = IIf(imageNo < 1 Or imageNo > 9, 1, imageNo) Me.ImageFrame.Picture = s_path & "\" & imageNo & ".bmp" DoEvents End If End If End Sub