Forms & Reports:
Subform/Subreport

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


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 is a form that is embedded in a form or report, the main form/report.

A subreport is a report that is inserted into another report, the main report. You cannot put a subreport onto a form.

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 page; 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 or Page Header/Footer, not just the Detail section. This lets you combine several reports into a single one. The Detail section could even be blank!

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.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

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 & Reports Arrow: subtopic open
    Icon: StepSwitchboard Form
    Icon: StepCustom Dialog
    Icon: StepLabel Wizard
    Icon: StepChart Wizard
    Icon: StepMultiple Columns
        Subform/Subreport
    Icon: StepSubform
    Icon: StepSubreport
    Summary
    Quiz
    ExercisesTo subtopics


Search  
Glossary
  
Appendix



How to Create a Subform/Subreport

  • Subform/Subreport tool Button: Subform/Subreport in the Toolbox bar:
    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 Database Window drag a table, query, form, or report and drop it on your form/report in Design View. (Exception: A report cannot be dropped onto a form.) 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

If you use the Subform/Subreport tool, a wizard opens with just 3 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 a new query from inside the wizard. It must already exist.


 

Wizard: Step 2 - select fields from table or queryStep 2: Select fields from table or query.

If you chose to use an existing form or report, the wizard skips this step.
 

Wizard: Page 3 - which fields linkStep 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. Access automatically adds your new object to the correct list in the Database Window, either Forms or Reports. This name also appears in the label for the control.
 


What Does a Subform/Subreport Show?

Subdatasheet expanded in Table Datasheet ViewFrom earlier lessons on tables, you learned that a tables 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 such related data, synchronized to the current record on the main form or report. Very useful!

A subform can use any view. 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 form view

Subform as datasheet; Subform as form
Scores in subform for the current student in the main form


Linking Parent and Child

Dialog: Properties - of subform, linking fieldsIn the Properties dialog for the subform/subreport, there are two properties that define the connection between the Parent (form/report) and the Child (subform/subreport). 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 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.