Forms & Reports:

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

Forms and reports are the usual way that you interact with a database. Forms allow you to enter new records and edit existing records. Reports allow you to sort and group results to show just exactly what you want to see. Reports can show totals (Sum, Avg, Min, Max, Count, etc) for the whole report or for groups.

Forms and reports are similar in the way that you create and manage them, though each has its own special features.

You can import data in a number of different formats or objects from another database. You can export datasheets in several formats for use in other programs. You can also export Access objects to another Access database. Sometimes it is better to leave the data in its original form and location and link to it instead of importing into the database.

A well-designed form is easy to read and easy to use. For a data entry form, the controls should be in an order that matches the source that the user is using to enter new data. Backgrounds should not interfere with reading the data. Calculated values should be clearly different from entered values.

A well-designed report prints neatly and is easy to read. Groupings and totals are clearly labeled. The report fits on the page and the controls show all of the data from the control's source. Nothing is cut off.

The Properties dialog controls the look and behavior of the whole form/report, each section, and each control. There are a large number of properties for most objects.

The Page Setup dialog controls the pages of a print-out, including margins, orientation, and the number and size of columns.

Images that are not part of records can be inserted with an Image control or as an unbound object frame. Bound object frames are needed for images that are part of a record. Adding images or other OLE objects to records can greatly increase the size of the database.

Compacting a database will usually reduce its file size. It is important to compact regularly and to keep backup copies of your database.

You can create totals using aggregate functions for a whole report or for groups in a report. Doing totals of calculated controls can usually be done but requires extra effort.

You can combine two or more forms/reports into a single object by using subforms/subreports. The subform/subreport must be synchronized to the main form/report if you want to show only the data that matches the current record.

Access includes a number of wizards to help you create and manage objects. The Label Wizard and Chart Wizard create specialty reports. The Form and Report Wizards create a basic form/report based on one or more tables and queries. The Subform and Subreport Wizards automatically create the link(s) to the main form/report to synchronize with records.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro Arrow: subtopic open

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    ExercisesTo subtopics


What You Printed for Project 4: Forms & Reports



What is it?

# of pages

1. Printable Form Form: Clients-print - first and last pages 2
2. Report Wizard Report: Clients - wizard - no photo - first 3 pages 3
3. Groups Report: Clients-by country and postal code 3
4. Totals - Simple Report: Clients - country totals - last page 1
5. Totals - Simple Report: Trips by Agent - Numbered 1
6. Totals - Calculated Report: Trips by Agent - Cumulative Total 1
7. Label Wizard Report: Labels-Mailing for Staff 1
8. Chart Wizard Report: Chart-Trip Totals by Agent 1
9. Multiple Columns Report: Clients - country totals-3col 1
10. Subreports Report: Trips by Agent - with Chart 1
11. Subreports Report: Agent Info - page 5 1

Skills Covered

Skill Lesson
  • Use Import Spreadsheet Wizard:
        to import data to a new table
        to append data to an existing table
  • Copy and paste cells from spreadsheet
Import Spreadsheet
  • Import data from text file to a new table
  • Append data from a text file to an existing table
Import Text File
  • Import objects from another Access database
Import Access Objects
  • Link to a table
  • Modify a linked table
  • Break links
  • Repair broken links
Link to Tables
  • Export as text file
  • Export as spreadsheet file
  • Export to another Access database
Export from Access
Designing Forms
  • Use the Form Wizard to create a form
Form Wizard
  • Select a control
  • Format control's appearance
  • Use Format Painter
  • Resize control
  • Move control
  • Add Form Header
  • Add fields manually
  • Format control
  • Select multiple controls
  • Format multiple controls
  • Move multiple controls
  • Align controls
Create Form Manually
  • Apply an AutoFormat style
  • Change formatting
  • Adjust spacing
  • Add a rectangle
  • Save new formatting as an AutoFormat style
  • Apply custom AutoFormat style
  • View Form Header/Footer
  • Copy controls from another form
