Building Great Forms and Reports

Access provides a design and development environment that gives you extensive control over the appearance and functionality of forms and reports. The tools you use for either task are remarkably similar. The differences typically reflect the different design goals of forms and reports, as explained here:

• A form is a formatted database object, generally used to display one record at a time in an onscreen window. Forms are most commonly used to create convenient fill-in-the-blanks windows for entering or editing data; in this case, you use a table or query as the data source for the form.

• Access reports typically organize data in a format suited for printing or publishing. Although you can also use a form to view data onscreen, reports are better suited for this task and often represent the most important end product of a database.

When designing a form or report, you start by specifying a data source—one or more tables or queries, or a statement written in SQL—and position controls on a design grid. Although you can create a report or form from scratch in Design view, using a wizard is often a better starting point. The wizard produces the basic structure, and you then open the form or report in Design view to make detailed changes to its content and appearance.

As with other database objects, Access provides several ways to create a form or report. After selecting Forms or Reports from the database window, click New to open the New Form or New Report dialog box. The options shown here let you create a new form or report by opening a blank form in Design view, or by choosing wizards and AutoForm/AutoReport options that enable you to create a default form with a single click.

To open an existing form or report in Design view, select it from the database window and click the Design button. If the form or report is already open, click the Design button to switch into Design view.

Design 101: Working with Controls

The building blocks of any form or report are objects called controls, which include text boxes, labels, option buttons, lists, command buttons, toggles, and other familiar Windows interface elements. Controls have their own property settings, as do individual sections of the form or report; by changing the settings of these properties, you can modify the appearance and content of the form or report. Controls can take any of three forms:

• Some controls are directly tied to a field in a table or query. In the peculiar jargon of Access, these are called bound controls. When you enter data in a control that is bound to a particular field, Access adds the data to that field; when you view data by using a form or report, Access checks the Control Source property for each control to see which data it should display. Figure 34.5 shows the Properties dialog box for a text box bound to the ItemNumber field in the underlying table.

  
  

  Figure 34.5.

  

  
  

• Some controls are unbound—that is, not tied to any data source. For example, a line, box, or freestanding text label is an unbound control.

• When you enter an expression in the Control Source property box, Access creates a calculated control. The expression =[SupplierPrice]*2, for example, multiplies the contents of the SupplierPrice field by 2 and displays the result.

→ For an overview of Access expressions, see "Using Expressions in Database Objects".

When you open a form or report in Design view, you can change the font, font size, color, borders, and other formatting properties of any control. In Design view, Access lets you position controls on a grid for precise alignment. You can also group and align controls.

When you work with a form or report in Design view, three interface elements are essential:

• Use the Toolbox to add new controls or change existing controls. You can customize the Toolbox buttons just as you can any other Office toolbars; to toggle it on or off while you work, click the Toolbox button or choose View, Toolbox.

• The Field List displays a list of all the fields in the source query or table, which you can use to add new controls. To show or hide this list in Design view, click the Field List button or choose View, Field List.

• To adjust the appearance or behavior of a control, section, or the form itself, open the Properties dialog box. You can keep the Properties dialog box open while you work; as you select different objects, the properties displayed in this dialog box change to reflect the available choices.

Adding a New Control to a Form or Report

If you drag a field name from the Field list onto a form or report, Access automatically creates a text box control bound to that field. If you click another Toolbox button first, and then drag a field onto the form, Access launches a wizard that creates the control type you selected. Figure 34.6, for example, shows one step of the Option Group Wizard. Follow the wizard's instructions to define the data source and behavior of the control.

The Toolbox contains buttons for common controls you might want to add to a form. By positioning the mouse pointer over a button in the Toolbox, you can view a ScreenTip that shows the name of the control itself. Some of the more common and useful controls include the following: check boxes, which let users enter data in a Yes/No field or an option group; combo boxes and list boxes, which let users select from lists of data items; and labels, which add descriptive text to a control or a form or report.

Positioning Controls and Labels

When you use the Form Wizard to build a form, the default type for all controls is a text box with a label attached to its left. In some cases, however, you'll want the labels to appear above the text box, and you might want to change the position, alignment, size, or grouping of controls on the form. After you learn the secrets of working with Access controls, you'll find it easy to position controls precisely where you want them. It does take some practice, however.

Access displays handles—small black rectangles—around the outside of a selected control, as shown in Figure 34.7; these handles are a visual indication that you've selected the control. The eight small black squares are size handles—you'll find one on each corner and one in the middle of each side of the selected control. When you position the mouse pointer over a size handle, it takes the shape of a double-headed arrow, at which point you can drag the handle in any direction to change the size and shape of the control itself.

The large black square at the upper-left corner of a selected control lets you move the object to a new position within the form. When you drag this handle, the mouse pointer takes the shape of a hand with one finger pointing up; as you move the selected control, you see only its outline, making it easier to position on the form grid. When you release the mouse button, the control itself moves to the location you've selected.

For the most part, every control you add to a form actually consists of two controls: the bound or unbound control (text box or combo box, for example), and a matching label. If you know the techniques, it's easy to position these controls correctly:

