Switchboards – Make Life Easier by
Choosing rather than Searching
A somewhat disorganized friend of mine was looking for a novel he
wanted me to read. He keeps his books in a bookcase, but in no
discernable order. It took him several minutes to find the book he
wanted, but he finally did. The book, The Wanderers by James
Michener, turned out to be a wonderful read.
To find the book, my friend had to search his entire collection
-- book by book. He could have saved himself some time and
frustration if he had a better way of organizing his books. If he
had grouped them into logical sets, and then ordered them by author
or by subject (Dewey Decimal System anyone?), then he could have
found what he wanted much more quickly. With an organization system
in place, it would have simply been a matter of selecting the
appropriate section (fiction) and then going to the M's and scanning
for Michener, the author's name.
The lack of organization of my friend's books is very much like
some databases I've seen. Users are able to get around, but they
have to hunt for the form, report or query they want to use. In this
month's issue, we'll explore a better way to navigate the components
of your database - Switchboards!
A complex Access database can have hundreds of forms,
queries and reports. In database lingo these items are called
objects. Many of these objects are used exclusively by other
objects (think queries used by forms), and are never directly
opened by the user. In most cases, even in complex databases,
the objects most frequently used directly by the user, is a
manageable number (10 or 20). In almost every case, there are
just a few major objects, typically forms, that are needed on
a regular basis. Although you could ask (or train) your user
to find the forms in the database, life would be a whole lot
easier if the most frequently used objects were available in
one central place.
Take a look at this screenshot:
The window above shows some of the many forms in the
database. The list is long, requiring a user to scroll up and
down the list to find the form they're searching for - the
same goes for any report, query or table. How long would it
take a new user to find the right form to choose? What if they
don't understand your naming convention?
Now take a look at the alternative:
Here we have simple interface that invites the user to
select from just a handful of choices. In this example, how
long would it take for a user to figure out where to go to
find client data? This menu, called a Switchboard in Access,
facilitates organization of forms, reports, tables and
queries. The menu (we'll call it a Switchboard too, since that
is the term Access uses) is a useful tool for organizing the
most important and frequently used objects, even for those
applications you write for yourself.
So how do we get from the confusing long list of text to
the organized Switchboard? We'll utilize the Switchboard
|The Solution - Design a Switchboard|
Access makes it really easy to create a Switchboard by
using a helpful Wizard called the Switchboard Manager. When
you open a database, one of the menu items at the top of the
screen is Tools. In that menu, you'll find Database Utilities,
in that sub-menu, choose Switchboard Manager. Note that you
may not see these items immediately in the menu (if you wait a
moment they'll appear). If for some reason these options don't
appear, you'll see a double downward pointing arrow (the
techie term is chevron) - just click on that and an expanded
menu will appear.
After you've selected the Switchboard Manager, you'll get a
message saying "the Switchboard Manager was unable to find a
valid Switchboard in this database. Would you like to create
one?" Choose Yes and you'll see:
This screen invites you to create a Main Switchboard, which
is the top-level menu. Choose Edit and you get to the
You can have multiple layers under the Main Switchboard,
and each will have its own name. These are the Items on this
Switchboard, which are the individual line items that your
users will see (in the "Welcome to Tracker" example above,
these items include: See Individual Client, Reports, Tools).
Right now there are none, so press New and you'll see:
This is where you get to add items to the Switchboard. The
text area is where you can type in the choices users will see
when they open the Switchboard. When you click on the arrow to
the right of the Command box, you will see a dropdown list
with the following choices:
- Go to Switchboard - Allows users to go to another
switchboard - more about this later
- Open Form in Add Mode - Opens a form where a user
may enter new data.
- Open Form in Edit Mode - Opens a form where a
user can review and change existing data
- Open Report - Opens a report.
- Design Application - Allows a user to manage
(change) the switchboard, not something you'd typically want
your user to do. I have to say I've never used this item and
don't intend to; I recommend always using the command
Switchboard Manager to manage the Switchboard.
- Exit Application - Closes the application, but
doesn't close Access.
- Run Macro - Runs preprogrammed steps. Although
macros are an easy way to automate processes, I do not
recommend them (more on this subject in a future
- Run Code - Runs VBA code; an excellent way to
accomplish complex or repetitive tasks (again, more to come
on this subject in the future)
As you make choices in the command field, the subsequent
fields in the window will change, depending on what you've
selected. For instance, if you choose either Open Form in Add
Mode or Open Form in Edit Mode, you'll be given a list of
forms in your database to choose from. Make your choices
depending on what you want your Switchboard to look like, and
fill in the fields as you go down. It couldn't get much
Continue creating Switchboard items or even new
Switchboards. Feel free to experiment, since you can always go
back into the Switchboard Manager to change, add, or delete
any item you'd like.
What if you have multiple (five, for example) "frequently
used" reports you would like to make accessible through a
Switchboard. In this case, you'd want to put a Reports button
on the main Switchboard, and have it open to another
Switchboard with all five reports listed. To do this:
- Navigate to the Switchboard Manager
- Choose New
- Give your new Switchboard a name (e.g. Reports)
- In the new Switchboard page called Reports, highlight
Reports and choose Edit
- Add the names of reports that you would like to include
on this Reports sub-Switchboard
- Make sure the last item on the Reports Switchboard is
"Return to Main Menu". To set this up, choose "Go to
Switchboard" in the Command field and choose "Main
Switchboard" from the dropdown list.
In order for your users to be able to open the Reports
Switchboard you have to make it available from the Main Menu.
To do this, use the Switchboard manager to edit the Main Menu.
On the main menu make sure you add an item that let's your
user open the Reports Switchboard. When you're in the edit
mode of the main Switchboard choose add new, Go to
Switchboard|Reports and then choose Reports from the
When you're done click Close and your work will be
automatically saved and one of the menu items on the
Switchboard will be Reports.
After you've created a Switchboard, there will be two new
objects in the database: a form called Switchboard and a table
called Switchboard Items. Exit out of the Switchboard Manager
and take a look at the forms tab. Open up the Switchboard form
and click some buttons. That will show you your Switchboard at
work. It's really simple and quite powerful.
You can safely change the name of this Switchboard form to
follow your naming convention; as a standard I always rename
mine to "fmnu" (for "form menu"; I like to refer to
Switchboards as Menus).
What's more, you can customize the Switchboard by changing
the size, colors, pictures and font. To do this, just open the
form in design view and make any changes you want. You'll
undoubtedly notice that the text to the right of the
Switchboard buttons is different from what you typed. The
reason for this is that all of the Switchboards created by the
Switchboard Manager use the same form, dynamically changing
the text of the Switchboard items per your instructions to the
wizard. You don't have to be concerned with the text for the
item buttons, the form itself will fill those in with whatever
you entered as text for each menu item.
Take a look at the new table created called Switchboard
Items. Open this table and take a look around to see how the
Switchboard Manger has recorded your instructions. This table
holds the values you typed into the Switchboard Manager and is
used by the Switchboard form code to respond to user
selections. I don't recommend changing any values in this
table since this table drives the selection availability.
If you want the Switchboard to open automatically when the
database is started, choose Startup from the Tools Menu and in
the Display Form/Page section choose the name of the
Switchboard form from the dropdown list. When the Switchboard
opens automatically, the user will be able to simply pick from
a list of organized forms, reports, tables, and queries rather
than hunting through a long list of objects to choose what
Using the Switchboard Manager to create a Switchboard makes
life a lot easier. There is however, one major drawback: the
Switchboards created through the Manager are limited to only
eight items. Since you'll want to make each Switchboard easy
to use by including a "Return to Main Switchboard" (or "Exit"
from the main Switchboard) option as one of the items, the
effective limit is seven items. Offsetting this limitation is
the fact that there is no limit to the number of
sub-Switchboards that you can create and link to a main
Switchboard. I've found that this limitation is not a problem
for most applications.
Creating Switchboards to send users directly to the most
frequently used forms, reports, tables and queries yields a
more polished, friendly application. In addition, users will
not be strapped with the burden of understanding the object
naming convention in the Access database. All this with a
relatively small amount of effort on your part - a bargain if
ever there was one!
|Trap of the Month|
Don't change the name of the Switchboard Items table! If
you do, you'll find that your Switchboard will break. The
Switchboard form and the code behind it expect to interact
with a table called "Switchboard Items." If that table doesn't
appear, then you'll get error messages when you try to run
In theory, you could change the name of the table and then
edit the form and the code to look to the same table using a
different name, but that would be more hassle than it's worth.
This is one of the few cases where I recommend you live with a
table that violates a good naming convention.
Tip of the Month-A View into Code
Most of the Access Wizards (like Switchboard Manager) write
code as part of their process. The language that Access uses
is Visual Basic for Applications (VBA), an English-like
language that's pretty easy to learn. Reviewing this code and
watching the code execute is a great way to learn more about
coding. This tip will explain how you can watch code execute.
We'll use this month's Switchboard code as an example:
- Open the form created by the Switchboard Manager in
design mode-- if you haven't changed the name, the form will
be called Switchboard.
- From the menu at the top of the screen choose View, then
Code. This will open up the code window, where you'll find
the directions that the program uses when the user clicks a
button or does something else that prompts the code to
execute. One of the first few lines at the top of the window
will be "Private Sub Form_Open." Right click on that line
and choose Toggle, then Breakpoint. This will put a red dot
to the left of the line and instruct the code engine to stop
when it gets to this point.
- Save and close the form, then startup up your
Switchboard by double clicking the form "Switchboard."
- When the form opens, it will pop you into the code
window and stop on the line with the breakpoint. You can
step through the code line by line by pressing the F8 key.
Although the code written by the Switchboard Manager is not
terribly friendly, I encourage you to trace it in this way as
well as look at other pieces of code written by Access Wizards
to get your feet wet.