Note: This site will be taken down July 1, 2024.

---

How to Create a Subform/Subreport

• Subform/Subreport tool 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.

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

 

Step 2: Select fields - for table or query.
This step is skipped if you choose to use an existing form or report.
 

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

Step 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?

From 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!

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.

---