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.
Click for larger image
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
THE WARNING CAREFULLY!!!
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.
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.
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.
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.