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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
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")
        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.

Microsoft Access Macro window

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



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date