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.

Switchboard table

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 simple structure:

This table has five columns:

• SwitchboardID 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.
• ItemNumber 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.
• ItemText is the text that will appear alongside the button. For a switchboard it is the caption of the form.
• Command 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.
• Argument 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.

Switchboard form

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 displayed:

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:

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 DAO to find the record in the Switchboard table:

It retrieves the contents of the Command field from that row and uses that number in a Case structure:

In each of these cases, the Argument holds the name of a form or a report in the database and the methods of the DoCmd object open the selected form or run the selected report as appropriate.

The constants such as conCmdOpenFormAdd 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:

and extend the VBA case structure with a call to DoCmd.TransferSpreadsheet(...).

Final warning

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 wizard:

• 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

Part 1 Getting started
Part 2 Building the structure
Part 3 Improving the appearance
Part 4 Behind the scenes