Database Fields on Forms and Reports

Common Fields of Forms and Reports

Introduction

As we saw in previous lessons, fields are created on a table, then they can be added to a query. Based on the way Microsoft Access deals with fields, a field on a table cannot be created as a combination of other fields, as if it were an expression (unless the field is created as “lookup”). Instead, the fields on a table are primarily created to store data. On the other hand, as we will see in the next few lessons, a new field can be added to a query as a combination, through an expression, of other fields. Forms and reports present this same solution. Besides using the fields of a table or a query, a new field can be added to a form or report. This is because forms and reports make use of objects called Windows controls. In other words, the fields of a form or a report are created from Windows controls.

A label is a static object that presents a word or a group of words. The user cannot (directly) change the value that a label is displaying. By default, a label displays its sentence on a gray background.

A text box is a rectangular object with a white background and thick borders. On a form, a text box either presents information to the user, requests data from the user, or does both. On a report, a text box is used like a label: to display information that would be printed.

A text box may not be explicit enough to indicate what it is used for. For this reason, it is usually positioned next to a label. The label is usually positioned on top, or to the left, of the text box it is accompanying. For this reason, a text box is named after the label close to it. For example, and throughout this site, if a text box is placed under, or on the right side of, a label that displays First Name, the text box would be called “The First Name Text Box”.

A combo box is a type of text box that displays an item selected from a list it is holding. On a form, to change an item in a combo box, you can click its arrow and make your selection from the list. On a report, a combo box appears as such only when in design. When printed, only its data displays. Although it depends on the programmer's configuration of the combo box, the user may not be allowed to enter in a combo box something that is not part of the preset list.

Forms and reports are equipped with the same navigation buttons used on tables (and queries). This time, they are used to navigate left and right to the previous or the next records instead of up and down.

Object	Role
	First Record: allows moving to the first record
	Previous Record: allows moving one record back (if there is one) from the current record
	Record Indicator: Displays the number representing the current record
	Next Record: allows moving one record ahead
	Last Record: Allows moving to the last record
	New Record: Used to enter a new record for a form Since you cannot create a new record on a report, this button is not available on it

1. Start Microsoft Access and open the Altair Real Estate1 database
2. On the Database window, click the Forms button. Then double-click the Assets form to open it 
3. On the Assets form, click the Maintenance button
4. Notice the labels

Microsoft Access is an MDI application. This means that it allows you to open many objects, such as different forms and/or reports at the same time. This lets you switch from one window to another to perform data entry on related objects.

A form, by design, provides a friendlier interface and should make its navigation easy for the user. To provide this functionality, a form can be equipped with various types of Windows controls. Some of the objects are used only to display information to the user. Some others allow the user to change data.

A subform is a form that is “glued” or embedded in another form. It is used to display related data that would otherwise be accessible from another form.

A form is equipped with a special area that allows selecting the currently displaying record. The Record Selector is on the left side immediate to the left border of the form. The Record Selector is not available on all forms. The database developer decides on its availability or not.

1. Notice the subform under Maintenance Details
2. To view the design of subform, right-click the title bar of the Maintenance form and click Form Design
    
   
3. After viewing the form, close it

Like a form, a report uses the same label, text box, combo box, and list box controls. As mentioned already, the appearance of the controls may not be printed as we will see when studying properties of controls, the background and borders of controls can be changed for aesthetic reasons. Such background and borders would be printed.

1. On the Database window, click the Reports button
2. Double-click Assets By Employee
3. To view the design of a report, on the main menu, click View -> Design View
4. Close the report

A subreport is a report added to a parent report in a master-detail layout. It allows the user to view data from different tables but that is related to create a more realistic report. Although playing the same roles, subforms and subreports are not created the exact same way.

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

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

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
• On the main menu, you can click View and click either 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 and a Form Header sections. 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 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 of 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 (for example, since you cannot declare (VBA) variables in Microsoft Access, you can include pseudo-variables in the Page sections and refer to these pseudo-variables in your expressions; the pseudo-variables can be added like normal controls and their contents would be used as variables: only you are aware of such controls, not the user).

1. The Altair Real Estate1 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

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. We will review this aspect in Lesson 26.

As mentioned for the form, you can add any control in any section of the report but, unlike a form, it is very important to know how you use the sections of a report as they have greater influence on the printed product.

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 Assets as the name of the report and click OK
7. Close the report

• 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