The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
March 2005

Switchboards – Make Life Easier by Choosing rather than Searching

A somewhat disorganized friend of mine was looking for a novel he wanted me to read. He keeps his books in a bookcase, but in no discernable order. It took him several minutes to find the book he wanted, but he finally did. The book, The Wanderers by James Michener, turned out to be a wonderful read.

To find the book, my friend had to search his entire collection -- book by book. He could have saved himself some time and frustration if he had a better way of organizing his books. If he had grouped them into logical sets, and then ordered them by author or by subject (Dewey Decimal System anyone?), then he could have found what he wanted much more quickly. With an organization system in place, it would have simply been a matter of selecting the appropriate section (fiction) and then going to the M's and scanning for Michener, the author's name.

The lack of organization of my friend's books is very much like some databases I've seen. Users are able to get around, but they have to hunt for the form, report or query they want to use. In this month's issue, we'll explore a better way to navigate the components of your database - Switchboards!

In this issue
  • Tip of the Month-A View into Code
  • The Problem
  • The Solution - Design a Switchboard
  • Switchboard Infrastructure
  • Conclusion
  • Trap of the Month

  • The Problem

    A complex Access database can have hundreds of forms, queries and reports. In database lingo these items are called objects. Many of these objects are used exclusively by other objects (think queries used by forms), and are never directly opened by the user. In most cases, even in complex databases, the objects most frequently used directly by the user, is a manageable number (10 or 20). In almost every case, there are just a few major objects, typically forms, that are needed on a regular basis. Although you could ask (or train) your user to find the forms in the database, life would be a whole lot easier if the most frequently used objects were available in one central place.

    Take a look at this screenshot:

    The window above shows some of the many forms in the database. The list is long, requiring a user to scroll up and down the list to find the form they're searching for - the same goes for any report, query or table. How long would it take a new user to find the right form to choose? What if they don't understand your naming convention?

    Now take a look at the alternative:

    Here we have simple interface that invites the user to select from just a handful of choices. In this example, how long would it take for a user to figure out where to go to find client data? This menu, called a Switchboard in Access, facilitates organization of forms, reports, tables and queries. The menu (we'll call it a Switchboard too, since that is the term Access uses) is a useful tool for organizing the most important and frequently used objects, even for those applications you write for yourself.

    So how do we get from the confusing long list of text to the organized Switchboard? We'll utilize the Switchboard Manager!

    The Solution - Design a Switchboard

    Access makes it really easy to create a Switchboard by using a helpful Wizard called the Switchboard Manager. When you open a database, one of the menu items at the top of the screen is Tools. In that menu, you'll find Database Utilities, in that sub-menu, choose Switchboard Manager. Note that you may not see these items immediately in the menu (if you wait a moment they'll appear). If for some reason these options don't appear, you'll see a double downward pointing arrow (the techie term is chevron) - just click on that and an expanded menu will appear.

    After you've selected the Switchboard Manager, you'll get a message saying "the Switchboard Manager was unable to find a valid Switchboard in this database. Would you like to create one?" Choose Yes and you'll see:

    This screen invites you to create a Main Switchboard, which is the top-level menu. Choose Edit and you get to the following screen:

    You can have multiple layers under the Main Switchboard, and each will have its own name. These are the Items on this Switchboard, which are the individual line items that your users will see (in the "Welcome to Tracker" example above, these items include: See Individual Client, Reports, Tools). Right now there are none, so press New and you'll see:

    This is where you get to add items to the Switchboard. The text area is where you can type in the choices users will see when they open the Switchboard. When you click on the arrow to the right of the Command box, you will see a dropdown list with the following choices:

    1. Go to Switchboard - Allows users to go to another switchboard - more about this later
    2. Open Form in Add Mode - Opens a form where a user may enter new data.
    3. Open Form in Edit Mode - Opens a form where a user can review and change existing data
    4. Open Report - Opens a report.
    5. Design Application - Allows a user to manage (change) the switchboard, not something you'd typically want your user to do. I have to say I've never used this item and don't intend to; I recommend always using the command Switchboard Manager to manage the Switchboard.
    6. Exit Application - Closes the application, but doesn't close Access.
    7. Run Macro - Runs preprogrammed steps. Although macros are an easy way to automate processes, I do not recommend them (more on this subject in a future newsletter).
    8. Run Code - Runs VBA code; an excellent way to accomplish complex or repetitive tasks (again, more to come on this subject in the future)

    As you make choices in the command field, the subsequent fields in the window will change, depending on what you've selected. For instance, if you choose either Open Form in Add Mode or Open Form in Edit Mode, you'll be given a list of forms in your database to choose from. Make your choices depending on what you want your Switchboard to look like, and fill in the fields as you go down. It couldn't get much simpler.

    Continue creating Switchboard items or even new Switchboards. Feel free to experiment, since you can always go back into the Switchboard Manager to change, add, or delete any item you'd like.

    What if you have multiple (five, for example) "frequently used" reports you would like to make accessible through a Switchboard. In this case, you'd want to put a Reports button on the main Switchboard, and have it open to another Switchboard with all five reports listed. To do this:

    • Navigate to the Switchboard Manager
    • Choose New
    • Give your new Switchboard a name (e.g. Reports)
    • In the new Switchboard page called Reports, highlight Reports and choose Edit
    • Add the names of reports that you would like to include on this Reports sub-Switchboard
    • Make sure the last item on the Reports Switchboard is "Return to Main Menu". To set this up, choose "Go to Switchboard" in the Command field and choose "Main Switchboard" from the dropdown list.

    In order for your users to be able to open the Reports Switchboard you have to make it available from the Main Menu. To do this, use the Switchboard manager to edit the Main Menu. On the main menu make sure you add an item that let's your user open the Reports Switchboard. When you're in the edit mode of the main Switchboard choose add new, Go to Switchboard|Reports and then choose Reports from the Switchboard list.

    When you're done click Close and your work will be automatically saved and one of the menu items on the Switchboard will be Reports.

    Switchboard Infrastructure

    After you've created a Switchboard, there will be two new objects in the database: a form called Switchboard and a table called Switchboard Items. Exit out of the Switchboard Manager and take a look at the forms tab. Open up the Switchboard form and click some buttons. That will show you your Switchboard at work. It's really simple and quite powerful.

    You can safely change the name of this Switchboard form to follow your naming convention; as a standard I always rename mine to "fmnu" (for "form menu"; I like to refer to Switchboards as Menus).

    What's more, you can customize the Switchboard by changing the size, colors, pictures and font. To do this, just open the form in design view and make any changes you want. You'll undoubtedly notice that the text to the right of the Switchboard buttons is different from what you typed. The reason for this is that all of the Switchboards created by the Switchboard Manager use the same form, dynamically changing the text of the Switchboard items per your instructions to the wizard. You don't have to be concerned with the text for the item buttons, the form itself will fill those in with whatever you entered as text for each menu item.

    Take a look at the new table created called Switchboard Items. Open this table and take a look around to see how the Switchboard Manger has recorded your instructions. This table holds the values you typed into the Switchboard Manager and is used by the Switchboard form code to respond to user selections. I don't recommend changing any values in this table since this table drives the selection availability.

    If you want the Switchboard to open automatically when the database is started, choose Startup from the Tools Menu and in the Display Form/Page section choose the name of the Switchboard form from the dropdown list. When the Switchboard opens automatically, the user will be able to simply pick from a list of organized forms, reports, tables, and queries rather than hunting through a long list of objects to choose what they need.


    Using the Switchboard Manager to create a Switchboard makes life a lot easier. There is however, one major drawback: the Switchboards created through the Manager are limited to only eight items. Since you'll want to make each Switchboard easy to use by including a "Return to Main Switchboard" (or "Exit" from the main Switchboard) option as one of the items, the effective limit is seven items. Offsetting this limitation is the fact that there is no limit to the number of sub-Switchboards that you can create and link to a main Switchboard. I've found that this limitation is not a problem for most applications.

    Creating Switchboards to send users directly to the most frequently used forms, reports, tables and queries yields a more polished, friendly application. In addition, users will not be strapped with the burden of understanding the object naming convention in the Access database. All this with a relatively small amount of effort on your part - a bargain if ever there was one!

    Trap of the Month

    Don't change the name of the Switchboard Items table! If you do, you'll find that your Switchboard will break. The Switchboard form and the code behind it expect to interact with a table called "Switchboard Items." If that table doesn't appear, then you'll get error messages when you try to run your menu.

    In theory, you could change the name of the table and then edit the form and the code to look to the same table using a different name, but that would be more hassle than it's worth. This is one of the few cases where I recommend you live with a table that violates a good naming convention.

    Tip of the Month-A View into Code

    Most of the Access Wizards (like Switchboard Manager) write code as part of their process. The language that Access uses is Visual Basic for Applications (VBA), an English-like language that's pretty easy to learn. Reviewing this code and watching the code execute is a great way to learn more about coding. This tip will explain how you can watch code execute.

    We'll use this month's Switchboard code as an example:

    • Open the form created by the Switchboard Manager in design mode-- if you haven't changed the name, the form will be called Switchboard.
    • From the menu at the top of the screen choose View, then Code. This will open up the code window, where you'll find the directions that the program uses when the user clicks a button or does something else that prompts the code to execute. One of the first few lines at the top of the window will be "Private Sub Form_Open." Right click on that line and choose Toggle, then Breakpoint. This will put a red dot to the left of the line and instruct the code engine to stop when it gets to this point.
    • Save and close the form, then startup up your Switchboard by double clicking the form "Switchboard."
    • When the form opens, it will pop you into the code window and stop on the line with the breakpoint. You can step through the code line by line by pressing the F8 key.

    Although the code written by the Switchboard Manager is not terribly friendly, I encourage you to trace it in this way as well as look at other pieces of code written by Access Wizards to get your feet wet.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
    Powered by

    Custom Software | - | Westford | MA | 01886