In Chapter 12, you learned that a report’s divided into separate sections (like a report header, a details section, a report footer, and so on), each of which appears in a specific place. The same’s true for forms. However, newly created forms start life with only one section: the details section, which defines the content for each record.

If you want to add a title or logo at the top of your form, or some sort of summary information or message at the bottom, you’ll want to include a header and footer section. To add these elements to your form, right-click anywhere on the form’s surface, and then choose Page Header/Footer.

When working with form sections, remember to keep them small (as shown in Figure 13-2). Each form section should be just large enough to fit the content you’re displaying. If you create an oversized form with a lot of blank space, the results look unprofessional. You’ll get unnecessary scroll bars on your form’s sides, which let you scroll off into nothingness.

Figure 13-2. Even though this form easily fits all its fields in the display area, it still has scroll bars. If you switch to Design view, you’ll see why—the form’s wider and longer than it needs to be.

You first learned about controls—graphical widgets like labels and text boxes—when you created advanced reports in Chapter 12. Access gives you the same ability to use controls with forms. In fact, you use the same ribbon section to add them. However, many of the controls that didn’t make much sense with reports really shine with forms.

One of the simplest and most useful controls is the humble label. Using the label, you can add formatted text anywhere on your form. You could choose to use labels to highlight additional instructions, as shown in Figure 13-3.

Figure 13-3. Use labels to add helpful instructions (or cheeky commentary) to your forms. Line and rectangle controls add a little polish.

To add a control, follow these steps:

Later in this chapter, you’ll consider how to create some popular form designs with controls. But first, it’s worth taking a quick overview of all the controls on the rib-bon so you can see exactly what’s available (and what isn’t). Table 13-1 introduces you to every member of the control family.

By now, you’re probably comfortable working with controls in Design view. Here’s a quick refresher if your memory needs a little jump start:

Figure 13-4. To move multiple controls at once, start by clicking somewhere on the form surface. Drag a selection box around all the controls you want to move, as shown here. Then, once all the controls are highlighted, drag any one of them. All the controls move as a unit. (You could also hold down Shift, and click each control one by one.)

If your controls aren’t contained in a layout, it can be difficult to get them neatly arranged. In an effort to help, Access supplies some shortcuts that can line up rogue controls and iron out minor variances. The following sections provide a few useful tips for using these features.

Aligning controls

If you have a group of controls that needs to be neatened up, select them all (by drawing a selection box, as shown in Figure 13-4), right-click the selection, and then choose an option in the Align submenu. Use the ever-popular “left” option to line all the controls up along their left edges. You can also line up controls on the right (see Figure 13-5), top, or bottom edges.

Figure 13-5. Top: These controls look messy. Bottom: Even though the controls aren’t in a layout, you can line them up properly using the Align options. Here, someone used the Align → Right command to pull them together against the right edge.

Sizing controls

If you have controls of different sizes, then you can tell Access to make them all the same size. Select them all, right-click the selection, and then choose an option from the Size menu. Use To Widest to make all the controls as wide as the widest one of the bunch (see Figure 13-6). Alternatively, you can shrink controls down by choosing To Narrowest, or change their heights with To Tallest and To Shortest.

Figure 13-6. The To Widest command makes all these text boxes (top) the same width (bottom), which creates a cleaner and more visually pleasing form.

Ordinarily, your controls have a fixed, unchanging size. This characteristic lets you precisely arrange a large number of controls next to each other. However, fixedsize controls also have a downside. If you resize the Access window to make it very large, then the controls can’t use the extra space. Conversely, if you make the Access window very small, then you’re sure to cut off part of your form. In other words, fixed-size controls make for easy design, but they’re inflexible.

Most people don’t worry too much about these limitations. They design their forms to fit comfortably on an average-sized screen (see the box “How Big Is Your Screen?” in Section 13.1.5.1). However, if you have one or more fields that display a large amount of data—like a memo field (Section 2.3.1.1) that’s chock-full of text—you might want to get a bit more ambitious.

Access 2007 introduces a feature called anchoring, that lets you create controls that can grow to fill extra space when the Access window’s resized. Anchoring’s a little tricky to get right, but if you have huge text fields, it’s worth the trouble.

Essentially, anchoring lets you attach a control to the sides of the form. As a result, when the form changes size, the control’s dragged to a new position or resized. Figure 13-7 shows an ordinary form that uses standard anchoring settings. Nothing happens when this form’s resized.

Figure 13-7. Ordinarily, all controls are anchored to the form’s top-left corner (top). If the form window changes size, nothing happens to the controls, because the top-left corner never moves (bottom).

If you anchor a control to a form’s right side, it’s a different story. As the form’s resized wider, the control hugs the right side, moving to a new position. Similarly, if you attach a control to the bottom of the window and make it taller, then the control keeps close to the bottom, no matter how small or large the window becomes. The really exciting bit’s what happens when you anchor a control to opposite sides. In this case, its position doesn’t change, but its size does. If you anchor a control to a form’s left and right sides, then the control widens as the form widens. Figure 13-8 shows how life changes when you anchor your control to different sides.

