Access 2007 Data-Driven Context Menu Macro


  • I have created a context menu for my Access form, using this article:

    Create custom menus and shortcut menus by using macros

    This menu needs some sub-menus, which the above article explains quite well.  Unfortunately, those sub-menus, being macros themselves, need to be pre-defined.  This does not easily fit into my application, because the choices in those sub-menus are based on the items in a database table.

    For example, the main context menu item is "Print Report".  The sub-menu items for that menu item may be "Report 1", "Report 2", etc.  I have a table in my database containing the available reports.  For the first version of my menus, I've hard-coded the report names into the "Reports" macro.

    What I would like is to have that "Reports" macro sensitive to the items in the Reports table.  My intent is to read the table in my startup code, and re-build the macro from the table contents.  I've searched, but can't find any example code to create or modify a macro.

    Can anybody suggest code to either modify an existing macro or delete and recreate the macro from scratch using VBA?


    Ron Mittelman

    Thursday, August 01, 2013 11:40 PM


All replies

  • Here are some promising links:

    There are ways to create a menu out of thin air using Windows API calls, but I hope you won't need to resort to that.

    -Tom. Microsoft Access MVP

    • Marked as answer by RMittelman Friday, August 02, 2013 10:15 PM
    Friday, August 02, 2013 3:37 AM
  • Thanks, Tom.  I have seen these articles.  I was going to use that technique when I stumbled on the article about doing it using macros and macro groups.  Now I have 4 macros:

    The macro group which contains some menu options, including 2 for sub-menus.
    The macro for the first sub-menu.
    The macro for the second sub-menu.
    The macro that adds the menu to the form itself.

    These all work well, except they are not data-sensitive, they are pre-defined.  My intent was for the 2 middle macros to be data-sensitive, so they would contain items matching my data records.

    I was really hoping to find some code examples which I could use to construct those 2 macro objects on-the-fly.  There must be some object model for macros that I could use to construct them in VBA, right?

    Or am I missing the point?  I'm assuming that the type of menu you create using CommandBar objects is different than the type you construct using macros.  Isn't a CommandBar object stored in the database (as mentioned by those articles) different than a macro?

    I may just need to re-architect and not use macros at all.  That way I would have the flexibility I need to data-drive the menus, right?

    Ron Mittelman

    Friday, August 02, 2013 3:04 PM
  • Hey Tom,

    Further research seems to indicate that the VBA method in those articles should work as well as the menu macro method, with the added benefit of having menu icons available.  In VBA, I can iterate my table and build the sub-menus dynamically, so when I attach the main menu, the sub-menus will be up-to-date.  Thanks.

    Unfortunately, I have a much bigger problem with this entire popup menu idea, as outlined in this thread.
    It seems there is a context issue when you run code from a popup menu as opposed to running from a control on the form.  When running from the popup menu, when I hit the line of code which calls a method on the sub-form (ex: Me.SubformControl.Form.AddSubRecord() ), I get an error trying to resolve the ".Form" object (err 2455: You entered an expression that has an invalid reference to the property Form/Report.).

    This error does NOT happen if I invoke the code from a command button.  I don't know if taking the trouble to build my popup menu from VBA CommandBar object will make a difference or not.


    Ron Mittelman

    Friday, August 02, 2013 5:49 PM
  • Final update:

    I cannot find a way to prevent the issue outlined in my prior reply.  So, I've abandoned trying to use a context menu to control operations.  I am going back to command buttons.

    Having said that, I've still used Tom's answer to create a popup menu that appears when 2 of the buttons (which cause 2 reports to be created) are clicked.  I needed a way to have the user specify which version of the documents to print (out of several possible versions).  I was using a ComboBox bound to my reports table, and then warning the user if he didn't choose an item in the ComboBox when he clicked one of the 2 buttons.

    Now, either button causes a popup menu to appear, containing the several document type choices.  These were loaded at startup using the technique Tom mentioned.  The popup menu is a Temporary menu that gets loaded each time the application runs.

    Thanks Tom, credit is yours!

    Ron Mittelman

    Friday, August 02, 2013 10:15 PM
  • I think going for command buttons or ribbon buttons is a good idea. I have always been ambivalent about context menus since they require an action (right-click) before you can see what's available.

    -Tom. Microsoft Access MVP

    Saturday, August 03, 2013 3:08 AM