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!

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.

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!

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.