Introduction to Forms and Reports

Forms Fundamentals

Introduction

As done for the tables and queries, Microsoft Access provides a central dialog box you can use to create forms: this is the New Form dialog box. To display the New Form dialog box, if no object is opened, on the main menu, you can click Insert -> Form. Alternatively, in the Database window, you can first click the Forms button. Then, on the toolbar of the Database window, you can click the New button.

As done for a query, before creating a form, you must decide where data would come from. If you are creating an independent form whose fields are made of Windows controls and whose contents would not depend on any field or data stored in another object, you can just proceed as you see fit, as if you were developing a regular Windows application. If you are creating a form meant for data entry for the database, you should specify where its data would come from. The object can be a table, a combination of tables, a query, a combination of queries, or a combination of tables and queries. If the data of a form will be based on such an object or a group of objects, you must specify it, before or while creating the form.

The fastest means of creating a form is through a feature called AutoForm. To use AutoForm, you can first select a table or a query on the Database window; you don't have to open the object. If the table or query is selected in the Database window, to create a form in one step, on the Database toolbar, either click the AutoForm button if it is already selected, or click the arrow of the New Object button and click AutoForm. Alternatively, while a table or a query is selected in the Database window but not opened, to fast create a form, on the main menu, you can click Insert -> AutoForm. If a table or a query is already opened and you want to create a form based on it, on the Table Databasheet toolbar, or on the Query Datasheet toolbar, you can click the AutoForm button if it is already set as the New Object or you can click the arrow of the New Object button and click AutoForm.

Another technique used to create a form using AutoForm consists of first displaying the New Form dialog box. From there you can click one of the three AutoForm options, select the object that holds data and click OK.

Practical Learning: Using AutoForm

1. From the resources that accompany these lessons, open the Altair Real Estate1 database
2. On the Database window, click the Queries button and click the Employees By Location query to select it
3. On the Database toolbar, position the mouse on the New Object button. If the tool tip displays New Object: AutoForm, , then click it. If it displays something else, click the arrow of the New Object button and click AutoForm
   
   
4. After viewing the form, on the Form View toolbar, click the Save button
5. Accept the suggested name for the form by clicking OK
6. Then close the form

As far as users are concerned, the form is the central point of a database. It is used to view, enter, manipulate, and search data. Because users spend a great deal of their time looking at forms, you should create and make them as attractive and friendly as possible. Form design can take a long time in database development but Microsoft Access provides quick means to get around.

The Form Wizard provides an easy and fast means of creating a form. Like the other wizards we have used so far, this one also takes you step by step through creating the object. There are two main ways you can launch the Form Wizard: from the Database Window or from the New Form dialog box. On the Database Window, you can click the Forms button. Then double-click Create Form By Using Wizard. On the New Form dialog box, you can click Form Wizard and click OK.

The first page of the Form Wizard allows you to choose the originating table or query that will supply the necessary fields in the form. Once you have selected the object, its corresponding fields display in the Available Fields list box, you can select all fields or decide which ones you want to include in the form.

The 2nd page of the wizard allows you to select the desired layout of the form. Forms can be designed in various flavors: Columnar, Datasheet, Tabular, Justified: 

• A columnar form is used to display data one record at a time. This is a convenient display for data entry and analysis because the user is able to examine each piece of information for each one record:
   
  
• A tabular form displays its data in a table layout following a continuous series of records. All or almost all records are displaying in a single layout:
  
  
  
  There are three main ways you can create a tabular form: from the New Form dialog box, using the Form Wizard, or designing it
• A datasheet form looks and behaves like a table, displaying all possible records at the same time instead of one record at a time:
  
  
   
  A datasheet form is mainly used in relationships to display another table's related records. It is also suitable for people who prefer to work in a spreadsheet environment. This display allows the database developer to provide a sheet view to the data entry personnel without making the table's design or formats available.
  
  You can make the same form available in Form View and Datasheet View. In fact, most forms are. Users can switch from Form View to Datasheet View by clicking the View menu. Unfortunately, this could also allow the users to get the form in Design View, and then they could modify it. If you do not want the users to have access to Design View but retain Form View and Datasheet View, you may have to create custom menus and toolbars.
  
  There are two main ways you can create a Datasheet form: using the wizard or designing it.
• A Justified form provides a good and consistent look. When the form is created, borders are added to labels:
   
  
  
  The 3rd page of the Form Wizard presents the forms designs you can choose from. These are the designs we saw when creating a database using the Database Wizard.
  
  The 4th page allows you to name the form.

1. The Altair Real Estate1 database should still be opened database
   On the Database Window, click the Forms button
