The New Access 2007 Switchboards

Now that Access 2007 Beta 2 Technical Refresh (B2TR) is out, you will notice that we changed the Switchboard Manager functionality. Now that we have Disabled Mode and Embedded Macros, it was about time we revisited the switchboards that the Switchboard Manager cranks out and come up with something that is more up-to-date.

The first you will notice is that switchboards are now VBA free. They are run using embedded macros, and the primary goal is pretty clear: being able to run in disabled mode. The secondary goal is that this also makes them easy to change without major VBA knowledge.

To make switchboards easily extendable and maintanable, their design is actually pretty ingenious. There are two components: the switchboard forms (each created switchboard) and an underlying table for all switchboards (the "Switchboard Items" table).

The "Switchboard Items" table is a table with the following columns:

• SwitchboardID (Number) - This ID indicates the switchboard this row belongs to.
• ItemNumber (Number) - This is the ID of the item on the switchboard. This is of course only unique within a switchboard. Each item in a switchboard will translate to a button, except the one with ID = 0. The first item in the switchboard contains the title of the switchboard.
• ItemText (Text) - This will contain the text used for the item in the switchboard.
• Command (Number) - This indicates which one of the possible commands (e.g. open switchboard, open form in edit mode, etc) the button in the switchboard will execute.
• Argument (Text) - Contains the argument that the command will need. For example, for opening a form in edit mode this argument will contain the form name.

For example, the switchboard table could look like this:

In this case, this is a switchboard called "foo" with two buttons: the first ("foo") opens the switchboard manager and the second ("aaaa") opens "Form1" in edit mode.

Now, let's move on to the switchboard forms. Below you can see a snapshot of a sample one in design mode. The first thing you should notice is that it is bound to the "Switchboard Items" through a query that will limit it to only read switchboard items that are related to this specific switchboard (such as "SELECT * FROM [Switchboard Items] WHERE [ItemNumber]>0 And [SwitchboardID]=TempVars!SwitchboardID ORDER BY [ItemNumber];").

The form has an embedded macro on its OnOpen event (below). The embedded macro will set the Temporary Variable (TempVar) called SwitchboardID with the ID for this switchboard and will set the label of the buttons to match the values in the table (in the example above, "foo" and "aaaa"). There should be one SetProperty call here for each button in the form. Notice that the query mentioned above makes use of this SwitchboardID TempVar to be able to narrow down the data to only relevant items.

As for the button on it, it has an embedded macro on the OnClick event. This embedded macro is the real brain of the switchboard. It contains all the necessary actions to perform all the possible commands you choose in the Switchboard Manager. Below is the embedded macro:

This embedded macro is actually straightforward. Based on the Command # (the column value for the record you clicked on the continuous form) you clicked on, we will only run the necessary code to run that action. The error handling is basically putting up an error message with the last erorr hit. If you run an unknown command, the "Unknown Option" message is shown.

As you can see, it's a very simple design that can be extended in a straightforward manner. You can add your own command ID and logic in the button embedded macro, even one that call VBA (say, after checking that this is not a disabled solution, as explained here). Of course, the switchboard manager will not necessary understand what you did, but who cares? Now you know enough to go on your own.

Disclaimer: everything discussed in this article is subject to change until Access 2007 ships. Note, however, that we don't expect any major changes. Perhaps some of the embedded macro logic and the visuals will change, but I wouldn't expect (don't guarantee however) anything "major". Of course, you should not take this as a guarantee and blah blah bla (see blog disclaimer for legalese).