Access 97/2000: Forms and Reports

• Forms and Reports
• Form/Report Structure
• Changing Section Sizes
• Types of Controls
• Getting Started with Forms
• The Toolbox and Controls
• Adding Controls to a Form/Report
• Selecting, Moving, and Sizing Controls
• Getting Started with Reports
• Mailing Labels

Forms and Reports

When you build a database, the most fundamental concept is to understand what each part of it does. In Access, you have tables that contain the data, queries that allow you to select and organize your data, and forms and reports that allow you to present your data to the world. Forms and Reports function as the front-end to your tables and queries. They are based on an underlying table or query and offer more flexible formatting options.

Like paper forms, Access forms collect and organize information. Forms give you a way to enter data into your database, display the data for review, and print it out. Forms are designed to make on-screen data entry and retrieval easier, as well as simplifying movement around your database application. With Access, you can present information in just the way that you want by combining text, pictures, lines, boxes, and color to create a fully interactive on-screen environment.

A Report provides a way to retrieve selected information that you have stored and present that information. Examples of reports that we use every day include mailing labels, invoices, receipts, and sales summaries. You can base a report on either a table or a query; reports are designed to be printed out rather than viewed on a computer screen, so they need to be carefully planned to make sure that you use them most effectively and meaningfully. Since you will be using reports to make presentation quality documents, they need to possess enough design flexibility to allow you to get the right message across to your audience. Fortunately, Access also comes with several pre-planned report layouts that you can set up in a few minutes using Report Wizards.

Both Forms and Reports share common building blocks called controls. All of the information stored in your tables and manipulated in your queries is contained in fields. All of the information on a form or report is contained in controls. Every item on a form or report is, then, a control. Controls are articles on a form or report that display data, perform actions, or enhance the object in question.

Form/Report Structure

You can increase a form or reportís effectiveness by knowing how it is put together. The basic section divisions are Form/Report Headers and Footers, Page Headers and Footers, and Details. You can adjust the size of sections in your form, change section properties, and add whatever controls you want. If you want to use a form to both enter and print data, you can add a header that appears only in Form View and another that appears when the form is printed.

Form Headers and Footers appear at the top and bottom of every page when the form is displayed in Form View. When a form is printed, the Form Header appears at the top of the first page, and the Form Footer appears at the bottom of the last page. Page Headers and Footers display information such as title, column totals, or column headings at the top or bottom of every page. Page Headers and Footers appear only on printed forms and reports, never onscreen. Details display either one record or as many records as will fit at once on the screen or page. There is one detail for each record in your underlying dataset.

Changing Section Sizes

You can increase or decrease the height and width of a section. In Design View, use the rulers at the top and left of the form to determine how much space you want. You can change the height of sections independently, but a form has only one width. When you change the width of a section, you change the width of the entire form.

To change the size of a section, place the mouse pointer on the bottom or right edge of the section you want to adjust. The pointer will change from the standard white arrow to a black, double-headed arrow when you are on the edge. Then, drag the edge until the section is the size you want.

Types of Controls

Forms and Reports draw their contents from Tables or Queries. Forms and Reports use graphical objects, called controls, to link the object back to the underlying table or query. There are three types of controls in Access: unbound, bound, and calculated.

• Unbound. An unbound control allows you to display information that is not stored in the database-- the title of the form or a descriptive label. This kind of control does not have a data source. You use unbound controls to enhance the presentation and clarity of your form by adding titles or descriptive labels, lines, and pictures.
• Bound. A bound control is always linked to information stored in the form's underlying table or query. A text box is always a bound control. You could display a person's name from the Name field in a Mailing List table. The text box would get the name from the Name field because you have bound it to that field. You can also use a bound control to display a picture if it is stored in the underlying table.
• Calculated. Calculated controls are a subset of bound controls, but they do not present information as it appears in a table or query. Instead, they operate on data taken from the table or query by using an expression--a combination of operators such as (+, -, and =), control names, field names, and constant values. For example, the following expression would combine a table's FirstName and LastName fields to present them both together:


 =FirstName + ì î & LastName

Note: Access' on-line help has more detailed information on building expressions.

Getting Started with Forms

You create a new form by selecting Form in the Database Window and clicking on the New button. You must then select a table or query to base the form on. At this point, you can choose between using the Form Wizards or working from a blank form.

The Form Wizards

These are the most helpful Wizards in Access because they do a lot of the basic formatting tasks and allow you to concentrate on refining special features. There are four basic types of wizards to choose from:

• AutoForms. These forms automatically display every field in the underlying table in either a datasheet, columnar, or tabular display. They are accessible from the New Object button on the toolbar. AutoForm: Columnar displays the values in one column with each field on a separate line. AutoForm: Tabular displays several records at the same time in rows and columns. AutoForm: Datasheet displays the values from the underlying table/query in datasheet view.
• Form Wizard. This is the most flexible wizard available. It will ask you to select all the fields that you want to include on the form and then prompt you to choose the layout you think will work best. Unlike the AutoForm options, it requires you to make decisions; however, it is also the only option that allows you to work with fields stored in multiple tables to create a main form and a subform. A Main/Subform form shows a one-to-many relationship between the data in the main form and the data in the subform. The main form is in a single-column format, and the subform is either in a datasheet or tabular layout.




• Chart Wizard. This form displays the selected data as a graph by using Microsoft Graph.
• Pivot Table Wizard. This wizard creates an Access form with an Excel Pivot Table. A Pivot Table is an interactive table that can summarize large amounts of data with the format and calculation methods you choose.