2. On the Database Window’s toolbar, click New
3. On the New Form dialog box, click Form Wizard
4. From the combo box, select the Assets
    
   
5. Click OK
6. Click the Select All Fields button 
7. Click Next
8. Accept or choose the Columnar layout, then click Next
9. Click the Expedition style and click Next
10. Set the name of the form as CompanyAssets and click Finish
    An automatic form is created for you
     
    
11. To close the form, click its Close button

Data entry of a database is mainly performed on forms as they provide the friendliest display of information. Data entry on a form is performed using text boxes, combo boxes, and other controls. On text boxes, the user enters data by typing it. On a combo box, depending on how the object was configured by the database developer, the user may have to only select an item from the list. In some other cases, the user may be allowed to enter new data.

After entering or changing data on a control, to move from one field to the next, the surest way is to press Tab. On most occasions, the user can also press Enter. The Enter key may not move the cursor from a text box that allows multiple lines of text (such as a field created as Memo). If the user is simply reviewing data without performing data entry, the keyboard’s arrow keys can also be used to move among fields.

1. The Altair Real Estate1 database should still be opened and the Forms button should be selected. Otherwise, on the Database Window, click the Forms button
   Double-click the Employees form to open it
2. In the first record, for Jerry Elliot, click Work Phone and type (301) 868-6660
3. Click the Next Record button
4. For Robert Niemmes's Work Phone, enter (301) 868-9400
5. Click in the Record Number text box , delete 2, type 4 and press Enter
6. For the Work Phone of Jeannette Hyan, type (301) 868-2824
7. To close the form, click its system Close button
8. Still in the Forms section? Double-click Assets 
9. In the Comments text box, type Computer currently located in the Conference Room, made available for customers and guests
10. Click the arrow of the combo box and select Excellent Condition
11. Press Tab and enter the Next Maintenance date as 06/06/07 (or 06/06/2007 depending on the settings on your computer)
12. Click Current Value and type 665
     
    
13. Click the Maintenance... button. This opens a form
14. Click the empty box under Date and type 01/14/07 (01/14/2007 depending on your computer)
15. Press Tab and type An anti-virus and other utilities have been installed
16. Press Tab and type Judith Kamen
17. Press Tab and type 112.95
18. Press Tab and create other records as follows:
     

    Date	Description	Performed By	Cost
    04/08/07	IE7 was installed and the computer received a routine cleanup	Ctrl + '	64.85
    07/07/07	New software has been installed: Adobe Acrobat 8.0 Professional and CorelDRAW X3 Graphics Suite	Hortense Engolo	125.55

    

19. To close the form, click its Close button 
20. On the Assets form, click the Depreciation... button
21. Click Depreciation Life and type 5
22. Click Salvage Value and type 300
23. In the Depreciation Details section, click under Date and type 11/15/06
24. Press Tab and type 50
25. Enter the other depreciation sequence as follows:
     

    Date	Amount
    11/15/06	50
    12/15/06	50
    01/15/07	50
    02/15/07	50
    03/15/07	50

     
    
26. To close the form, click its Close button 
     
    
27. To close the form, click its Close button 

Although not part of their primary role, forms provide a quick means of printing data, especially in the absence of desired reports. There are various issues related to printing forms, including printing all records or just some of them.

You can print a form without opening it. Once you know the form you would like to print, locate its name in the Database Window, right-click it and click Print... All records on the form would be printed.

When a form is opened, you have the option of printing all of its records or the selected one. Once in Form View, you can click the Print button on the toolbar. To print just one record, navigate to the record you would like to print, click the Form Selection bar to select the record. On the Form View toolbar, click the Print button.

Practical Learning: Printing Forms

1. The Altair Real Estate1 database should still be opened.
   In the Forms section of the Database window, double-click the Assets form to open it
