Using Switchboard for easy navigation
Requirement: Visual Basic Knowledge
File to download: None
The benefits of using a Switchboard
Switchboard in Microsoft Access is a wonderful navigation tool. It’s versatile and user-friendly. Most of the users I encountered have some sort of phobia of using MS Access. Almost every time when I advocated using Access over Excel on some applications, especially for those involve a lot of data crunching and data storage, I got some resistance from the users. The major reason of such phobia is that the users are not familiar of using Access and they easily get “lost”. Many of the users have hard time to navigate among the mosaic of tables, queries, forms, and reports. If I put in a switchboard and do a demo to the users, I immediately see a substantial ease of reluctance.
The creation and structuring of a switchboard require some knowledge of using Visual Basic (VB) coding. It may deter some users to create one. Even a seasoned VB expert in Excel may find that it is a bit intimidating to do VB coding in Access because the Access VBA is somewhat different from Excel VBA in terms of object referencing, data reading and writing, and some other little things. However, using Switchboard is proven to be a valuable tool for success.
Below 3 illustrations demonstrate how the navigation may look like when we use a Switchboard. With a good design, the users should be able to reply on just the Switchboard to navigate without ever to resort to the use of the navigation pane. In fact, I sometimes completely hid the navigation pane from the users. The only thing they see when they open an Access file is the Switchboard. All accesses of tables, forms, and reports have to go through the Switchboard. Please note that you should always have a button for returning to the Main Switchboard or other pages of a Switchboard.
Besides the benefit of user-friendliness, the use of a Switchboard brings two more significant benefits: program integrity and security. When I create an Excel worksheet, the users tend to change something to it even I have already protected a worksheet with password. The users may not be able to change anything on the protected worksheets, but they can insert a new page to it or alter a name definition. Therefore, Excel files tend to require more effort to maintain them.
However, when I create an Access program, it can last for more than ten years without even a single alteration from hundred of users. One major reason is that users are less familiar to Access files so they tend not to mess with it. But don’t forget that the users can still make accidental changes and that is still a major concern. There are certain measures you can do to avoid users making accidental changes. One smart way of achieving it is to put in a Switchboard and hide all other things, so the users can not directly touch the structure of the tables, forms, and reports. I usually convert all queries into SQL statements and embed them to the Visual Basic Modules. Then I hide the VB modules by saving the Access files into MDE or ACCDE format. With all the practices that I put in, the Access files achieve almost a 100% program integrity and security.
The Structure of a Switchboard
There are 6 types of object in Microsoft Access: tables, queries, forms, reports, macros, and modules. Switchboard is a “form” object, but it also links to a “table” and a “module” object. The “table” object is called “Switchboard items”. The “module” object is automatically created once you created a Switchboard form object. The “module” object cannot be found under the “Modules” area in Access. Instead, it is hidden and you need to open it through the form object.
Click the “View” icon and choose the “Design View”. You will notice that the ribbon changed. On your right hand side, there is a “View Code” icon. If you click it, you will see the Visual Basic Codes that is automatically generated when you create your Switchboard.
I tried not to change anything with the VB codes that associated with the Switchboard. However, there is one part of the codes that are really important for me to work with the Switchboard. It is the command codes that tells the Switchboard what actions need to be carried out once the users click one of the Switchboard buttons.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
From the above code, you might be able to tell that the action to carried out for “1” to go to different “page” of the Switchboard. “2” is to open a form for change (not just to add, but also change, or delete). “3” is to open a form to view the data, etc. Those codes are necessary to be put into the “Command” field of a “Switchboard Item” table. The most frequent used commands for me are 1, 6, 7, and 8.
When you create a Switchboard form, a “Switchboard Items” table will be automatically created. If you double click the “Switchboard Items” table (or right click the mouse and select the “Design View”), you will see the structure of the table.
In the new version of Microsoft Access (version 2007 or afterwards), when you click the Switchboard form object, you will see the “Switchboard Item” table on the tab side-by-side with the form.
Steps to create a Switchboard
Step 1: Click the “Database Tools” on the ribbon and then click the “Switchboard Manager”.
Step 2: Click “Yes” button when the pop-up screen asks you if you want to create a new switchboard.
Step 3: Now you edit the items on the Switchboard items table. When you are done with the table editing, you can click the Switchboard and then press the “Refresh All” button to see the result.
