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

Ribbon Commands Unique To You

Getting Something Just for You

If you have kids who are in college, or if you've recently been in college, you know that one of the challenges is signing up for the courses you want. Frequently the class you want is unavailable; it might be full or simply not offered. In most cases, students will just take a different course. However the truly clever student will decide that they want a course just for them. They will do the legwork to design a course then recruit a professor to supervise the independent study – a course just for them.

Last month, we learned how to take the standard Ribbon and add existing commands (the equivalent of a course that’s already offered and has an open slot). This month will be the first of two Wizards that will show you how to add a command that is just for you (the equivalent of an independent study).

The Building Blocks

Our goal is to make a custom routine written in Access’s programming language (VBA) available on the home tab of the default Ribbon. Unfortunately, this isn't something we can do directly; we have to have an intervening macro in order to run our code. So we need two pieces:

  • Our VBA code.
  • A macro to run the code.

For our code we will use the following function. Note that I use a function rather than a subroutine. The reason for this is that a macro can call a function, but cannot call a subroutine. Although this may seem illogical, it is one the quirks of Access.

Function MyIndependentStudy()
    MsgBox "Hello World."
End Sub
If you already have a module that is not associated with a form or report, you can put the function there. Otherwise create a new module by clicking on the create tab and choosing module on the far right-hand side of the Ribbon. Once your module is created, you can type the function above and save the module giving it whatever name you like. If you need a tutorial on how to create a function from scratch, see the August 2006 Wizard.

As I mentioned above, to run our function from the Ribbon, we have to take a side trip through a macro. To create a new macro, click on the create tab of the default Ribbon then click on macro, which is on the far right-hand side in the macros and code group. That will bring you to a window that has a green cross and a message inviting you to add a new action with a downward black triangle. Right click on that downward right triangle and scroll down until you see run code as shown below.

At this point, you will be invited to enter a function name. Below, you will see that I typed in the name of my function, MyIndependentStudy.  It is important to that you precede the name of your function with an equal sign and end with the open and close parentheses (). 


Next, save the macro:  Let’s call it My Macro. Then click the Run button (the red ! at the left-hand side of the Ribbon) and you should see the message “Hello World.” If you find this is not the case, make sure that your function is in a module not associated with a form or report.

Next Step and Coming Attractions

At this point, we have the pieces of the puzzle in place: the macro and the function. Next month, we will show you how to integrate these two pieces so they show up on your Ribbon.

Tip of the Month: Easy Access to the Name and Path of the Current Database

If you need to get the full name and path to the current database, you can of course go to the file save as dialog and, from there, highlight the path and then highlight the file name. However, there is a much easier way. If you open an immediate code window (by pressing the control and G keys simultaneously) and type in the following:


you will get a printout of the full path and name of your current database. You can then make a copy of the result and paste it wherever you’d like.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

phone: 978-392-3462

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

unsubscribe from this list | update subscription preferences