Format Form
  • Create a tab control
  • Rename tabs
  • Format tab text
  • Add new tab pages
  • Create multiple rows of tabs
  • Change order of tab pages
  • Create controls on pages in a tab control
Tab Control
  • Backup database
  • Use Properties dialog to control image display
  • Add a background image
  • Add an image to a form in an Image control
  • Format an Image control
  • Resize header and footer sections
Form Images
  • Add images to records with:
        Insert Objects dialog
        drag and drop
        copy and paste
  • Compact database
  • Compare database file sizes
Record Images: Bound Object Frame
  • Use Print Preview and Page Setup
  • Use the Properties dialog
  • Find Help about properties
Printable Form
Designing Reports
  • Use the Report Wizard to create a report
Report Wizard
  • Create a report manually
  • Create a calculated control
  • Handle a circular reference
Create Report Manually
  • Create group
  • Format controls
  • Move and align controls
  • Add controls to headers/footers
  • Add page numbers and total number of pages
  • Use 2 groups with headers
  • Count records in groups and the whole report
  • Edit the report's source in Query Builder
  • Manage sorting with the Sorting and Grouping dialog
  • Create simple total of field values for group and whole Report
  • Create a running sum in the Detail section
  • Number records with Running Sum
Totals - Simple
  • Revise report's SQL using Query Builder
  • Paste a control's label to different section
  • Create a calculated control
  • Create a total of calculated values for group and whole report with:
         Method 1: Repeating calculation in Total control
         Method 2: Query with calculated field
         Method 3: Running Sum property
Totals-Calculated Values
  • Email a report as an attachment
  • Export a report as a file in various formats:
        Excel format
        RTF format
        HTML format
        Text format
        Snapshot format
  • Save a report as a data access page
Special Forms & Reports
  • Create a switchboard
  • Inspect how a switchboard works
Switchboard Form
  • Create a report that uses the form data as parameter(s)
  • Create a custom form
  • Format a custom form as a dialog
  • Create an event procedure using Command Button Wizard
  • Edit an event procedure
  • Add a macro to the OnOpen event of a form/report
  • Add a macro to the OnClick event of a button
  • Add a macro to the OnClose event of a form/report
Custom Dialog
  • Use the Label Wizard
Label Wizard
  • Use the Chart Wizard
Chart Wizard
  • Use Page Setup to create multiple columns
  • Design the report to use all of the page
  • Design the report to have full-width report header with multiple Detail columns
Multiple Columns
  • Add a subform using drag-and-drop
  • Modify the source of a subform
  • Format a subform
  • Add a subform to a form using Toolbox
  • Rename a subform
  • Add a subreport using drag-and-drop
  • Add a subreport to report's Detail
  • Hide duplicate records
  • Remove unwanted white space with Can Shrink

Important Terms

aggregate function
Analyze It with Excel
Auto Activate
bound control
bound object frame
calculated control
Can Grow
Can Shrink
check box
circular reference
combo list
Control Source
custom dialog

default data
Detailed report
divider line
ellipsis button
Expression Builder
external data
Field List
file size
Form Header/Footer
Form Wizard
Format Painter
Group Header/Footer
Hide Duplicates
HTML format
Image control
Label Wizard
Linked Table Manager
list box

Macro Builder
mailing labels
main form
main report
Microsoft Graph
multiple columns
object frame
OLE object
option button
Page Header/Footer
Page Setup
Paste Errors
Print dialog
Properties dialog
Publish It with Word
Query Builder
Record Source
Report Header/Footer
Report Wizard
RTF format
running sum

Snapshot Report
Snapshot Viewer
SNP format
Sorting and Grouping
SQL statement
Summary report
Switchboard Manager
switchboard pages
tab control
tab page
toggle button
Toolbox bar
unbound control
unbound object frame
View: Design
View: Form
View: Layout Preview
View: Print Preview
XLS format