Setting Up Right Click Menus for Access 2007

by  Garry Robinson

Summary

This article shows you how to apply and even create right click (Short Cut) Menus in Access 2007 even though Toolbars and Menus are not obvious in the user interface.

What is the story with menus and toolbars in Access 2007?

In Access 2007, most developers will be aware that the ribbon menus and the navigation bar dominate the user interface.  Whether you think they are a good idea or not, they are here to stay and Access developers need to make the most of the new environment.

Now in the new Access environment, one of the things that seemed to completely disappear was Toolbars and the old fashioned Custom Menus.  Well that is true almost; custom menus are available in the hard to find Add-Ins Ribbon tab and Toolbars that appeared at the top of the Access 2003 databases are completely gone.

What has been retained in the user interface in full in Access 2007 though is right click menus. Microsoft sometimes refers to them in the help guides as ShortCut menus. It is this topic that we will cover in detail in this article.

What is a right click menu and why bother having custom versions in your database?

If you look at Figure 1 you will see two forms where I have right clicked on the form to bring up the menu. Have a look carefully (this figure is like one of those children's games where you have to spot the difference) and you will see that the second menu does not have the Form Design icon. For developers, this is good because it means your design is safer and for users it is good because there is less to distract them whilst they are using the application. The custom right click menu also doesn't have the Pivot views or the Copy and Paste options but it does have some Filter options that should be easier to use than the Ribbon filtering commands.

Figure 1 - One Form with the built-in right click menu and the second with a custom right click menu

As you are starting to understand, customised right click menus in forms are pretty useful but there is more (free steak knives I hear you say). In Figure 2,  I have added a custom right click menu to a report so that you can see the difference between the standard right click menu and a custom menu that is simpler to use.

Figure 2 - One report with the built-in right click menu and the second with a simpler custom right click menu

Change Forms and Reports over to Garry's Custom Menus

The instructions for creating custom menus in Access 2007 (FOOTNOTE 1) are a little long winded and you will end up with menus that do not have icons next to them. So to give you an easier alternative, I will show you how to import the custom Right click menus that I demonstrated in Figures 1 and 2.

1. In Access 2007, in the External tab in the Ribbon, choose the Access button.
2. Find the sample database that comes with this article called rightclickmenu.mdb
3. Choose the Import Tables, Queries, Forms etc option and press OK.
4. You don't need to select any Objects, you just need to click on the Options button
5. Now select the Menus and Toolbars check box and press OK

Figure 3 - How to import custom right click Menus

Footnote 1: You can use the Import command in earlier versions of Access to import right click menus into other Access database of that vintage.

This will bring my menus into your database. Now here are the instructions for add them to your form.

1. Open the Form that you want to add the menu too in design mode.
2. Choose the Design tab in the Ribbon and make sure that the property sheet is turned on (see Figure 4).
3. View the Other tab in the property dialog and make sure that Shortcut menu is set to Yes
4. In the Shortcut menu bar property, choose mnuFormPopup for Forms or mnureportPopup for reports.

Figure 4 - The Other tab in the property dialog and the options that you need to select.

Note: If you don't want to see any right click menus on a form, set the Shortcut Menu property to No.

Grow your own menu in Access 2007

Now I guess that you are probably already thinking, how can I edit Garry's menu, well in Access 2007, this CANNOT be done. If you hunt around the Ribbons there are no options for editing menus. My preference is to edit them in Access 2003 but if you don't have Access 2003 or earlier, then you are stuck. If you do have those versions of Access, I recommend you think about setting up your preferred right click menus in any database and import them into your database and they will be in a better state for deployment into Access 2007. 

So does this almost means that you are stumped if you only have Access 2007? Not quite as you can recreate the menu from scratch using specially setup Access macros. This process is described in some detail in the Access 2007 help and you can find the article by searching the help for "Create custom menus". In simple words and pictures, here is what you need to do

1. Create a new Macro like the one I have illustrated in Figure 5.  The Macro name column is what appears on the menu (see Figure 7).  The Condition column will generally be blank.  The Action column is what is going to happen when the users presses the menu item.
2. Now create a second macro that is going to run the first macro as shown in Figure 6. This menu will generally only have the AddMenu action and a reference to the first menu item
3. Open the form in design view, choose the Other property and add a macro line with name of the second macro to the Shortcut Menu property. In this case that will be mcrRightClick. That's it, your form will now have a menu item like that in Figure 7.

The one drawback with this approach is that the right click menu that you have created will not have any pictures on it.  Personally, I like doing the menus in older versions of Access because they look better.

Figure 5 - This is how you setup the the first of two Macros you need to create a right click menu

Figure 6 - The second (overriding) macro that makes the macro appear like a right click menu.

Figure 7 - A form with the custom menu illustrated in Figures 5 and 6.

 

How To Build Menus in Access 2003 or earlier

Building right click menus in earlier versions of Access is tricky. It took me a few pages of pictures and text in menu chapter of my book on Protection and Securing your Database to describe how to do it. To get started, Customize Toolbars and select Shortcut Menus. 

Conclusion 

That is how you can create Access Right or Shortcut menus in Access 2007. I am sure after reading this that you can see that it is worth investigating right click menus as it will simplify and protect the database that your users work in.

Access 2007 Readiness ~ Get ready for the Economic Upturn

If you are in the unfortunate position of not having much paid work on in this global economic downturn, learning about Access 2007 is not a bad thing to do with your spare time.  So here is what I suggest you do.

1. On a computer that doesn't have any significant Access applications, download the free trial of Access 2007 and install it on your computer.  Be careful not to override any existing versions of Access by reading all the information in the setup package and using the advanced install options.
2. Identify the database that you most proud off and convert it to run in Access 2007.
3. Do not stop when things get hard, solve all the problems.
4. If you dont like the Ribbons or Navigation pane, keep researching them until you do like them. They are not so bad, especially if you turn on Search in the navigation pane.
5. If you are happy at the end of the trial period, purchase the product and learn more.  Also if you want to save a few dollars, only purchase Access 2007 by itself, the other programs can always wait till later.

 Good luck to you in your job hunting endeavours.

Garry Robinson from Sydney, Australia

Author Bio.


Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies popular web site at  http://www.vb123.com/  or sign up for his Access email newsletter by sending a blank email here.   The web site features many Access resource sand software  that are used by  more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at  … www.gr-fx.com

 

  Your Sample Database Is Called   "rightclickmenu.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

Internal Links

Building a Shortcut (Right Click) Menu in Access 2003

External Links

 

You may want to read about ribbons at Microsoft here