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?
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:
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.
Create an event procedure attached to the Click event of the toggle button. (If you’re unsure of how to do this, see Section P.5.5 in the the preface of this book.) Add the following code to the event procedure:
Private Sub tglMark_Click( ) ' If the toggle button is depressed, mark this record; ' otherwise, return to the previously saved record. If Me!tglMark Then Call acbHandleMarkReturn(conMark) Else Call acbHandleMarkReturn(conReturn) End If End Sub
Add the following constants to the declarations section of the form’s module (if the form’s module is not currently displayed, select View → Code from the design view of the form):
Private Const conMark = 1 Private Const conReturn = 2 Private Const conDiscard = 3
Add the following public function to the form’s module:
Public Function acbHandleMarkReturn(intAction As Integer) Static svarPlaceHolder As Variant With Me!tglMark Select Case intAction Case conMark ' Mark record position svarPlaceHolder = Me.Bookmark .Caption = "Return to Saved Place" Case conReturn ' Return to marked position Me.Bookmark = svarPlaceHolder svarPlaceHolder = Empty .Caption = "Save Place" Case conDiscard ' Reset marked position and unpress button svarPlaceHolder = Empty .Caption = "Save Place" .Value = False Case Else ' Shouldn't happen MsgBox "Unexpected value for intAction", _ vbCritical + vbOKOnly, "acbHandleMarkReturn" End Select End With End Function
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.
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.
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.
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.
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.
In the VBA editor, insert a new module and add this public function:
Public Function acbAbandonBookmark( ) Call Form_frmCustomer.acbHandleMarkReturn(3) End Function
In the form, set the ShortcutMenuBar property of the tblMark button to popAbandon.
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.
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.
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(intAction As Integer) Static svarPlaceHolder As Variant With Me!tglMark Select Case intAction Case conMark ' Mark record position svarPlaceHolder = Me.Bookmark .Caption = "Return to Saved Place" Case conReturn ' Return to marked position Me.Bookmark = svarPlaceHolder svarPlaceHolder = Empty .Caption = "Save Place" Case conDiscard ' Reset marked position and unpress button svarPlaceHolder = Empty .Caption = "Save Place" .Value = False Case Else ' Shouldn't happen MsgBox "Unexpected value for intAction", _ vbCritical + vbOKOnly, "acbHandleMarkReturn" End Select End With End Function
The conMark
constant (conMark
=
1
) 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 conReturn
constant value
(conReturn
=
2
), 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 conDiscard
constant
value (conReturn
=
3
), 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
call public functions that are in standard modules only 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 3
to specify that the
bookmark should be abandoned:
Public Function acbAbandonBookmark( ) Call Form_frmCustomer.acbHandleMarkReturn(3) 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 Section 2.11.2 .
18.223.106.100