In Chapter 11, you learned that a report is 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 is 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. An oversized form with a lot of blank space looks unprofessional. Also, you’ll end up with unnecessary scroll bars on your form’s sides.

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 is 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 11. 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 ribbon 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 jumpstart:

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. 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.

Note

Many of the commands you’ll learn about in the following sections for alignment, sizing, positioning, and anchoring, exist in two places. You can get them from the Form Design Tools | Arrange tab of the ribbon or by right-clicking a selection of one or more controls and looking in the pop-up menu. The menu approach is often faster, but the ribbon approach is sometimes more convenient for setting complex formatting.

Sizing Controls

If you have controls of different sizes, 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 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, fixed-size controls also have a downside. If you resize the Access window to make it very large, the controls can’t use the extra space. Conversely, if you make the Access window very small, 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. However, if you have one or more fields that display a large amount of data—like a Long Text field that’s chock-full of text—you might want to get a bit more ambitious.

Access includes a feature called anchoring, that lets you create controls that can grow to fill extra space when the Access window is resized. Anchoring is 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 is dragged to a new position or resized. Figure 13-7 shows an ordinary form that uses standard anchoring settings. Nothing happens when this form is 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 is 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, the control keeps close to the bottom, no matter how small or large the window becomes. The really exciting bit is 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, the control widens as the form widens. (This makes sense for a title at the top of your form with center-aligned text.) 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 FirstName 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.

Making a Single Control as Large as Possible

In the previous example, you saw how to use anchoring to make a control grow horizontally. You can also use anchoring to make a control grow vertically, but there’s a catch. In most forms, you have several controls placed one on top of the other. If you’re not careful, when a control gets taller, it starts blotting out the control underneath it. The solution is to make sure that only one control on the form can grow vertically. This control (probably a large field that’s stuffed with text) then expands to consume all the extra space. All the controls above this control must be anchored to the top of the form. All the controls underneath it must be docked to the bottom so they stay out of the way.

Here’s how to put this model into practice:

1. In Design view, shrink the width of the Details section so it’s just wide enough to fit your controls.

   As with all types of anchoring, extra space is your enemy.

2. Select the control that you want to grow vertically to get the extra space.

   Consider the form in Figure 13-10, which shows customers. In this case, it’s the Notes field that has the most text and would benefit most from the extra space. Even if you anchor the Notes field to both sides, you’ll still get only a bit of extra space. Better to use whatever blank space you can get at the bottom of the form.

3. Choose Form Design Tools | Arrange→Position→Anchoring→Stretch Down and Across.

   This action anchors your control to all four sides of the form: the top, bottom, left, and right. As a result, the control grows when the form is widened or heightened. If you want the control to grow vertically but not horizontally, you choose Anchoring→Stretch Down instead.

4. Select the first control under the control that grows vertically. Choose Form Design Tools | Arrange→Position→Anchoring→Bottom Left.

   This action anchors the control to the left and bottom sides. That way, as the form is heightened, the control drops down to make space for the one above.

   You could also use the Stretch Across Bottom option. In this case, the control is still anchored to the bottom, but it grows horizontally to fit the width of the form.

   Note

   In the previous example, you didn’t need to anchor the labels in front of every field, because they stayed fixed in place. However, in this example, you do need to use Bottom Left anchoring for all the labels that appear underneath the control that stretches vertically. (Otherwise, this label doesn’t line up with its value box.) You never use one of the stretch anchoring options with a label, because you don’t want your label to change size.

5. Repeat step 4 for each control underneath.

   If you forget a control, you see a telltale sign. When you resize the form window smaller, some controls overlap because the different anchoring settings don’t agree.

   Assuming you anchor everything correctly, you get the result that’s shown in Figure 13-10.

Figure 13-10. Now, when the form gets taller, the Notes field gets the extra room.

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, 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 is 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 to 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 by using the Property Sheet in Design view.

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 is 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 salespeople 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 recordkeeping 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 is 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, you can edit the data directly by using the datasheet for the table.

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

An expression is 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.

Figure 13-13. In this form, the expression = “You have” & [DueDate]-Date() & “days to finish” calculates the number of days between the current date and the project due date, and places that number in a complete sentence. You’ll see this information appear as soon as you type in a due date and move to another field. (You can get around this requirement, and force the fields to update themselves as you type, by using a tiny bit of VBA code that triggers a recalculation. Page 622 shows an example.)

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 is 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).

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.

The tab control isn’t all good news. Its main drawback is 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 is a good choice.

To use a tab control, follow these steps:

Links are the less powerful cousin of buttons. Whereas command buttons (Performing Actions with Command Buttons) can perform almost any action, links are limited to exactly two tasks:

To create a link, follow these steps:

Access includes a Web Browser control that lets you embed a web browser window in a form. This way, you can display web pages that are in some way related to your record data.

There are two scenarios where the Web Browser control makes sense:

Figure 13-16. The Books table includes a field named Amazon_ASIN, which stores an Amazon identification number. (For books with ten-digit ISBN numbers, the ASIN and the ISBN are the same.) When you view a book record in the BookDisplay form, the Web Browser uses the ASIN value to display the book’s Amazon product page, which includes up-to-date reviews and a sales ranking. You can find this example in the AmazonBooks database with the samples on the Missing CD page (www.missingmanuals.com/cds/access13mm).

Here’s how to use the Web Browser control to build an example like the one shown in Figure 13-16:

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 by using the same criteria (like if you hunt down products by name or employees by Social Security number), this technique is much faster than using the navigation buttons or filtering the records.

Here’s how to add a navigation list for an existing form:

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 is 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 readymade choices. The Command Button Wizard then helps you build a macro (see Chapter 15) that does whatever you requested.

The choices in the Command 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 5).

The following steps lead you through the Command Button Wizard:

Figure 13-21. This form includes several exotic button specimens that you can create by choosing an option from the Form Design Tools | Format→Control Formatting→Quick Styles menu.

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 is 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, use the Boutique Fudge database that’s included with the downloadable content for this chapter.)

Figure 13-22 shows what happens if you select the ProductCategories table and then choose Create→Forms→Form. (Don’t create a split form or a multi-item form. Access ignores relationships when you create these form types.) 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).

Figure 13-22. 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.

If your table is the parent of more than one child table, 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 it 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, 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.

The next two properties—Link Master Fields and Link Child Fields—let you define the way the two tables are related. The master field is the field in the form, and the child field is 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 ProductCategoryID (in the Products table) are linked. Once this link is 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-22, Access shows only the products in the current category.

Usually, the master field corresponds to the parent table, and the child field is 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 is 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, 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 is 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. You can show linked records in a tabular or stacked form. Figure 13-23 shows an example.

Figure 13-23. 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 (page 409), 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. 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. Linking to Related Data in Chapter 14 shows how you can use navigation and filtering to show related records in a separate form.