Recipe 9.4 Mark a Record on a Form and Return to It Later

9.4.1 Problem

Sometimes you are interrupted when you're editing a record on a form and need to move quickly to some other record. You'd like a way to save your place and easily return to it later. Is there an easy way to do this in Access?

9.4.2 Solution

Access forms have a Bookmark property that is similar to the bookmark you use when you put a book down but want to be able to quickly return to where you left off. This solution shows how to use VBA code to store the bookmark value of a particular record and return to it, presenting this functionality to your users with a toggle button. The solution also shows you how to add a custom shortcut menu to a control.

Follow these steps to add the ability to return to a designated record in your own forms:

1. Create a new bound form or open an existing form in design view. Add a toggle button (not a command button) control to the form's header or footer section. In the frmCustomer sample form, we named our button tglMark and added it to the header section.

2. Create an event procedure attached to the Click event of the toggle button. (If you're unsure of how to do this, see How Do I Create an Event Procedure? in the preface of this book.) Add the following code to the event procedure:

   Me.Private Sub tglMark_Click( )     ' If toggle button is depressed, then     ' mark this record; otherwise return     ' to previously saved record.          If Me.tglMark Then         Call acbHandleMarkReturn(msMark)     Else         Call acbHandleMarkReturn(msReturn)     End If End Sub

3. Add the following code to the declarations area at the top of the code associated with your new form. This enumeration supplies all the possible values of the state of the marked row:

   Public Enum MarkState     msMark = 1     msReturn = 2     msDiscard = 3 End Enum

4. Add the following public function to the form's module:

   Public Function acbHandleMarkReturn(msAction As MarkState)     Static svarPlaceHolder As Variant     Select Case msAction         Case msMark             ' Mark record position             svarPlaceHolder = Me.Bookmark             Me.tglMark.Caption = "Return to Saved Place"         Case msReturn              ' Return to marked position             Me.Bookmark = svarPlaceHolder             svarPlaceHolder = Empty             Me.tglMark.Caption = "Save Place"         Case msDiscard              ' Reset marked position              ' and unpress button             svarPlaceHolder = Empty             Me.tglMark.Caption = "Save Place"             Me.tglMark.Value = False         Case Else             ' Shouldn't happen             MsgBox "Unexpected value for intAction", _              vbCritical + vbOKOnly, "acbHandleMarkReturn"     End Select End Function

5. To create a shortcut menu, select View Toolbars Customize, make sure you are on the Toolbars tab in the dialog, and click the New button. Name your new pop-up menu popAbandon, as shown in Figure 9-11, and click OK. Note that this dialog is used for creating both toolbars and menus.

Figure 9-11. Creating a shortcut menu from the Toolbars tab of the Customize dialog

6. Click the Properties button in the Customize dialog, and select Popup for the type property. Click OK in the message box that appears, and click Close in the Toolbar Properties dialog.

7. Scroll down in the list of toolbars in the Customize dialog, and check Shortcut Menus. A special menu bar appears showing you all the shortcut menus. Click the down arrow next to Custom, the last menu item, and then click the right arrow next to popAbandon. This displays your blank shortcut menu as a small gray box.

8. In the Customize dialog, select the second tab, Commands. With the File category selected in the list on the left, click on Custom in the list of commands on the right, and drag a custom command over to your blank popAbandon menu, as shown in Figure 9-12.

Figure 9-12. Adding a custom item to a shortcut menu

9. Right-click on the new Custom item you created in your shortcut menu, and select Properties. In the Properties dialog, change the caption to &Abandon Saved Place. For the OnAction property, enter "=acbAbandonBookmark". Click the Close button in the Properties dialog, and click the Close button in the Customize dialog.

10. In the VBA editor, insert a new module and add this public function:

    Public Function acbAbandonBookmark( )   Call Form_frmCustomer.acbHandleMarkReturn(3) End Function

11. In the form, set the ShortcutMenuBar property of the tblMark button to popAbandon.

12. Save the form and verify that it works correctly. Mark a record to return to, move to another record, right-click on the button, and your pop-up menu will enable you to abandon the place you had saved.

To see how this works, load the 09-04.MDB database and open the frmCustomer form, which contains 500 customer records. Navigate to a record and begin to make a change to it. For example, in Figure 9-13, we made some edits to Margaret Woods's record before marking it. Click on the Save Place toggle button in the form's header to mark the current record and save your place in the recordset. The toggle button will remain depressed and its caption will change to Return to Saved Place (see Figure 9-14). Now navigate to some other record. Click on the toggle button again, and you will return instantly to the earlier "marked" record.

Figure 9-13. The frmCustomer form before marking the current record

Figure 9-14. The frmCustomer form after marking the current record

Mark the record again and navigate to yet another record. Perhaps this time you have changed your mind and wish to abandon the earlier marked record in favor of the current one. However, if you press the toggle button a second time, you will return to the previously marked record, losing your new place. You can remedy this situation by right-clicking while the mouse cursor is over the toggle button control. A shortcut menu giving you the option to abandon the previously marked record will appear (see Figure 9-15). Select this option, and you'll now be able to mark the current record instead.

Figure 9-15. The toggle button's shortcut (right-click) menu

9.4.3 Discussion

The mark-and-return facility built into the frmCustomer form has several interesting user interface aspects. First, the toggle button is the main user interface element. This control type is ideally suited for this situation because it is able to store binary state information that visually matches the two states you wish to represent (mark and return). Second, the shortcut menu, although a little less easily discovered than the toggle button, allows you to offer the extra "abandon" functionality without taking up a lot of screen space.

The actual code that implements the mark-and-return facility is small, and basically revolves around grabbing the form's Bookmark property and storing it between calls to the acbHandleMarkReturn function. This is handled by the Select Case statement in acbHandleMarkReturn:

Public Function acbHandleMarkReturn(msAction As MarkState)     Static svarPlaceHolder As Variant     Select Case msAction         Case msMark             ' Mark record position             svarPlaceHolder = Me.Bookmark             Me.tglMark.Caption = "Return to Saved Place"         Case msReturn              ' Return to marked position             Me.Bookmark = svarPlaceHolder             svarPlaceHolder = Empty             Me.tglMark.Caption = "Save Place"         Case msDiscard              ' Reset marked position              ' and unpress button             svarPlaceHolder = Empty             Me.tglMark.Caption = "Save Place"             Me.tglMark.Value = False         Case Else             ' Shouldn't happen             MsgBox "Unexpected value for intAction", _              vbCritical + vbOKOnly, "acbHandleMarkReturn"     End Select End FunctionMe.

The msMark enumerated value case is executed when the user depresses the toggle button, so the code stores away the bookmark in the svarPlaceHolder static variable and changes the caption to indicate the new state of the button. Notice that we used a static variable rather than a module-level global variable. A static variable is a better choice in this situation because we are changing the value of the variable only within this one function.

When called with the msReturn value, the code sets the form's bookmark to the previously stored value, clears svarPlaceHolder, and resets the caption to the default.

Finally, when called with the msDiscard constant value, the code clears svarPlaceHolder, resets the caption, and sets the Value property of the toggle button control to False. This causes the toggle button to reset itself to the unpressed state, which is necessary because the function was called from the shortcut menu macro without toggling the button.

We made the acbHandleMarkReturn function public because we needed to call it from the shortcut menu. However, you can only call public functions that are in standard modules from toolbar buttons or menu items, which is why we needed the additional acbAbandonBookmark function to call the function that is in the form. Note the syntax that acbAbandonBookmark uses to call the public acbHandleMarkReturn function in the form, passing in the value msDiscard to specify that the bookmark should be abandoned:

Public Function acbAbandonBookmark( )   Call Form_frmCustomer.acbHandleMarkReturn(msDiscard) End Function

An alternate way to offer this functionality the ability to browse other records and return to a previous record is to create multiple instances of the same form. This method was demonstrated in the Solution in Recipe 2.11.

Bookmarks

A bookmark is an array of bytes that points to the current record of an open recordset (or in the case of the form's bookmark, the current record of a form's recordset). Bookmarks make sense only within the lifetime of the currently open recordset (or form). If you requery or close and rerun the query or form, the set of bookmarks will be different. A bookmark is not a record number; it's a dynamically created handle (or pointer) to the current record. To store bookmarks, you can use a variable of type Variant.