Make Access Command Buttons Work Harder

The other day I was working on an existing Access database application when
I realized that there were simply too many command buttons for the allotted
real estate.  The form was already packed with controls and the client required
additional functionality.  There was no way I was going to fit it all
without some kind of clever adaptation, so I reached into my bag-of-tricks and
I found my old Popup Magic Button.

The Popup Magic Button is simple to deploy and it gives you the ability to
group similar functions on a single command button.  As shown in Figure 1 below, the button click
opens a popup menu on which you include all the functionality you require.

The code for the Popup Magic Button is included in the download associated with this article and in addition
to showing you how to make this button work, you also get code to accomplish
the following:

  • Determine the current database file’s path.

  • Output a table to HTML, Excel and Text.

  • Use the Shell() function to launch the newly created export file.

  • How to open a report to a specific record.

The following article will focus on the button logic, as well as providing a
comprehensive lesson on how to create and maintain shortcut menus in Microsoft
Access.

Step 1:  Create a menu

If you are the least bit new to Access, the process for creating
a popup menu (shortcut menu) might prove a little confusing. 
Accordingly, Figure 2 
illustrates the four steps to the process, which are …

1.  Right
click the menu bar and choose Customize to open the Menu Customize dialog box

2.  Click
New to create a new menu

3.  Supply
a meaningful name for your menu and click Ok to save it.

4.  Find
your menu in the list, select it and click Properties

5.  Change
its Type property to Popup

6.  READ
THE WARNING CAREFULLY!!!
 

Create a new shortcut menu
Figure 2

The last step of the process is very important.  Once you change the type
of your menu to Popup, it no longer shows up in the menu list.  It is now
part of what is called the Shortcut Menus toolbar and you must select Shortcut
Menus to view it.  This is not terribly intuitive but once you are
familiar with the paradigm, it is easy enough to use.

Shortcut menu warning

That was a lot of work and all we really have at this point is an empty
popup menu. Now we need to add items to it, but this is the fun and creative
part.  In my sample code, I loaded my menu with reports and macros, but
you can drag and drop ANY menu item from the Commands tab of the menu Customize
dialog box.  This means that anything that Access exposes in any menu
could be added to your Popup Magic Button.

First, you have to find your new menu.  Remember that it is now
carefully hidden on the Shortcuts Menu, so begin by checking the box for
Shortcut Menus in the menu Customize dialog box.  Look to the far right of
the now visible menu bar and click on the word Custom to reveal your custom
popup menus.  Now that you have found it, it is time to put it to work.

View shortcut menu

To add an item to your new, empty menu, simply select a category on the
left, select the desired object from the right pane and drag it to your
menu.  Once it is on your popup menu, right click the item to bring up the
properties menu.  You will probably want to change the name of the item to
something friendly, as I have in my example, changing from the report name of
rptBookSales002 to simply Book Sales 2.  The property options are mostly
self-explanatory and will be modified according to your personal taste. 

The property options include a number of button images as shown
below. One not so obvious thing to note is the Copy Button Image
option.  This allows you to reuse a menu button image from any of the
other Access menus.  Just find the image you want, right click and select
Copy Button Image, return to your menu item and select Paste Button
Image. 

Add items to your new menu

You may also create groups by inserting a dividing line between menu
items.  I also played with the Assign Hyperlink option, setting the path
to my web site.  The function of that button is an innocuous Copy command
and after performing a Ctl-C it jumps you to my web site.  You might also
want to click on the Properties option to view a Properties dialog box where
you are given the opportunity to set even more attributes of the menu.

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles