Visit our new web pages

We have launched a new extended set of web pages at www.alvechurchdata.co.uk. These old pages will stay on the server because people still have links to them but they will not be updated.

The new version of this page is at www.alvechurchdata.co.uk/hints-and-tips/accswitchboard4.html.

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, if you know how the system works then you can modify it to extend its capabilities.

Switchboard table

The system relies on a table and a form, both named "Switchboard". 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.
• 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 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

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 HandleButtonClick in its Click event and each passes its identifying number into that function. Inside the function there's some DAO code which finds 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:

and extend the cases 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 the Switchboard Manager and change the table or form directly:

• Make sure you've got a good backup before you start.
• Read the details in Help.
• Make small changes and test often.

Part 1 Getting started
Part 2 Building the structure
Part 3 Improving the appearance