Figure 13-8. This form includes controls that are anchored to different sides. The ID and Email fields are anchored to the top right, and the Notes field is anchored to the bottom right. As the form grows, the controls change position.

Theoretically, you can use anchoring to create all kinds of bizarre effects. You could anchor controls to all different sides of the form, so they move and overlap as the form changes size, scrambling the form in complete confusion. More realistically, people use anchoring to achieve two effects, which are demonstrated in the following two sections.

When you’re using a form to edit a record, you need to skip from one field to another. You can jump anywhere you want by using your mouse, but hard-core keyboard jockeys don’t want to waste time raising their fingers from the keys. Here’s where the Tab key comes into the picture.

You probably already know that the Tab key lets you move from one control to another in any Windows application. The Tab key also works in the datasheet, letting you skip from one column to the next. So it should come as no surprise that the Tab key also works in your forms.

The first time you press Tab in a form, you may be in for an embarrassing surprise. If you’ve spent a fair bit of time fiddling with your controls and rearranging them, then the Tab key doesn’t necessarily take you to the control you expect. Figure 13-11 illustrates the problem.

Figure 13-11. You’d expect that you could press the Tab key here to jump from the FirstName field to the LastName field. But try it and you wind up in the Country field instead, halfway down the form.

Getting a form straightened out so that the Tab key moves from one control to the next in an orderly fashion is called setting the tab order. Essentially, every control that supports tabbing has three important properties (which you can find in the Other tab in the Property Sheet). These properties are:

Every time you add a new control, Access gives it a new, higher tab index. Even if you drop a new control at the top of the form, Access puts it at the end of the tab order. To fix this problem, you could select each control in Design view, and then change the Tab Index setting by hand. However, a much less time-consuming alternative lets you set the tab order for the entire form in one go. Here’s how it works:

In a database, almost every piece of information’s subject to change. However, that doesn’t mean people should have free range over every field.

Suppose Boutique Fudge creates a form named CurrentOrders that lets people in the warehouse review outstanding customer orders, sorted by date. The warehouse personnel need to review each order, pack it up, and then ship it out. The only change they need to make is to update the order status (to indicate when it’s been sent out), or add a record to the shipment log. Other details, like the order date, the order contents, and the customer who’s receiving the order, should be off limits. The warehouse people have no reason to change any of this information.

Forms are powerful tools in scenarios like this, because they let you prevent changes in certain fields. That way, there’s no chance that a misplaced keystroke can wipe out a legitimate piece of information.

Every bound control (a control that displays a field from your table) provides the following two properties that you can use to control editing. You can change these properties using the Property Sheet in Design view (Section 11.4).

In Chapter 4, you learned how to prevent errors from creeping into your tables by using validation rules, default values, and input masks. This bulletproofing’s an essential part of database design.

However, validation rules don’t help in some situations because the rules apply sometimes, but not always. You don’t want the sales people at Boutique Fudge to enter a new order with an old date. Clearly, that’s a mistake—a new order should receive today’s date. To try and stamp out the problem, a clever database designer like yourself may use the following validation rule on the OrderDate field:

	<=Date( )

However, a few weeks later you discover that the catering department neglected to enter the information about their orders on time. For record-keeping purposes, these orders need to indicate when the order was originally placed. So you need to remove your well-intentioned validation rule before you can enter these records.

It turns out situations like these abound in real life. Fortunately, there’s a way to handle this scenario without giving up on validation. The trick’s to place the validation in the controls on the form. That way, different forms can use different validation rules. If you want to make completely unrestricted changes, then you can edit the data directly using the datasheet for the table.

If you plan to move the validation out of your tables and into your forms, then you’ll be interested in the following control properties, which you can tweak in the Property Sheet:

An expression’s a formula that manipulates some information, like numbers, dates, or text, and displays the final result (see Figure 13-13). Often, expressions perform calculations with field values. You’ve used expressions before to crunch the numbers in queries (Chapter 7) and reports (Chapter 10), and now you’ll put them to work in your forms.

To create an expression, follow these steps:

One of the control world’s unsung heroes is the tab control, which lets you present large amounts of content in a limited space. The tab control’s trick’s the way it lets you organize this content into separate pages. You can see only one page at a time, and you choose which one by clicking the corresponding tab (see Figure 13-14).

The tab control isn’t all good news. Its main drawback’s that you need to use extra clicks to get from one tab to another. For that reason, the tab control isn’t a great choice in forms that you’ve set up to create new records. In those instances it’s better to streamline the new record creation process and have all the controls on one page, so you can move through them quickly. A tab control makes most sense in forms that are primarily designed for editing or reviewing data. If this data can be subdivided into logical groups, and if editing tasks often involve just one group, then the tab control’s a good choice.

Figure 13-14. It makes sense to put customer address information on a separate tab, because all these fields make up a logical group of related information.

To use a tab control, follow these steps:

You can drag controls from the rest of your form onto a page, or you can add new controls from the ribbon. Either way, remember to select the tab you want first, and then add the controls you need. Even in Design view, you can see only one page of a tab control at a time.

