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 disappear completely 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. We will cover this topic 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 careful look (this
figure is like one of those children’s games where you have to spot the
difference). 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 databases of that
vintage.
This will bring my menus into your database. Now here are
the instructions for adding them to your form.
1. Open the Form that you want to add the menu to, 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 mean 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 the 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 the menu chapter of my
book on Protection and Security 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 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 of and convert it to run
in Access 2007.
3. Do not stop when things get hard, solve all the problems.
4. If you don’t 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. In addition, if you want to save a few dollars,
only purchase Access 2007 by itself, the other programs can always wait until
later.
Download rightclickmenu.mdb.
»
See All Articles by Columnist Garry Robinson
