For example, a Customer form might have a subform that displays each customer's Orders. Subforms are a great way to display information from a one-to-many table relationship.
Subforms are great for working with data in multiple tables with one-to-many relationships. Here the subform displays all the tours made by each employee.
Select the table or query and fields that you want to include on your subform.
You need to define which fields link the main form and subform.
The main form and subform are linked so that the subform displays only records that are related to the current record in the main form. For example, when the main form displays a particular customer, the subform displays only orders for that customer.
-
Open the frmEmployees form in Design view.
Usually you will want to have the assist you when you add a subform.
-
Make sure the
Control Wizards button on the Toolbox is pressed in.The Control Wizard is on whenever its button is depressed.
If the Toolbox isn't displayed, click the
Toolbox button on the Form Design toolbar.Before you add a subform, make sure that you have enough room for it on the main form.
-
Resize the main-form window as needed, so that you have enough room to add the subform.
You're ready to add the subform! Here's how:
-
Click the
Subform/Subreport button on the Toolbox.The mouse pointer changes to a
indicating that you can click and drag the subform onto the main form. -
Place the
pointer just below the DOB field. Click and drag the
pointer down and to the right, until the subform covers most of the bottom half of the main form.The appears and asks if you want to use an existing form for your subform or if you want to build a new form, using tables or queries. In this exercise we will have the Wizard build us a new form using tables and queries as our subform.
-
Click Next.
The next step of the Wizard appears. Here you have to select the table or query and fields that you want to display in your subform, as shown in figure. We want our subform to display the tours that each employee has sold, so we will select the qryCustomerTours query as the source for our subform.
-
Select Query: qryCustomerTours from the Tables/Queries combo box.
Now you need to select the fields you want to appear in the subform. You must select the related field used to join the main form and subform. This related field must appear on both the main form (where it is called the parent field) and on the subform (where it is called the child field). In this exercise we will use the EmployeeID field to link the two forms.
It's very important that the underlying tables or queries of the main form and subform have a related field and that the related field appears somewhere on both forms. See Creating Relationship Between Tables in Creating Relational Databases if you're having trouble understanding this concept.
-
Select the EmployeeID field and click the
button to add the field to the Selected Fields list.Now that you've added the most important field that will link the two forms, you can add the remaining fields that you want to appear on the subform.
-
Add the Date, TourID, CustomerID, and Number of Tickets fields to the Selected Fields list.
If you're this far, you should be a pro at adding and removing fields using the Wizard. Just make sure that you add the fields in the order specified in Steps 8 and 9.
-
Click Next.
The next step in the Wizard, as shown in figure, is to define the fields that link the main form and the subform. The Subform Wizard is often smart enough to recognize the field and use it to link the two forms as it is in this exercise. If not, you will have to click the Define my own option and select the two related fields.
-
Click Next and then click Finish.
Access creates the subform and adds it to the main form.
-
Save your changes to the frmEmployees form and display it in Form view.
The frmEmployees form displays information on each employee and the new subform displays the tours that employee booked.
Subforms created with the Subform Wizard are usually a little rough around the edges and will require a little clean-up work on your part. We'll learn how to modify and work with a subform in the next lesson.
To create a subform:
-
display the form in design view.
-
click the toolbox button on the toolbar, if necessary.
-
make sure the control wizards button on the toolbox is selected.
-
resize the form if necessary.
-
click the subform/subreport button on the toolbox.
-
in the form window, click and drag where you want the subform to appear.
-
click next.
-
specify the table or query you want to use for the subform and select the fields you want to appear in the subform.
-
click next.
-
if necessary, specify the parent and child fields that link the main form and subform.
-
click finish.