Section 14.2. Building Forms with Navigation Smarts | Access 2007[c] The Missing Manual

14.2. Building Forms with Navigation Smarts

The navigation pane's an invaluable tool for getting around your database, but it doesn't suit everyone. People who've never used Access before might find it a little perplexing, and there's nothing stopping someone from changing the navigation options (and opening objects they shouldn't).

To get more control and add a friendly veneer, many Access experts build navigation features into their forms (and occasionally their reports ). After all, a form gives you virtually unlimited possibilities for customization. You can add a paragraph of text, throw in a hot pink background and a company logo, and reduce confusing options to a few fat, friendly buttons .

If you do decide to use forms for navigation, your first decision's what kind of form to build. Access gives you a few options, including built-in support for something it calls switchboards .

14.2.1. Building a Switchboard

A switchboard is a form whose sole purpose is to lead you to other forms (usually, when you click a button). Think of a switchboard as the main menu for your database. It's both the starting place and the central hub of activity. A typical switchboard form has a stack of buttons that lead to different places.

Figure 14-10. This Access switchboard provides single-click access to five different forms. The nice thing about Access switchboards is that you can click your way to building one in a matter of seconds. The drawback's that they have a slightly dated look, which leads picky people to design their own from scratch.

Note: A switchboard, once created, is like any other type of form. So you can use the skills you picked up over the last two chapters to give your switchboard a personal touch after you create it.

To automatically create a switchboard, you need to use the Form wizard. Here's how it works:

Choose Database Tools Database Tools Switchboard Manager .

The first time you click this button in a database, Access informs you that it can't find a switchboard, and asks if you'd like to create one. Click Yes to continue to the Switchboard Manager (Figure 14-11).

If a switchboard already exists, then you'll barrel ahead to step 2, where you can edit the current switchboard.

Figure 14-11. The Switchboard Manager shows a list of pages. Each page is a separate part of the switchboard's menu. The simplest, most straightforward switchboards have only one page, which means they're only one level deep and every button performs a useful action (like opening a form or report).

Click Edit to edit the main switchboard page .

The Edit Switchboard Page window appears (Figure 14-12). Here's the spot where you'll define the actual menu commands.

Figure 14-12. The Edit Switchboard Page window lets you create menu commands, remove ones you don't want anymore, and rearrange them (their order dictates their order on the switchboard form).

To create a new menu command, click New .

The Edit Switchboard Item window appears (Figure 14-13). To create a menu command, you need to supply two pieces of information: the text that appears on the form, and the command that Access should perform when you click the button.

Figure 14-13. This command launches the Attendance report. From the Command drop-down menu, pick the action you want the command to perform.
Enter the menu text, and then choose the action you want the button to perform .

Your options include:
- Go to Switchboard jumps to another switchboard page. You can use switchboard pages to break up really large menus into several smaller menus .
- Open Form in Add Mode opens a form in data entry mode so you can start adding new records.
- Open Form in Edit Mode opens a form in its normal mode for viewing or editing records. (This mode doesn't let you edit the form, contrary to the misleading name .)
- Open Report launches a report in print preview mode.
- Design Application opens the Switchboard Manager window so you can edit the switchboard menu. You rarely need to include this option in the menu.
- Exit Application quits Access.
- Run Macro and Run Code fire up a macro you've created (see Chapter 15) or a piece of Visual Basic code you've written (Chapter 16).
Repeat step 3 and 4 until you've created all the commands you need. Then click Close to move back to the main Switchboard Manager window .

Switchboards have an ugly secret: Each page can accommodate only eight menu commands. If you need more (and who doesn't?), you need to add extra pages to your menu, as described in the next step.
If you've decided to use more than one switchboard page, click New to add the page, enter the page name, and then click OK. Next, click Edit to start adding commands, and then Close when you're finished .

Follow the instructions in steps 3 to 5 to fill in the commands for this page.
When your switchboard's complete, click Close in the Switchboard Manager window .

To try out your switchboard, find the new Switchboard form that Access has created, and then double-click it.

Switchboard forms don't always look right with the tabbed windows that Access uses. Mainly, there'll be some extra blank space at the bottom and the right side.

UP TO SPEED
Switchboards with Multiple Pages

A switchboard shows one page of menu commands at a time. When you first create a switchboard, it has exactly one page. However, you can easily add more from the Switchboard Manager window (see Figure 14-11), using the New button.

Switchboards don't give you any built-in way to get from one page to another. Instead, it's up to you to add the Go to Switchboard command. Suppose you have three pagesa main page and two more with extra commands. On the main page, you need two Go to Switchboard commands, each of which leads to one of the new pages. On the new pages, you need a Go to Switchboard command that lets you get back to the main page.

Using multiple switchboard pages really isn't ideal. It's just too much like the annoying touch-tone menus on automated voicemail systems. Before you know it, you'll have pages that lead to more pages and you'll be scrambling through all of them to find the command you want. So, if possible, consider limiting your switchboard to eight tasks. If you're crafty, each of these tasks can pull together multiple forms, either by using subforms (Section 13.3.1) or buttons that navigate to related records (Section 14.3.1). And if you really and truly do need a more comprehensive switch-board, consider undertaking the task of building your own. That way, you can split all the navigation buttons into separate sections, draw borders around related buttons, add labels with descriptive text, and so on. In the end, it'll probably look a whole lot better.

To remedy this problem, you can show the switchboard form as a pop-up window that appears above all other windows. That way, the window's always sized correctly. To make this change, open your form in Design view. Using the Property Sheet to configure the form, choose the Other tab, and then set the Pop Up property to Yes.

14.2.2. Designating a Startup Form

Seeing as the switchboard's the gateway to your Access database, it's a good starting point for folks who are going to use your database. You can tell Access to open any form (like the switchboard) automatically when someone first opens your database. Here's how:

Choose Office button Access Options .

The Access Options window appears.
In the list on the left, click Current Database .

The settings for the current database appear.
Under the Application Options heading, look for the Display Form box. Choose your switchboard form in the list .

Now, whenever you open the database, Access launches your form immediately.
Optionally, if your switchboard completely eliminates the need for the navigation pane, look down under the Navigation heading and clear the checkbox next to the Display Navigation Pane setting .

BEHIND THE SCENES
Switchboard Menus are Stored in the Database

If you're the sort of person who likes to worry about potential problems on the horizon, you've probably already noticed something questionable in the switchboard universe. It's easy to imagine a scenario where you create the perfect switchboard, but then decide to redesign your database with a few new forms. In this situation, you don't want to be stuck with new forms but an old switchboard that doesn't include buttons to display them.

Fortunately, the people at Microsoft who created the switchboard feature thought about exactly this sort of problem. Their solution's to have Access store the switchboard menu in the database.

Here's how it works. When you create a switchboard, Access adds a table named Switchboard Items to your data-base. As you add entries to the switchboard, Access enters them into the Switchboard Items table. In order to make this system work, Access needs to take a few extra steps.

Namely, when your switchboard form's opened, Access runs a macro (see Chapter 15) that grabs the list of switchboard items from the table, and uses it to fill in the list of buttons that appear on your switchboard form.

Here's how you benefit. If you decide you want to change your switchboard after creating it, it isn't too difficult. You just open the Switchboard Manager (choose Database Tools Database Tools Switchboard Manager), and then edit your entries. Access updates the records in the Switchboard Items table. It doesnt need to change the Switchboard form itself. So if you've customized the switchboard form (with your own content or buttons), it isn't affected.

Usually, you won't have any reason to open the Switchboard Items table directly. So why not hide it in the navigation pane to prevent confusion? Section 1.4 explains how.

If you're worried about overzealous folks opening something they shouldn't, then hide the navigation pane, and train them to use the switchboard for all their needs. It's like navigating the database with training wheels on.

Tip: Every time you finish a task in a database, you return to the switchboard and pick another task (or just exit Access). To make this process easier, you may want to add a button to each form you create that closes it, allowing the switchboard to come back to the forefront. You can do this using the Command Button wizard (Section 13.2.7).

14.2.3. Switchboard Alternatives

Switchboards are a great idea, but they have some clear drawbacks. The eight-item limit, the slightly antiquated look, and the extra maintenance are all good reasons to think twice about using a switchboard unless it's clearly simplifying the navigation in an otherwise complicated database.

If you aren't quite convinced that the Access switchboard fits the bill, you have some other options, as described in the following sections.

14.2.3.1. Custom switchboard forms

One of the simplest and most compelling approaches is to build your own switchboard form by hand, and then set that as the startup form for your database. Consider the form shown in Figure 14-14, which presents the same navigation buttons as the switchboard in Figure 14-10, but adds a hefty dose of modern styling. This switchboard presents a clean blank surface along with an image control that shows a snazzy graphic. It also includes several ordinary button controls that were created with the Command Button wizard (Section 13.2.7). Each button's Back Style property's set to Transparent, to give it a more modern flat look. The Cursor On Hover property's set to "Hyperlink hand" so that the mouse pointer changes to a pointing hand when you move over a button, which lets you know that you can click there.

Note: Another approach is to use a picture as the background for the whole form, and put other controls on top. To do this, you need to set these properties on the form: Picture (the picture file you want to show), Picture Tiling (whether or not the image should be repeated to fill the available space), Picture Alignment (use Top Left so that it starts from the form's top-left corner), and Picture Size Mode (use Clip, so the picture's not stretched , resized, or otherwise mangled). All the controls you place on top of a form with a background picture should have their Back Style property set to Transparent so that the picture shows through.

Check out the "Missing CD" page at www.missingmanuals.com to see a screencast (an online, animated tutorial) that demonstrates how the custom switchboard shown in Figure 14-14 was created.

Figure 14-14. This custom switchboard's just an ordinary form with a lot of navigation buttons. The advantage to crafting your own switchboard is that you can make everything just the way you like it. The disadvantage is that it's more work to update when the database changes. Every time you add a new form, you need to modify your switchboard's design to use it.

14.2.3.2. Compound forms

Alternatively, you could forget about designing a way to jump from form to form, and instead create a form that brings everything you need into one place. This trick, called a compound form , uses the subform control you learned about in Section 13.3.1.

In Chapter 13, you learned how the subform control lets you show related data (like a list of products for the current product category). However, the subform also makes sense if you want to show several unrelated tables in one place. Just leave the Link Master Fields and Link Child Fields properties of the subform emptythat way the subform shows all the records without filtering. Figure 14-15 shows an example.

Tip: There's a shortcut to creating a compound form. First, choose Create

Forms

Form Design to create a blank new form. Find a form you want to use in the subform, and then drag it from the navigation pane to your new forms design surface. Access creates a subform control that shows that form. You can also drag a table onto your form, in which case Access creates a subform for that table (and asks you to pick a name for it).

Figure 14-15. This compound form's an all-in-one dashboard for adding and reviewing products and reviewing the customer list. The prebuilt templates that Access includes (Section 1.2.1) often use compound forms to put several related editing tasks in one place.

If you're using Tables and Related Views mode in the navigation pane, a compound form usually appears in the Unrelated Objects area. That's because the switchboard form itself doesn't use any tables. Instead, it contains subforms, and these subforms use the various tables you're displaying.

14.2.4. Showing All Your Forms in a List

You may find one last trick useful when building a navigation hub. Rather than create a button for each and every form you want to use, you can create a list control that has them all. When the person using the database picks a form from the list, Access jumps to that form. This approach works well if you have a large number of forms, which would make the button-only approach irredeemably cluttered.

Note: This technique works as well for reports as it does for forms.

The first step's to put the form names in a list box. Access gives you three ways to do this:

Type the names in by hand . Just drop a combo box control onto your form. When the wizard starts, choose "I will type in the values that I want", and then enter the form names in the appropriate order.

Note: Section 13.2.6 has more about the List wizard. Just remember, at the end of the wizard, you need to choose "Remember the value for later use". Your list's used for navigation, not record editing.
Pull the names out of a custom table you create . Create a new table, and then fill it with the names of the forms you want to show in the list. Then, when you create the combo box, choose "I want to look up the values in a table or query", and then specify your custom table. This method's conceptually similar to the way that an Access-generated switchboard works.
Pull the names out of the system table . For a really nifty trick, you can get the full list of forms straight from your database without any extra work. The trick's to use one of the hidden system tables . These system tables are tables that Access uses to keep track of database objects. Every Access database has these tables, but tucked out of sight.

The first two options are straightforward. The third option's more impressive, but it takes a little more work. Ordinarily, the system tables are hidden from sight. You can pop them into view (see Figure 14-16) by choosing Show System Objects from the Navigation Options window. Showing the system tables isn't a good choice for the long term , because any change you make in these tables could damage your database and confuse Access.

Even if you don't show the system tables, you can still use them. The most interesting system table's MSysObjects, which lists all the objects in your database. You can get a list of all the forms in your database by querying this table with an SQL command (see Section 6.2.3 for a refresher on how queries use SQL). The Name field provides the database objects' name. The Type field contains a numeric code that identifies the type of object. Table 14-1 lists the types in which you may be interested.

Figure 14-16. Here, the navigation pane shows a bunch of system tables, which are ordinarily hidden. You can open them to take a look, but you'll have a hard time making sense of the (mostly numeric) data they contain.

Table 14-1. Useful Type Codes

Object	Type
Table	1
Query	5
Form	-32768
Report	-32764

Based on this information, you can get a list of forms by retrieving the Name field, and then filtering out those records with a Type value of -32768.

You can most easily build this bit of logic into your list control by adding your list to the form and skipping out of the wizard (hit Esc when it starts). Then, you can configure the control using the Property Sheet. In the Data tab, find the Row Source property, and then enter the following SQL statement, which performs the query you need:

 SELECT Name FROM MSysObjects WHERE MSysObjects.Type=-32768

You now have a list that shows all the forms in the database. You can substitute the number -32764 for -32768 to get reports instead; Figure 14-17 shows the results.

So far, you've seen only half of the solution you need. You've learned how to get the list into the right control, but at the moment nothing happens when you use the control. You really need a way to jump to the selected form or report.

Figure 14-17. This form shows a list of all the available reports.

It turns out that this solution's a bit more advanced than the examples you've seen so far. In order to make it work, you need to customize a macro. (A macro's a list of one or more instructions that's stored as a database object so you can use any time.)

As you learned in Chapter 13, when you create a command button, the Button wizard asks you a few questions, and then builds the macro you need. However, the Button wizard's woefully underpowered. For instance, while it can create a macro that navigates to a specific form, it can't create a macro that can go to any form. But with just a little more work, you can create a simple macro with the wizard, and then fix it up to really suit your needs. Here's how:

Drop the button onto your form .

Place it next to the combo box control. The Button wizard launches.
Choose the Report Operations category and the Open Report action, and then click Next .

Or, if you're showing a list of forms, choose the Format Operations category and the Open Form action.
Pick any report (or form), and then click Next .

It doesn't matter what you choose here, because you'll change this part later.
Complete the wizard .

Make sure you give your button a suitable caption, like "Go," "Open Form," or "Show Report."

Once the wizard's finished, it's time to take a closer look at the button in the Property Sheet.
In the Property Sheet, select your newly created button, and then switch to the Event tab .

Events are occurrences that can trigger your macros. For example, every button has an OnClick event that takes place when you click the button.
Find the OnClick property, and then click inside the property box, where it says [Embedded Macro] .

An ellipsis ( ) appears at the corner of the box.

Click the ellipsis to edit the macro .

A macro editing window appears (Figure 14-18). In the lower portion of the window, an Action Arguments section lets you edit how the macro works.

Figure 14-18. You'll learn much more about this window in Chapter 15. For now, all you need to know is that this macro has a single action (represented by the single line in the grid). That action opens a report (as indicated by the OpenReport value in the Action column). The Arguments column has all the additional informationnamely, which report to open.

In the Action Arguments section, find the Report Name (or Form Name) property. Change it to the expression = MyList.Value

This expression finds your combo box, and pulls out the currently selected value. It assumes your combo box is named MyList. If not, change the expression accordingly . (If you don't remember the name of your list control, click to select it, and then look at what name appears in the drop-down list at the top of the Property Sheet.)
Close the macro window, and then choose Yes to save your changes when prompted .

You return to the form design window.
Switch to Form view, and then try out your new list mojo .

You should be able to select a form in the list, and then click the button to open the form you chose.