Behind the scenes
How is it done
The Switchboard Manager in Access is a useful tool and you do not need to
know anything about it in order to be able to build your own system of
switchboards. It does however show up some useful techniques in Access
and in VBA program code. If you know how the system works then you can
modify it to extend its capabilities and perhaps use some of these
programming techniques elsewhere.
The system is based on a table and a form, both named "Switchboard". The
table defines the structure of the switchboards, the form defines their
appearance. If you open the Switchboard table you'll see that it has a
This table has five columns:
is the same for all rows because this system only has one
switchboard and all the entries belong to that switchboard. More complex
systems can have subsidiary switchboards with entries of their own.
is the row number of this button on the switchboard. Together
with the SwitchboardID it forms the primary key for the table.
You can rearrange the order of the buttons on the form if you
alter these values. The first row has an ItemNumber of zero
because it is the switchboard itself.
is the text that will appear alongside the button. For a
switchboard it is the caption of the form.
is a code that identifies one of the commands available. This
sample shows that 3 must mean "Open FOrm" and 6 must mean "Exit
Application". The others are documented in the VBA code of function
HandleButtonClick of the Switchboard form.
is the name of the form, report or switchboard to run. The final
row has no argument because the command "Exit Application" does not
need any more information.
The key feature on the switchboard form is the column of eight buttons.
VBA functions on the form determine the appearance of these buttons at
runtime and respond to the users' clicking the buttons.
Make the buttons visible
When the form loads, the FillOptions method creates a recordset from the
Switchboard table and loops through it to decide which buttons should be
intOption = 2
Me("Option" & intOption).Visible =
Me("OptionLabel" & intOption).Visible =
The loop starts at 2 because the first button must always be visible. A
second loop then sets the caption associated with each button by reading
the ItemText field from the recordset:
Me("OptionLabel" & rst![ItemNumber]).Caption _
Deciding what action to take
Each button calls the HandleButtonClick method in its Click event and each
passes its identifying number into that function. Inside the function
there's some VBA code using
to find the record in the Switchboard table:
rst.FindFirst "[SwitchboardID]=" & _
Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
It retrieves the contents of the Command field from that row and uses that
number in a Case structure:
DoCmd.OpenForm rst![Argument], , , , acAdd
DoCmd.OpenReport rst![Argument], acPreview
In each of these cases, the
object open the selected form or run the selected report as appropriate.
holds the name of a form or a report in the database and the
methods of the
The constants such as
are all defined at the top of the HandleButtonClick function. If you need
to extend the range of actions available to the switchboard you could add
a new constant, for example one to run an export into an Excel spreadsheet:
Const conCmdExportToXL = 9
and extend the VBA case structure with a call to
I can't guarantee that anything I've said on this page will work with
your database.The usual warnings apply if you decide to bypass any
- Make sure you've got a good backup before you start.
- Read the details in Help.
- Make small changes and test often.
Other pages in this series
Building the structure
Improving the appearance
Behind the scenes