Make Access Command Buttons Work Harder - Page 2

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")
        objPopup.ShowPopup
        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.

Conclusion

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