Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 19, 2004

Make Access Command Buttons Work Harder - Page 2

By Danny Lesandrini

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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM