For starters, consider using filtering to cut down the amount of information shown in the navigation pane. You might have a database with three dozen objects, only ten of which you use regularly. In this case, there’s no reason to show the objects you don’t use.

Essentially, Access lets you make two decisions with the navigation pane:

The confusing part is that you make both these choices using the same menu. To open this menu, click the drop-down arrow in the navigation pane’s title region. Figure 14-1 explains how it works.

You can choose to categorize the navigation pane in five ways:

When you apply filtering in the navigation pane, Access completely hides whatever you don’t want to see. But as you probably already know, Access gives you another option. You can click the collapse arrows next to a specific section to shrink it so that only the section title is visible (Figure 14-3). You can then pop it back into display when you need it.

Figure 14-3. Click the collapse arrows to quickly hide the objects in a particular section. In this example, the Queries group is collapsed neatly out of the way.

Figure 14-4. So far, you’ve been using list view in the navigation pane, the most compact option. However, Access also lets you use a slightly bigger icon view (left), or a details view that includes information about when an object was created and last modified (right).

The filtering system has one limitation—it lets you choose only one category at a time. If you’ve chosen “Tables and Related Views,” you can filter the list down to the objects that are related to a single table. However, you can’t choose to include two (or more) table groups. Similarly, if you choose Object Type, you can show all the forms or all the reports in your database, but you can’t show forms and reports without including everything else (although the collapsing trick shown in Figure 14-3 helps to reclaim most of the space).

You can use an easy way around this restriction. To get more control over filtering, follow these steps:

Hiding the groups you don’t want to see is all well and good—but what if there’s a single object you want to tuck out of sight? Maybe you want to make sure other people who use your database aren’t distracted by a few potentially risky action queries (Chapter 8) that they really shouldn’t use. No problem. Just right-click the query in the navigation pane, and then choose “Hide in this Group.”

To reveal a hidden object, you first need to configure the navigation pane so that it shows hidden objects. To do so, right-click the title bar, choose Navigation Options, add a checkmark in the Show Hidden Objects box, and then click OK. Now, hidden objects appear in the navigation pane, but they’re slightly faded so you can distinguish them from the other non-hidden objects. To unhide an object, right-click it, and then choose “Unhide in this Group.”

All of these approaches—filtering, custom groups, hidden objects—are designed to make your database easier to use. These approaches don’t provide any security. (A person who really wants to use a database object can just change the navigation settings to get to it.)

Ordinary people don’t think in terms of tables and database objects. Instead, they think about the tasks they need to accomplish. But none of the readymade grouping options fit this approach. Fortunately, you can build your own groups that do. Here’s how:

Figure 14-8. One reason you might create multiple views is if different people use your database. In the Cacophoné Studios example, the administrative staff sees forms for creating classes and adding students (using the Administration view), while the teachers get to print attendance lists and create assignments (using the Teachers view, which is selected here). As you can see, the Teachers view contains a category named “Attendance and Marking” and one named “Payments.” Each has its own set of forms and reports.

If you just can’t bear to have anything out of your sight, you may need to put up with a cumbersomely long list of objects in the navigation pane. However, Access still provides you with one convenient feature that can save you hours of scrolling.

It’s the search box, and it lets you jump to an object almost instantaneously, provided you know its name.

To show the search box, follow these steps:

The search box appears at the top of the list in the navigation pane. As you type, Access filters the list so it includes only matching objects (Figure 14-9).

Figure 14-9. The search box matches objects that contain the text you type. So if you type Class, you’ll see objects like Classes and Students_Classes.

A menu form has just one purpose—to transport people to other forms (usually, when they click a button). A typical menu form doesn’t display any information—it simply provides a stack of buttons that lead to different places. It serves as both a starting place and the central hub of activity for your database.

Building a custom menu form is one of the simplest and most effective ways to provide navigation for your database. You simply create a series of buttons and configure each one to show the appropriate form (as described on Performing Actions with Command Buttons). You can even place a background picture on your form, or add an effect to make your buttons stand out. Figure 14-10 shows an example.

Figure 14-10. This custom menu is just an ordinary form with a lot of navigation buttons. The advantage to crafting your own custom menu form is that you can make everything just the way you like it. The disadvantage is that you need to maintain it. For example, every time you add a new form to your database, you need to add a button to your menu to use it.

This menu form 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 (Performing Actions with Command Buttons). Each button’s Back Style property is set to Transparent, to give it a more modern flat look. The Cursor On Hover property is 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.

