Home

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.

AutoForm

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

The From Wizard

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:
     
    Columnar Form
  • 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.

Practical Learning: Form Wizard - Columnar

  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 New Form
  3. On the New Form dialog box, click Form Wizard
  4. From the combo box, select the Assets
     
    New Form
  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
     
    Company Assets
  11. To close the form, click its Close button

Data Entry on a Form

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.

Practical Learning: Form Data Entry

  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 Record Number, 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

    Maintenance

  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 

Form Printing

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

Reports Fundamentals

 

AutoReport

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.

Practical Learning: Using AutoReport

  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

The Report Wizard

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.

 

Practical Learning: Using the Report 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

Report Printing

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

Lesson Summary

 

MOUS Topics

S6  Print database objects (..., forms, reports, ...)
S7 Navigate through records in a form
S16 Create a form with the Form Wizard
S23 Enter records using a form
S35 Create a report with the Report Wizard
S26 Preview and print a report
 

Exercises

 

Tenley Associates Company Expenses

  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
 

Previous Copyright © 2002-2007 Yevol Next