Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Databases > Forms & Reports > Subforms/Subreports
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Forms & Reports: Subforms/Subreports

Access lets you display data that is related to records in a form or report by using a subform or subreport. The creation and management is much the same for both.

A subform or subreport is embedded in a form or report, the main form/report.

You cannot put a subreport onto a form in Access 2007 but you can in later versions of Access.

The form or report that contains a subform or subreport is called the parent. The subform or subreport is called the child.

Form with subform on its own tab Report with subreport for each record

Form with subform on tab control; Report with subreport in Detail

Print Preview: Report with subreport in report header and subreport in each DetailCombined report: Subreports can also go into the Report Header or Footer as well as the Page Header or Footer, not just the Detail section. This lets you combine several reports into a single one. The Detail section could even be hidden!

The illustration shows one subreport in the Report Header, which will appear only once, and another the Detail section, which will be different for each record.

Nesting: You can nest up to 7 levels of subforms inside forms or subreports inside reports. "Nesting" means you have a main form that contains a subform, which itself contains a subform, which also contains a subform... for 7 levels deep. It's the same for reports. Having that many levels would certainly be unusual!

Total number: There is no particular limit to how many separate subforms or subreports you can put into a form or report.

How to Create a Subform/Subreport

  • Subform/Subreport tool Button: Subform/Subreport in the Controls palette:
    In Design View, click the tool and click on the form/report or drag to create a control of the size that you want. A wizard walks you through the rest of the process.
  • Drag-and-drop:
    From the Navigation Pane drag a table, query, form, or report and drop it on your form or report in Design View. (Exception: A report cannot be dropped onto a form in Access 2007.) If Access cannot figure out how to link the parent and child, the wizard opens to walk you through the rest of the process.

Wizard

The wizard for inserting a subform/subreport has just three or four steps. The illustrations below are for a subform but the wizard is the same for a subreport. Only the title bar is different.

Wizard: Step 1 - source for subform or subreportStep 1: Pick source for the subform/subreport. You can use an existing form or report or you can pick an existing table or query.

You cannot create anything new from inside the wizard. The object must already exist.


 

Wizard: Step 1a - match fields yourself to line a table or query (Access 2010)Step 2: Select fields - for table or query.
This step is skipped if you choose to use an existing form or report.
 

Wizard: Step 2 - select fields from table or queryStep 3: Create a link between the main and the sub.
The wizard makes a guess and cleverly offers you in words what you want to see rather than making you pick fields to match up.
 

Wizard: Page 4 - name the controlStep 4: Name the new object.
If you used a table or query, Access automatically adds your new subform to the Navigation pane, in either Forms or Reports.

The name you entered will appear in the label for the control. Choose a name that makes it clear that this object is part of another object.


 

What Does a Subform/Subreport Show?

Subdatasheet expanded in Table Datasheet ViewFrom earlier lessons on tables, you learned that a table or query that has a One-to-Many relationship with another one can show a subdatasheet of related records. The illustration shows a table of Students with a subdatasheet for the scores of the student. The Student table is the One side and the Scores table is the Many side of the relationship. One student has many scores.

The usual type of subform/subreport shows this kind of related data, synchronized to the current record on the main form or report. Very useful!

A subform can use any view to display the data. Datasheet View or Form View are, of course, the most common. You can even edit the data in the subform or add new records. Sweet!

Form with subform in datasheet view form with subform in continuous form view

Subform as datasheet; Subform as continuous form

Linking Parent and Child

In the Property Sheet for the subform/subreport, there are two properties that define the connection between the Parent (form/report) and the Child (subform/subreport). Both are required to create the link.

Link Child Fields is for the field or fields on the subform/subreport that match the field or fields from the main form/report in Link Master Fields. Both properties must be set for the subform/subreport to synchronize with the main form/report.

The field names are usually the same but can be different. The data types, however, must be the same or compatible. These are usually the fields that join the underlying tables. Otherwise you cannot be sure that there is a unique choice.

You can edit the Link properties yourself if Access loses track of the matching fields.  Be careful to pick the right fields!  If you are wrong, you will pull the wrong data and it may not change correctly when you change records on the main form/report.

The subform/subreport will be blank if Access cannot connect the data.

Conditions must be met for Access to automatically link the child and parent:

  • The underlying tables have relationships already defined.
  • The main form/report is based on a table that has a primary key. The subform/subreport is based on a table that shares that field or has a compatible field. In particular, an AutoNumber field will match only with a Long Integer type field.