Database Fields on Forms and Reports

Forms provide a friendlier look for data entry to the main user of the database. As such, you will spend a great deal of time designing their look, tuning their functionality, and checking their efficiency. Even when creating a product from the Database Wizard, you will usually realize that various objects are lacking critical fields for the role of the object, or the database itself, as software, needs a few more objects.

As we have learned already, the quickest way to create forms and reports is by using the Database Wizard. Even if you create your database from scratch, you still can use the AutoForm or AutoReport to get very easily rendered and readily available objects. As it happens, the Database Wizard was configured to create only a limited number of objects. If you need additional objects, you can create your own. If you want to control everything or almost everything on the forms and reports, you will have to get into design.

To add fields to a form or a report, you must display the object in Design View. This gives you access to the Controls toolbox, which hosts some of the Windows controls used on forms and reports.

Field Insertion on Forms and Reports

Data on fields of forms and reports fall in three main categories:

• Fields that directly originate from a table or a query.
• Fields created as a combination of existing fields.
• Fields independent of any other fields.

The techniques used to create these fields are different but a field created using one technique can be changed into another category as necessary.

If you want to use a field that is already part of a table or a query, before, or when, creating the form or report, you must specify the list that holds the fields. There are three main ways you can do this:

• While in the New Form or the New Report dialog boxes, or if you are using the Form Wizard or the Report Wizard, you can select the object (table or query) that holds the list of fields from the combo box:
   
• If you have already started a form or a report and it is displaying in Design View but you did not yet select the table or report that holds the list of fields, on the Properties window for the form, you can click the arrow of the Record Source field and select the table or query from the list.
• If you want to use only a selected list of fields from a table or a query, start the form or report in Design View without selecting an object. Then, on the Properties window, click the Record Source field and click its ellipsis button. This will allow you to use the Query Builder to create a query that is a list of the needed field. After creating the query, close it. Its Field List would display only the previously selected fields.

To add a field to a form or a report, you can either click a control from the Toolbox and click the form or report, or drag a field from the Field List to the form or report.

Introduction to Form Design

When it comes up, a form is presented as a rectangular box made of a form selection button, two rulers, two scroll bars, a horizontal bar labeled Detail, and a gray area:

By default, a form presents a section referred to as the Detail section. This section starts from the Detail bar to the lower end of the section.

Besides the Detail section, a form can be equipped with additional sections. To add these sections, you can:

• Right-click the middle of the form and click either Page Header/Footer or Form Header/Footer; or
• On the main menu, you can click View and click either Page Header/Footer or Form Header/Footer.

If you click Form Header/Footer after right-clicking the form, two new sections will be added to the form:

• The Form Header section on top; and
• The Form Footer section at the bottom:

Although two sections are added, you can reduce one completely so it would not appear to the user. Therefore, you can keep one section and hide the other. If you create a form using the Form Wizard, both the header and the footer sections are added but they are completely reduced so they would not appear to the user. If you want to display them to the user, you must expand them.

If you click Page Header/Footer after right-clicking the form, two sections would be added to the form:

• A Page Header section
• A Form Header section

These sections would not appear to the user. They appear only if the user decides to print a form, in which case they would appear on the printed paper. Like the Form sections, you can change either or both the Page Header and the Page Footer sections.

To create a field on a form, you can click a control on the Toolbox and click one of the sections. You can also drag a control from the Fields List to a section of the form. Normally, you are allowed to add any control on any section but there are suggestions you should follow:

• You should add in the Form Header section a control that would display a common message on all records. An example would be the title of the form since the Form Header appears on top of the form. Such a field can be made independent of any other field. In some cases, you can also include an expression that can remind the user of the data on the current record. You can also add a company’s logo to the Form Header so it can display the same for all records.
• The Detail section is the most commonly used section of a form. It can be used to display any control. It is also the favorite place to display the current record of the form. Every form should (in fact must) have a Detail section.
• As mentioned already, the Page Header and the Page Footer sections do not appear to the user. If you allow the users to print a form, you can add these sections and create fields on them. On the other hand, if you think the users would not print the form or if you prevent this, you can use these sections to add controls that can be used by the form or database but must be hidden from the user.

Practical Learning: Creating Forms Fields

1. The Rockville Techno database should still be opened
   On the Database window, click the Forms button if necessary. Then, on the main menu, click Insert -> Form
    
2. On the New Form dialog box, make sure Design View is selected and the combo box is empty. Then click OK
3. If the Toolbox is not displaying, on the Form Design toolbar, click the Toolbox button
   On the Toolbox, click Text Box and click anywhere in the Detail section (the wide area under the Detail bar)
4. To add two new sections, right-click the Detail section and click Form Header/Footer
5. To save the form, on the Form Design toolbar, click the Save button
6. Type EmployeesOfficeContact as the name of the form and press Enter
7. Close the form

Introduction to Report Design

By its structure, a report resembles a piece of paper that you can use to prepare printable information to the user. Like a form, a report is made of a Selector button, two rulers, two scroll bars, and a Detail section. Unlike a form, by default, a report presents a Page Header and a Page Footer sections:

As you can see, a report appears with three default sections. Like a form, you can add another section on top and another at the bottom of the report. To do this, you can right-click the body of the report and click Report Header/Footer. You can also click View -> Report Header/Footer on the main menu. Additionally, unlike a form, you can create as many sections on a report as you see fit. This is referred to as grouping fields.

As mentioned for the form, you can add any control in any section of the report but, unlike a form, it is very important how you use the sections of a report as they have greater influence on the printed product. For this reason, each section can be configured for a specific task. If you are creating a regular report that displays one record per page, you may not need a Report Header and a Report Footer sections. If you are creating a brochure or a document that appears as a catalogue or a book, you may need a Report Header and a Report Footer sections. The sections would appear as follows:

• The Report Header section is the first page of the report. It can be created and configured like the cover of a magazine or brochure. If you are creating a regular report, you can use the Report Header to display a label that would appear in the beginning of the printed document even if it doesn't appear on its own page. For example, a Report Header section can be used to create a title for the report
• The Page Header appears on top of each piece of paper printed with the following exception: if the report contains a Report Header section and the Report Header section is configured as a whole page, the Page Header would not appear on the first page printed
• The Detail section is used to display regular data of the report. It is presented on each page except on the first if the report includes a Report Header and on the last if the report includes a Report Footer sections
• The Page Footer section is like the section that displays page numbers or footnotes of a book. It appears on each page
• The Report Footer section is the last page of the document. It is like the back page of a book or a magazine.

Practical Learning: Creating Reports Fields

1. On the main menu, click Insert -> Report
    
2. On the New Report dialog box, make sure Design View is selected and the combo box is empty. Then click OK
3. To add a control, on the Toolbox, click Text Box and click anywhere in the Detail section
4. To add two new sections, right-click anywhere in either section of the report and click Report Header/Footer
5. To save the report, on the Report Design toolbar, click the Save button
6. Type ListOfAssets as the name of the report and click OK
7. Close the report

MOUS Topics

Exercises 

Yugo National Bank

• Open the Yugo National Bank database. Start a new form in Design View. From the Toolbox, add two text boxes to the Detail section. Save the form as AccountTypes and close it.