Subforms - Viewing the One to Many Relationship in Action

In my last blog post we learnt how to create a One to Many Relationship between a Customer Table and an Orders Table.  We examined how Access was able to separate groups of orders according to which customer they belonged to.  This was possible due to the creation of a Primary Key Field in tblCustomer and a corresponding Foreign Key Field in tblOrder; and then creating a One to Many Relationship between the two tables. In this post we are going to examine how using a Subform enables us to view this One to Many Relationship in action.

To do this we will begin by creating the main form for the Customer Table (frmCustomer).  This will display each customer record, and corresponds to the One side of the One to Many Relationship.  We will then create a new form for the Orders Table (frmOrders) which will display in Datasheet View.  This is going to be used as the subform contained within frmCustomer.  It will display a list of orders for each individual customer. This corresponds to the Many side of the One to Many Relationship. The finished result will look like this:

The top section of the form shows the Customers Details.
The Subform below show the orders made by that Customer.

If you havn't already done so, you can create the tables and the relationship by following the instructions in the previous post on The One to Many Relationship.

Creating the Orders Form

The first step in this exercise is to create the orders form - that which will be used as the subform. We will call this frmOrders.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
   
   
   This opens up a blank Form Design Grid. (For more information about the form design grid, see my post on Creating an Access 2007 form from Scratch).
   
   
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
   
   
   
   
   
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblOrder.
   
   
   
   
   
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblOrders so the list of field opens out for that table (if it has not already done so).
   
   
   
   
   
9. Select the ItemOrdered, Date, and Price from the FIELD LIST by double clicking each one in turn. As we do so, they appear in the FORM DESIGN GRID like this:
   
   
   
   
   
10. We are now going to change the DEFAULT VIEW property of the form.  So select the FORM again by following step three above (if it is not already selected).
11. Open the PROPERTIES SHEET (if it is not already open).
12. The DEFAULT VIEW property is located on the second line down of the FORMAT tab of the PROPERTY SHEET.  Change the property to DATASHEET by clicking the DEFAULT VIEW field;  then clicking the arrow at the end of the box; and selecting that option from from the drop down list.
    
    
    
    
    We use the DATASHEET option here so that our subform displays as a list within the main form, thereby reflecting the One to Many Relationship that we want to show in action.

You can now close the form, saving it as frmOrders (if you have not already done so).


Creating the main Customer Form


Next we are going to create the main customer form to display the customer details.  This form will also hold the Orders Subform, thereby listing all the orders for each particular customer.  Steps one to nine below correspond to the steps we went through creating the orders form.  Step 10 onwards is new, and deals with the creation of the Subform Control.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon. 
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblCustomer.
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblCustomer so the list of field opens out for that table.
9. Select the FirstName, Surname, Address1, City and PostCode from the FIELD LIST by double clicking each one in turn.
10. We are now going to create the Subform.  We are going to do this process manually so begin by deselecting the USE CONTROL WIZARDS icon from the CONTROLS group of the DESIGN ribbon. It is deselected when it is no longer highlighted in orange.
    
    
    
    
    
11. Click the SUBFORM icon in the CONTROLS group of the DESIGN ribbon.
    
    

    

    The SUBFORM icon is on the bottom row,
    highlighted in orange.

    The mouse pointer changes to the Add Subform Icon.
12. Take the Add Subform Icon to a location below the other text fields in the FORM DESIGN GRID, and click.  This creates an empty subform control on your main form.  At this stage it just looks like this:
    
    

    

    The empty Subform Contol is represented in this
     image by the unbound control labelled Child1.

    
    
13. It is a good idea at this point to resize the control to accomodate the subform.
14. Next we are going to set the Subform Control's properties to display tblOrders (which we created above) as the actual subform.  NB you might like to note the distinction here between the Subform Control and the Form which is displayed in that control. The later is a property of the former.
15. Click the Subform Control on the Form Design Grid to select it. The border changes to an orange highlight once it is selected.
16. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  This will display the PROPERTIES SHEET for our Subform Control.
17. Select the DATA tab of the PROPERTIES SHEET.
18. Now set the SOURCE OBJECT property to tblOrders.  Notice how Access has filled in the LINK MASTER FIELDS property below to ID, and LINK CHILD FIELDS property to CustomerId.  The Master Field is the Primary Key Field (ID) of tblCustomer, and the Child Field is the Foreign Key field (CustomerId) in tblOrder.  These can be entered manually if necessary.  

This has now configured our Subform Control to display a list of orders for each individual customer.  At this stage you might like to tidy up the form, and enter any labels or dividing lines that you see fit. When you have done so, save the form as frmCustomer, and open it in FORM VIEW to see what we now have.  It should look something like this:

As you can see in the screenshot above, the customer, John Jones has three orders displayed in the Orders Subform.  As you move through each customer record in turn, you will see a different set of orders corresponding to the particular customer being viewed. You will see that each customer in frmCustomer contains the orders we entered when we worked through the exercise in the last blog post on the One to Many Relationship.  So to see the One to Many Relationship in action, just compare your results from frmCustomer with this screenshot of the data held in our two tables:

As such, by using a Subform we are effectively processing information from both related tables at once.  This makes your database application much more user friendly.  Try entering some new orders for the customers in this database, then look at the orders table to see them stored. Notice how Access enters the customerId in the orders table automatically so it know which customer it belongs to.  All this is part of the One to Many Relationship in action.