Links are the less powerful cousin of buttons. Whereas command buttons (Section 13.2.7) can perform almost any action, links are limited to exactly two tasks:

To create a link, follow these steps:

There are two list controls in Access forms: the list box and the combo box. The difference is that the list box shows several items at once (depending on how large you make it). The combo box shows just one item—to see the list, you need to click the drop-down arrow.

Access gives you two ways to use list controls:

Using lists for navigation is a true Access power trick. If you often look for a record using the same criteria (like if you hunt down products by name or employees by social security number), this technique’s much faster than using the navigation buttons or filtering the records.

Here’s how to create a list for navigation:

The last control you’ll consider is one of the most powerful. Command buttons let you trigger just about any action, like opening a new form, printing a report, or polishing off last year’s taxes. (All right, some tasks are more difficult than others, but if you’re willing to hunker down with some Visual Basic code, almost anything’s possible.)

When you add a button to a form, Access launches the useful Command Button wizard, where you can choose the action you want from a list of ready-made choices. The Command Button wizard then helps you build a macro (see Chapter 15) that does whatever you requested.

The choices in the Button wizard provide a good menu of possibilities. Some Access fans find that they can do almost everything they want to do by just using buttons and the wizard. Other people eventually want to do something more original, in which case they need to create their own macros or write custom code (tasks you’ll tackle in Part Five).

The following steps lead you through the Button wizard:

Access is intelligent enough to notice relationships when you create a new form for a parent table. To see what this ability means in practice, select a table that’s the parent of another table. You can use the ProductCategories table in the Boutique Fudge database, because every category’s a parent record that’s linked to one or more child records in the Products table. (You can also use the Customers table, because customers are linked to orders, or the Orders table, because orders are linked to order items. To try this out, use the Boutique Fudge database that’s included with the downloadable content for this chapter.)

Figure 13-20 shows what happens if you select the ProductCategories table, and then choose Create → Forms → Form. Access creates a form that displays the records you expect (the categories), and the linked records in the child table (in this case, the products).

If your table’s the parent of more than one child table, then Access shows only records from one table. It chooses the first relationship it finds. If this relationship isn’t the one you want, don’t worry—it’s easy to change once you understand how the subform control works. The next section has the inside scoop.

Access shows linked records using the subform control. You can add the subform control to any form to show linked records. It’s available in the ribbon’s Form Design Tools | Design → Controls section with all the other controls. If you add it by hand, then Access prompts you to pick the table you want to show.

Three properties determine what the subform control shows. First, the Source Object property identifies the object in the database that has the related records. You can choose an existing table, query, or form.

Figure 13-20. This form lets you browse through the different product categories. Each time you move to a new category, a mini datasheet on the form shows the linked product records. Using this form, you can edit products and product category information.

The next two properties—Link Master Fields and Link Child Fields—let you define the way the two tables are related. The master field’s the field in the form, and the child field’s the field in the source object. In the product category example, the master field’s ID (in the ProductCategories table) and the child field’s ProductID (in the Products table). Once this link’s defined, Access knows how to filter the subform. It looks at the master field, and displays only records that have the same value in their child fields. In Figure 13-20, Access shows only the products in the current category.

Usually, the master field corresponds to the parent table, and the child field’s in the child table. However, you can reverse this relationship. You could create a form of products that includes a subform that shows each product’s matching category. When you use this approach, the subform includes only a single record (because only one parent’s linked to any child).

Now that you understand how the subform control works, you can add it to your forms with wild abandon. There’s no reason you can’t add several subforms to show a whole collection of related data at once. If you’re creating a form for the Customers table, then you could display two subforms—one for the orders made by that customer, and one for the payments. You just need two subform controls with different data sources.

When you set the Source Object property to a table or query, Access always displays the linked records in a mini datasheet. If you’re intent on customizing every last piece of your form, you may not want that behavior. Interestingly, Access lets you control exactly how linked records are shown, if you do a little more work.

The trick’s to set the Source Object to the form that you want to show in the subform control. Then, the form appears in its Default view mode, which is whatever that form’s Default View property is set to (see Section 12.3.5). You can show linked records in a tabular or stacked form. Figure 13-21 shows an example.

Figure 13-21. Thanks to the magic of subforms, this window actually shows two forms at once: ProductCategories_ WithSubform and ProductsInCategory_ Subform. In this example, the subform uses Continuous Form view (Section 12.3.5), so it shows a list of all the matching products. To see the rest of the products, you need to use the second set of navigation buttons (circled).

Depending on the effect you’re trying to achieve, you may already have a suitable form lying around ready to use. If you’re designing a form for the ProductCategories table, you can use the form you created for the Products table in a subform control. However, you’ll often want to use a completely separate form so you can customize it just the way you want. In the products table example, you may want to show products differently in the subform than they appear in their own dedicated form. After all, there’s less space available when you use the subform control, so you may choose a more compact format and leave out the report header altogether.

Sometimes, try as you might, there’s no way to fit everything in the small subform area of a form. In this case, you have two options: Try to rearrange your subform to make it more compact, or use two separate forms. Section 14.3.1 in Chapter 14 shows how you can use navigation and filtering to show related records in a separate form.