The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Menu Customization, Part 1


The last time I bought a suit, I went to the local men's clothes shop, got a suit off the rack, had the local tailor make some adjustments, and my suit was ready to wear. The time before that, I did pretty much the same thing. As a matter fact, with every suit I have ever bought I have gone through the same drill.

But there is a different way to buy a suit, it's called a bespoke suit. For a bespoke suit, you select your fabric, a talented tailor takes your measurements, asks your preference of styles, and creates a suit specifically designed to your tastes and for your body. The advantages and disadvantages of both of these approaches are fairly clear. The off-the-rack suit is fast, easy, and relatively inexpensive. The bespoke suit takes a lot more time and a lot more money, but it is exactly the suit that you want tailored to be an exact fit for you.

With Access 2007/2010, we have a ribbon instead of the menu. The ribbon is the equivalent of the off-the-rack suit. You can make some changes and they are not terribly difficult to make, but after you have completed your adjustments, the ribbon may not be exactly what you want, nor may it be the best fit for your needs. However, it is relatively painless to make these changes and it doesn't take a whole lot of time and energy. If you want to build a customized menu in Access 2007 or 2010, the equivalent of a bespoke suit, you have to build from the ground up using a language known as Extensible Markup Language (XML) and use callback functions to empower code.

This month we will begin a series of Ribbon customizations.  We’ll begin simply with the equivalent of off the rack and eventually move all the way to a bespoke Ribbon – a Ribbon we will build from the ground up.


A note before we start on Ribbon customizations: The approach I am going to show you for adjusting the built-in ribbon is available only for Access 2010 and beyond. Unfortunately, in Access 2007 the ability to easily customize a Ribbon is not available and you must use XML.

As an overview of the framework, ribbons are arranged in tabs. Each tab consists of groups. In each group, you have individual commands, controls, and menus that provide you with further commands.

Before you can begin customizing your ribbon, you have to understand the terminology that Office uses for its intereface. The Ribbon itself is the overall structure of all the options appearing at the top of the screen. For this month’s Wizard, we will only be looking at the main Ribbon, but you should be aware that you can have as many ribbons as you would like for your application, as long as you are willing to build from scratch.

To get to the customization window, take the following steps:

1.  Click on the File Tab.

2.  Select Options.

3.  Select Customize Ribbon.

You will end up with the following window:

For our demo purposes, we will do something very simple: Add a group to the home screen and, in that group, add a command that allows us to go to the design view of the current form or report.

To start, click on the + to the left of the Home tab on the right-hand side of the menu above. This will expand the Home tab, and you will see lines for views, clipboard etc. Since we want to add a new group, you want to click on the new group button at the bottom right hand side on your screen. At this point, your screen should look like this:

When you click the New Group Button, you will get the following screen:

This dialog allows you to name your group whatever you would like and to associate a picture with the group. The dialog box will list a predefined set of pictures that are consistent with the Office commands. Be aware that if you want to add your own custom pictures you can; however, that is considerably more complicated and something that we will cover at a later date.

We will call our new Group “My Custom Group” and choose the happy face to go along with it by clicking on the icon as you see below. Once you have selected it, click OK.

The next step is to add a command to our new group. First, make sure that the new group is highlighted on the right side then, on the left-hand side of the screen, click on the design view icon. You will see that the design view has jumped over below My Custom Tab. At this point, click OK and your changes will be saved. You will also be notified that you must close and reopen the current database for the specified option to take effect. This is misleading in that only some customizations require you to close and reopen the database; this type of change to an existing menu is not one of those.

After you have done this, you should now see that you have a new group on your home tab called My Custom Group and, in that group, you have the option to go directly to a design view when appropriate.

Conclusion and a Look Ahead

This particular example, although trivial, can immediately make your life easier by putting commands you use frequently front and center. This does little more than scratch the surface of the types of things you are able to do. You can take any of the existing commands and put them onto any existing group on an existing tab, or even on a tab of your own making.

In the next Wizard, we’ll see how to create your own commands rather than choosing from existing items. With this technique, you will be able to get into much deeper customizations because you will be able to have your ribbon display commands that are unique to your needs, rather than the generic needs of other Access users. 


Tip of the Month: A Better Way to Time Events

Last month, I showed you a method for timing events in Access. A clever reader, Steph Hollingsworth out of Great Britain, wrote me a very nice note telling me how she appreciated the Wizard and included a much more compact way to time events.

Her method uses a call to a function GetTickCount from an external library. (See the first line below.) Put this into the declaration area of a public module (the top part of a module that you create rather than one that exists in a form or report).  A note about the code below. If you do a cut and paste of the code below beware of extra line breaks. Makes sure that you get remove extras before firing off the code.

The specifics are:

Public Declare Function GetTickCount Lib "kernel32" () As Long° 
Private mlngCount as long
Public Sub StartTimer()
   mlngStart = GetTickCount
End Sub
Public Function EndTimer(Optional sEvent As String) As Long
    EndTimer = (GetTickCount - mlngStart) / 1000
    Debug.Print sEvent & " Time: " & EndTimer
End Function

From here, you can use the StartTimer and EndTimer functions to get a much more compact way of timing items as I explained last month.

My Wizard’s hat is off to Steph. It is a much better technique than the one I had showed. If you ever are in a position where you think there is a better way to do something that I have shown, by all means let me know and I will pass it along in the Access Wizard.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

email: jim@custom-software.biz
phone: 978-392-3462
web: http://www.custom-software.biz

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886