2. On the Assets form, click Depreciation. Since we have only one record, we will just print it
3. On the Form View toolbar, click the Print button . Notice that the job goes straight to the printer and starts printer
4. Close the Depreciation form and close the Assets form
5. On the Database Window, double-click Employees 
6. To navigate to the 3rd employee, click the Next Record button twice
7. After making sure that the desired record is displaying, in this case, the 3rd record, on the main menu, click File -> Print…
8. On the Print dialog box, click the Selected Record(s) radio button
9. Click OK
10. Verify that only the displaying employee's record was printed
11. Now we will print records from 2 to 4
12. Navigate to record No.2
13. Click the Record Selector bar
     
    
14. Press and hold Shift. Click the Next Record button twice to display the fourth record (even though the record # still displays 2)
15. Release Shift
16. On the main menu, click File -> Print… 
17. On the Print dialog box, click the Selected Record(s) radio button and click OK
18. Verify that records from 2 to 4 have been printed
19. Close the Employees form

A report provides an object used to print a database records. Although you can print tables, queries, or forms, reports are customized to be printer friendly. They can perform and display calculations. Once again, Microsoft Access provides wizards to quickly create reports.

The simplest way to create a report is by using the AutoReport feature available either from the New Object button on the Database toolbar or the New Report dialog box.

1. The Altair Real Estate1 database should still be opened
   On the Database Window, click the Tables button
2. Click the Asset Categories table to select it
3. On the Database toolbar, click the arrow of the New Object button and select AutoReport
    
   
4. After viewing the newly created report, close it
5. When asked whether you want to save the report, click Yes
6. Accept the suggested name for the report and press Enter
7. On the menu bar, click Insert -> Report
8. On the New Report dialog box, select AutoReport: Columnar
9. In the combo box, select Status
    
   
10. Click OK
11. After viewing the report, close it
12. When asking to save it, click Yes
13. Accept the suggested name for the report and click OK

Microsoft Access can help you create a more elaborate report where you would use one of the pre-designed layouts. This is done using the Report Wizard. The Report Wizard is available from the New Report dialog box that you can access either from the menu bar or from the New button on the Database Window when in the Reports section. If you are using Microsoft Access 2000 or 2002, you can also double-click Create Report By Using Wizard.

1. The Altair Real Estate1 database should still be opened
   On the main menu, click Insert -> Report
2. On the New Report dialog box, click Report Wizard and click OK
3. On the first page of the wizard, click the arrow of the Tables/Queries combo box and select Table: Employees
4. Click the Select All Fields button 
5. Click Next
6. Accept anything in the second page and click Next
7. Accept anything in the third page and click Next
8. In the Layout section, click the Columnar radio button:
    
   
9. Click Next
10. Select the Bold Style to preview it. Then click Formal and click Next
11. Accept the suggested Title of the report as Employees and press Enter
12. Scroll down in the report to view the records and close the report

As stated already, the report is the favorite object used to print data. As done with the other objects, you can print a report whether it is opened or not. Before printing a report, you must first select or open it. If you want to print a report without opening it, in the Database window, make sure the Reports section is selected. Then, you can locate and right-click the desired report. If you follow this approach, the report would be sent directly to the printer.

If you want to control or customize the printing of a report, first select it in the Reports section of the Database window or open it. Then, on the main menu, you can click File -> Print… This would display the Print dialog box, allowing you to select a printer if you have more than one. You can also specify such aspects as the color, the number of copies or the range of records to be printed.

Practical Learning: Printing a Report

1. The Altair Real Estate1 database should still be opened
   On the Database window, click the Reports button
2. Right-click the Asset Categories reports and click Print…
3. To print a report after displaying it, double-click the Assets By Category report to open it
    
   
4. On the main menu, click File -> Print…
5. Click the Properties button
6. Based on your printer, select the Landscape radio button
7. Click OK on the Print dialog box
8. After printing the report, close it
9. On the Database window, click the Forms button and double-click Switchboard
10. On the Main Switchboard form, click Preview Reports...
11. Click Preview the Assets By Employees Report
12. After viewing the report, on the Print Preview toolbar, click the Print button
13. On the Main Switchboard form, click Preview Reports...
14. Click Preview the Maintenance History Report
15. In the dialog box, click Begin. Entry Date and type 01/10/07
16. Press Tab and type 04/15/07
     
    
17. Click Preview
18. To print the report, on the main menu, click File -> Print...
19. Click Properties and select the Landscape radio button
20. Click OK
21. After printing the report, close it

1. Open the Tenley Associates database
2. Use the Form Wizard to create a Columnar form based on the Employees table and including all fields. Save the form as Employees and set its display Style to Stone
3. Use AutoForm: Columnar from the New Form dialog box to create a form based on the Expense Details table and save it as Expense Details
4. Fill the Employees form up with the following values, ignoring those not mentioned:
    

   Last Name	Department	Work Phone	Extension
   Bidds	Accounting	(202) 266-5022	2422
   Roberts	Sales	(202) 266-5022	2408
   Hoaks	Sales	(202) 266-5022	2412
   Baugh	Administration	(202) 266-5022	2418

5. Print the Employees form
6. Using the Report Wizard, create a Columnar report based on the Expense Categories table, including all fields and using the Corporate style. Save the report as
   Expense Categories and print all of its records
7. Using the AutoReport from the Database toolbar, create a report based on the Employees Contact Information query. Save the report as
   Employees Contact Information and print all of its records