Another approach is to use a picture as the background for the whole form and to put other controls on top of the background. To do this, you need to set these properties on the form: Picture (the picture file you want to show), Picture Tiling (whether 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 isn’t 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.

Since a custom menu form is 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 automatically when someone first opens your database. Here’s how:

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 on The Subform Control.

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 empty—that way the subform shows all the records without filtering. Figure 14-11 shows an example.

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

When creating a compound form like the one in Figure 14-11, you should set the form’s Navigation Buttons property to false (because the real navigation buttons are in the subforms, not the main form). You can also set the Record Selectors property to No to prevent the record selection margin from appearing at the left.

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 form itself doesn’t use any tables. Instead, it contains subforms, and these subforms use the various tables you’re displaying.

You may find one last trick useful when building a navigation hub. Rather than create a button for each 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 irredeemably clutter the button-only approach.

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

The first two options are straightforward. The third option is 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-12) 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.

Figure 14-12. 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.

Even if you don’t show the system tables, you can still use them. The most interesting system table is 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 Analyzing a Query 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.

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 (press 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-13 shows the results.

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

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.

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

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 is 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:

Here’s how to create a navigation form like the one shown in Figure 14-15.

Figure 14-18. This navigation form uses several tricks to improve its appearance. It has no header, the navigation buttons are artfully styled, and the navigation subform uses anchoring so it expands to fill the available space. You can find this example, along with all the navigation forms in this chapter, on the Missing CD page (www.missingmanuals.com/cds/access13mm).

Creating a two-level menu is almost as easy as creating a simple single-level menu. Here’s how to do it:

Ordinary navigation buttons don’t look bad, but with a little more effort you can replace them with something a bit more stylish.

Your first option is to add a picture to a navigation button, which can either replace your text or sit alongside it (Figure 14-20). Before you do so, open the Property Sheet by choosing Form Layout Tools | Design→Tools→Property Sheet. Then, select a button and set the following properties:

Your second option is to alter the button’s appearance by using the formatting commands in the Form Layout Tools | Format→Control Formatting section of the ribbon, just as with ordinary buttons. Here’s a quick review:

Figure 14-20. Here, tiny icons make it obvious which form is for viewing data, and which is for editing it.

In Chapter 13, you learned how a subform control can show linked records in one place (The Subform Control). However, subforms don’t always give you enough room to work. Depending on the way you work and the sheer volume of information you’re facing, you may prefer to show the related records in a different place. You could add a button to your form that pops open another form with the linked records. The trick to making this work is using filtering in the second form so that it shows only the related records. Figure 14-21 and Figure 14-22 show an example with the Cacophoné Studios database.

Figure 14-21. The Classes form shows a list of classes. Click “See Students in this Class” to open a second form Figure 14-22).

Figure 14-22. The StudentEnrollments form homes in on the students in just one class.

You can create the two forms that appear in Figure 14-21 and Figure 14-22 without much effort. The tricky bit is creating the “See Students in this Class” button.

Here’s what you need to do to wire up a button that opens another form to show related records:

You can use a similar technique to allow navigation in reports. If you want, you can create a way to jump from one report to another, related report. In fact, the macro you need to create is almost identical to the one in the previous example.

Usually, Access experts use this technique to start with a general report and let people click their way to a more detailed report that highlights part of the data. Figure 14-24 and Figure 14-25 show an example.

Figure 14-24. The first report (TotalsByCustomer) shows all the customers and their total orders. Click a single customer, and then Access launches the more detailed report shown in Figure 14-25.

Figure 14-25. Here’s the CustomerPurchases report that profiles the selected individual’s spending habits. A string-building expression =“Products Purchased By” & [FirstName] & “” & [LastName] puts the current customer’s name into the title.

To create this navigation, you need to begin by creating a text box that looks like a hyperlink. (You can’t use the bonafide hyperlink control, because it displays only fixed, unchanging text. Instead, you need a way to display a field’s content as a link—in this example, the customer ID.) You can then create a macro that, when the text field is clicked, springs into action to move you to the new report. This macro’s job is to open the detailed report that you want, and then apply filtering so that only the related records appear.

You can easily format the text box. You can select any control, and then change its color, font, and so on, using the commands in the ribbon. However, you don’t even need to perform this work. That’s because every text box has an odd property named Is Hyperlink—set this to Yes, and the text box morphs into a blue underlined piece of text, which is just what you want.

Once that’s out of the way, it’s time to add the macro you need. You can use the following steps with the Boutique Fudge database (included with the downloadable content for this chapter) to add the link to the TotalsByCustomer report (which opens the CustomerPurchases report).

As always, you can try this example for yourself by using the sample databases for this chapter.