If you choose not to use the Form Wizards, then you will start with a blank form. You then need to place all of the desired fields on the form and decide what kinds of controls you need.

The Toolbox and Controls

The Toolbox is a special kind of toolbar that contains buttons allowing you to select the type of control that best suits your needs. When you need to create controls, the toolbox gives you quick access. Since the same toolbox is used for designing both forms and reports, you may not find all tools useful for all forms.

The toolbox should be visible when you enter the form's Design View. If it is not visible, then you can turn it on by selecting Toolbox from the View menu (or by selecting the Toolbox icon on the toolbar). The toolbox controls belong to five major categories: text display and entry, presentation of yes/no options, list boxes, graphics and subforms, and layout enhancement tools.

Text Boxes and Labels

Text boxes and labels are the default tools for looking at information in an Access form or report. Text boxes display the information either entered in a field or calculated on the form, while the label is a caption or descriptive text. All labels are unbound controls, but they can be attached to other controls. Every text box you create on your form will automatically have an attached label. You can use labels to create titles and headings.

Yes/No Options

The toolbox contains four different kinds of controls for displaying Yes/No values. Check boxes and option buttons (AKA radio buttons) when turned on or selected indicate a Yes value. A check box contains an X when turned on, while an option button has a dot when selected. Toggle buttons indicate a choice between two values. You can change the face of a toggle button to display your own text. An option group shows which option was selected from several alternatives. Option groups frame a set of either check boxes or option buttons. Note: only one option in the field can be selected for each record.

Drop Down Boxes

Access' toolbox contains two types of drop-down boxes: a List box and a Combo box. These controls display a series of values from which you can select. They are most useful when entering data. You can take the values for the boxes from existing values in a table or query or from a list you specify using the control's properties. The most effective way to take values from an existing table is to create a query that extracts all the unique values from a table and sorts them.

Graphics Objects and Subforms

Forms can be enhanced by including different kinds of graphics objects or including other forms inside the main form. The Graph tool allows you to create a graph in Microsoft Graph and place it on your form. The Subform/Subreport tool lets you insert a pre-existing form into your current form. The bound and unbound object frames allow you to insert onto your form pictures or other OLE objects.

Layout Enhancement Features

You can improve the appearance of your forms by including lines, boxes, or page breaks. The line and box tools are designated sections of your form that you want to separate visually from other sections. Page breaks allow you to control how much information goes on one page of each form.

Adding Controls to a Form/Report

Once you have created a form or report, you need to learn a few basics about working with controls in Access to make it look great. First, for the best way to create and position controls on your form, use both the Toolbox and the Form Design Toolbar. The Toolbox, the Field List for the underlying table or query, and the Properties box can be displayed for easy access. You can turn all of these options on by selecting the appropriate buttons from the Form Design toolbar or from the View menu.

The best way to create a new control is to combine these two actions into one. When both the Field List and the Toolbox are displayed, you can select the control type that you want to add in the Toolbox and then drag the field you want bound from the Field List to the form's design grid.

Selecting, Moving, and Sizing Controls

Access has easy drag-and-drop movement of controls in forms and reports. You select a control by clicking on it; once a control is selected, you should see ìhandlesî appearing around it. These are small boxes that you can use to resize and move the control. You can also select several adjacent controls by ìlassoingî them. To lasso, you need to click in an open space on the form, then drag the pointer through all of the controls that you want to select. Make sure that you start lassoing from a point on the form that will allow you to drag through all of the desired controls without intersecting any others. To select controls that are not adjacent, begin by clicking on the first control and select subsequent controls by clicking on them while holding down the <Shift> key. Once selected, you can move a control and its attached label anywhere in the form by dragging it to a new location.

If you place the cursor on a controlís border, you will see one of three options. An open hand means you can move both the control and its attached label. A hand pointing a finger means that you can move either the selected control or label, but not both. A double-headed arrow means you can re-size the control. You then move or re-size by holding down the left mouse button and dragging the control to its new location or size.

Getting Started with Reports

Like all Access objects, you create a new report by selecting the New button in the Database Explorer . You are then presented with several options that essentially boil down to using Wizards or designing your own report from scratch. If you choose to use Wizards, you will be prompted to enter information to help Access determine how to format your report. You can also select AutoReport: Tabular or AutoReport: Columnar Wizards , which create the appropriate type of report automatically without prompting you for any information. These options can be helpful but always default to a single-column report displaying all the fields in the underlying table or query. In all cases, a report always needs to be based on an underlying table or query. If you choose to start with a blank report, then you will have to format everything on your report.

Report wizards include five standard report formats: Single Column, Groups/Totals, Mailing Label, Summary, Tabular, and Chart. These wizards take care of some complex preliminary tasks that you must carry out in designing an effective report. The report wizards let you choose from numerous report styles, transposed onto three basic layouts: columnar, tabular, and justified. These styles and layouts control report layout, fonts, positioning, and colors. They demonstrate effective presentation techniques and eliminate some of the grunt work needed to produce a report.

Mailing Labels

The Label Wizard is quite possibly the most useful report-building wizard included with Access. It allows you quickly and easily to construct mailing labels based on any of the standard Avery label sizes. You start the Label Wizard by choosing it in the New Report dialog box. Make sure that you choose a table or query on which to base the labels and click on the OK button. Then, choose the appropriate label size and style from the list provided. Add the fields to the sample label that the wizard provides for you, remembering that field names are often longer than the field contents if the spacing seems odd. Finally, finish the wizard and make any necessary changes in the reportís design view. The label will be small and simple. The label wizard will have set up the appropriate column and row heights so that you can print more than one detail section on a single row.