October 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

You want to edit information on 25 records on the Form randomly. You will open the main data editing Form. Search for the record by entering the Key Value to find (say Employee Code or OrderID Value) in the Find (Ctrl+F) control and edit the record when found on the Form. This procedure is repeated for all 25 records for the first time because you don't have any other choice but to find and edit the information.

But, the information you have changed is very critical and any mistakes in them may lead to serious issues. Mistakes can easily creep in when you go through the finding and editing procedure in a hurry. It is important that you should have a second look at each record to verify and ensure the accuracy of changes.

Going through the same procedure to find all those 25 records again by displaying the Find control, keying in the Key Values, and clicking on the Find button to reach the required record is not as enjoyable as it did for the first time.

But, if you can reach all those distant records one by one, in the same order of editing, without going through the above cumbersome procedure, then it will be a great relief, to finish the work faster.

Why I said in the same order during editing because you are holding the source document changes in the same order of your first visit to the records.

We will develop a trick with the Form's Bookmarks to make this kind of work easier for our Application Users.

FORM BOOKMARKS.

When you open a Form attached to a Table, Query, or SQL Statement each record on the Form is marked by MS-Access with a unique identifying tag known as Bookmark (a two Byte string Value). This is happening every time you open the Form with the above record sources. The Bookmarks are valid only in the current session of the Form and not stored in Tables.

We can read the Bookmark of any record from the Form's Bookmark Property, when the Record is Current on the Form, and store it in Variables in memory. Bookmarks which saved this way can be used again to go back quickly to the same record we visited earlier.

I have created a Function with the name myBookMarks() for this purpose and you can implement this method on any Form that has a Recordset attached to it by creating a Combo Box and a Command Button and four lines of code in the VBA Code Module of the Form to run the Function.

You will definitely get a pat on the back from the User of your MS-Access Application for implementing this simple feature. So, let us start 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
NB: We are not using the second table directly, but the Order Details table references the Products Table for its Product Description.

Demo Form Design
Click on the Order Details table to select it.
Select Form from Insert Menu and select AutoForm: Tabular from the displayed list.
MS-Access creates a Tabular Form quickly. Save the Form with the name Order Details.
A sample image of a Tabular Form in Design View is given below.
Open Order Details Form in Design View.
Expand the Form Header area and move the Field Headings down for enough space to create a Combo Box and a Command Button as shown on the sample design above.
Display the ToolBox (View - ->ToolBox), if it is not already visible.
If the Control Wizards Tool Button is already in the selected state, then click on it to de-select it.
Select the Combo Box Tool from the ToolBox and draw a Combo Box in the Header Section of the Form as shown on the design above.
While the Combo Box is still in the selected state; select Properties from the View menu to display the Property Sheet of the Combo Box.
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 into the Combo Box List with a sequence number in the first column or not.
Make a few more double-click on different Record selectors up or down in the form you like.
Check the Combo Box contents again to ensure that all these record references are added to the Combo Box with running serial numbers.

Now, let us check whether we can jump quickly to one of these records visited earlier by using the saved Bookmarks List appearing 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 selected Order Id record will become the Current Record on the Form. Even if there are several records with the same OrderID it will correctly pick the record that you visited earlier because we are using Bookmark and not the Find method with the OrderID Value to find the record. If OrderID was used, then it will stop at the first record with the same OrderID numbers, not on the same record you visited earlier.
You may try out other items appearing on the list for now. You may implement this method on Forms with Column Format too.

Important Points to Note.
Here, I would like to remind you that we are saving the List of Bookmarks in the BookMarkList Array in myBookMarks() Function in the Standard Module. The ComboBox list items are added from the bookmark list from the Array. We have dimensioned the Array to hold a total of 25 elements and not all of them are filled in. The index number of the array with bookmarks filled in is added 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 change the value 25 to a higher or lower desired value according to your specific needs.
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 look at 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 on the Record Selector of a record the above Sub-Routine calls the main Function myBookmarks() with the following parameters:

Action Code: 1 - indicates to fetch the Current Bookmark (a two-byte string value consists of displayable/non-displayable characters) from the Active Form and save it in Memory in BookMarkList Array after incrementing the Array index number in Variable ArrayIndex. The Action Code is tested in the Select Case. . .End Select segment in the myBookMarks() Function. The Bookmark value itself is not displayed anywhere.

Combo Box Name: "cboBMList" - to display the Index Number of the BookmarkList Array in the Combo Box. The Name of the Control is enough to reference it on the Active Form.

Record Field Value: OrderID - to display the Record Field Value in the Combo Box along with the BookMarkList Array Index number. You can use any Field Value of your Table so far as it serves the purpose of checking the correctness of the record retrieved using the Bookmark.

The third parameter of myBookMarks() Function is defined as Optional and is omitted while calling the Functions to retrieve the Bookmark or to erase the Bookmarks List in the following three Sub-Routines respectively:

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) re-creates the Bookmarks for the Recordset on the form and earlier 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