• Use the large square in the upper-left corner of either the control or the label to move either one independently. This technique is effective if you want to move a label from the left of a text box so that it sits above the control.

• To move both the control and its label at once, point to any border of the control or the label, until the mouse pointer takes the shape of an open hand. Drag to position the control-label combination in its new location.

To delete any control, select it and press the Delete key. If you select a control, Access also selects its label; if you click the label itself, Access does not select the matching control.

Grouping and Aligning Controls

When working with a form or report in Design view, you can group, distribute, and align controls and other objects, just as you can in the Office drawing layer.

→ For a full discussion of basic techniques for working with the drawing layer in Office programs, see "Using Office Drawing Tools".

In all cases, you start by selecting all the controls you want to work with simultaneously. To do so, hold down the Shift key and click each selection in succession. Or use the mouse to draw a rectangle around a group of objects and select them all at once. Say you've quickly added six new fields to a form, and now you want to tidy up the collection:

Making Forms Easier to Use

A well-designed form makes data entry easier and more accurate, especially when you want other people to enter data into a database. By limiting the data the user sees, carefully arranging input boxes, and providing explanatory text, you can guide the user through the data-entry process.

A form can include as many as five sections. The data itself typically appears in the Details section; in addition, each form can have up to two headers and two footers, with one header/footer combination for the form itself and another for individual pages. Use the View menu to hide or show headers and footers.

Simple forms generally show the contents of one record at a time, but you can also design a form that includes a subform, which displays information from a related table or query. If you choose a table that includes a subdatasheet and then create an AutoForm, Access adds a subform automatically. Using this type of form, you can scroll through groups of records, or search for information by using filters and other search tools. Figure 34.8, for example, shows a form and subform combination in which the main form is bound to the Suppliers table, with a subform bound to the related Products table. Note the two sets of navigation buttons at the bottom of the form. Use the Next and Previous record buttons for the main form to jump through the Suppliers table; use the navigation buttons within the subform to move through the list of products for each supplier.

For special-purpose forms, one powerful area to explore is the Properties dialog box for the form itself. This list contains settings that affect important characteristics of a form's appearance and behavior. For example, in the Data tab (see Figure 34.9), you can set three properties that determine whether the form is available for reading alone or can be used for editing, deleting, and appending records.

When you create a form from a single table, the Allow Edits, Allow Deletions, and Allow Additions properties are set to Yes. As a result, the user can add, edit, and delete records using nothing but this form. Depending on who will be using the form, you might want to restrict this capability. Change the property setting to No on one or more of these important properties if you want to restrict the user's ability to revise the data.

Making Reports Easier to Understand

Access reports are organized into horizontal sections that are laid out in a specific order. Understanding how to work with each section is a crucial step in designing an effective report. Figure 34.10 shows a basic report that illustrates some commonly used sections.

• The Report Header and Footer appear at the beginning and end of the report. A report header often includes the title of the report and a calculated control that contains the expression =Date() to display the date the report was printed. Report footers often contain grand totals or averages for the data within a report. To hide either section, change its Visible property to No.

• The Page Header and Footer appear at the top and bottom of each page, even if the Detail section is a continuation of data from the previous page. Page headers are commonly used for column headings, so readers can follow a lengthy list, and page footers are useful for dates and page numbers.

• Group Header and Group Footer sections appear automatically when you define grouping and sorting options for a report. By placing calculated fields in either of these sections, you can display summaries of the data within each group.

• The Detail section includes fields from each record in your data source. Each field in the Detail section appears once for each record in your data source, making this the right place to specify how you want a list to appear.

Grouping and Sorting Records in a Report

In complex presentations of Access data, groups are the essence of report design. A group defines how records are organized in the output of a report, and how information can be summarized in statistical calculations, such as totals and averages. In the Design view of a report, groups are represented by a hierarchy of Header, Detail, and Footer sections.

Using the Report Wizard, you can choose the fields you want to use for grouping in a report, and you can specify how you want groups to be summarized by specific calculations. To adjust grouping and sorting options, however, you need to flip into Design view and click the Sorting and Grouping button. This option displays a dialog box such as the one shown in Figure 34.11, which gives you control over virtually all grouping options.

For example, in a report that you plan to use as a product catalog, you might want to group records by Supplier, in alphabetical order, with the product names listed under each supplier's name, also in alphabetical order. In this example, you would include the Supplier's name in a Group Header, with the ProductDescription, SupplierPrice, and UnitsInStock fields in the Detail section; optionally, for an inventory report, you might add a calculated field in the Group Footer section, using the expression =SUM([SupplierPrice]* [UnitsInStock]).

To tell Access you want to sort by Supplier, and then by ProductDescription, open the Sorting and Grouping dialog box and use the drop-down arrows to select those two columns in order. By default, Access offers to sort in Ascending order; if you want to use Descending order, adjust the setting here.

By choosing these two fields, you tell Access that you want to sort records in this order, but you need to go one extra step to group records: In the Sorting and Grouping dialog box, select the row that contains the Supplier field and change the Group Header property to Yes. If you want to add a footer for this section, change the Group Footer property to Yes as well.