Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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
functionalitythe ability to browse other records and return to
a previous recordis to create multiple instances of the same
form. This method was demonstrated in the Solution in Recipe 2.11.