Make Access Command Buttons Work Harder

Friday Nov 19th 2004 by Danny Lesandrini

Danny Lesandrini reaches into his bag of tricks and pulls out a Popup Magic Button. Follow along as he explains how to group similar functions on a single command button to open a popup menu, which includes all the functionality you require.

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
Pop-Up Magic Button in action

Figure 1

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


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.

Step 2:  Code the button

The code for animating the menu is quite simple.  There is, however, one little trick.  You must declare a public variable, of type object, to which you assign the command bar object.  The declaration is as simple as this ...

Public objPopup As Object

 Once this variable has been declared, enter the following code on the Click event of your command button:

Private Sub cmdReports_Click()
    On Error Resume Next
        Set objPopup = CommandBars("mnuReports")
        Set objPopup = Nothing
    End Sub

That is all there is to the code.  The real work is in creating the menus, and the objects that are called by the menus.  The next step discusses some of the things you can do, and some things you cannot do.

Step 3:  Get fancy

I may lose my guru license for this, but I am going to suggest, nay even recommend, that you code this next part using Macros.  I know, they are wicked and evil, but it is the only way I could get the popup menu to launch a piece of code.  You should, of course, write the code using a Function in a standard module, but once it is working correctly, you need to create a macro that launches it.

 For example, my code includes a function to output the contents of a table to text and open it with Notepad.  The function looks like this:

    Public Function ExportTableToText(ByVal sTable As String) As Boolean
    On Error Resume Next
        Dim sFile As String, sThisMDB As String, sOpen As String
        Const q As String * 1 = """"
        sThisMDB = CurrentDb.Name
        sFile = Left(sThisMDB, InStrRev(sThisMDB, "\")) & sTable & ".txt"
        If Dir(sFile) <> "" Then Kill sFile
        DoCmd.TransferText acExportDelim, , sTable, sFile, True
        sOpen = "notepad.exe " & q & sFile & q
        Shell sOpen, vbNormalFocus
    End Function

That code works great, but to launch it from the popup menu, it will have to be put into a new macro.  The following image shows the three functions that are available from the Functions button on my sample application.  I simply named the macro, set its action to RunCode and supplied the name and arguments of the public function described above.  Once the macro is saved, it becomes available to the popup menu from the All Macros category.

Microsoft Access Macro window


The first screen shot of this article shows it all:  many functions available from one button click.  There are times you will implement this because you need the real estate, other times just because it's slick.  In fact, as I wrote this article it struck me how many different things you could do with this method and I am certain I have overlooked some really valuable implementation.  If you think of something I missed, drop me a note.  In addition, if you figure out how to execute a function directly from a menu option without using a macro, I would love to hear how you did it.

» See All Articles by Columnist Danny J. Lesandrini

Mobile Site | Full Site