Creating a Form for a Many to Many Relationship

I was recently asked how to create a form to input order management data where there is an underlying Many to Many Relationship in place.  There are, of course, many ways of dealing with this common scenario, but the particular solution I suggested involved creating an Order Form, with an Order Details subform.  When the user enters the Order Details data on the subform, he or she is able to select a Product Item from a Combo Box list. The Row Source used for the Combo Box list is data drawn from the Products table.  Hence we have all sides of the Many to Many Relationship represented on a single form.

Figure 1 (Above): The Many to Many Form; the solution I suggested
in answer to dcodding's question.

 Figure 2: The underlying Many to Many
Relationship Structure, upon which the form is based.

It is interesting to note where each of the fields from the Order Form is located in the underlying Many to Many Relationship structure.  So let me briefly take you through this.

The main section of the order form has three fields derived from tblOrder, the left hand table in figure 2 above.  These are the OrderId and OrderDate fields.  In addition to this we also have the CustomerId field which is the foreign key from tblCustomer (a table which is not part of the Many to Many Relationship, therefore not shown in Figure 2).

The relationship between the main section of the Order Form and it's subform is modelled on the One to Many Relationship between tblOrder and tblOrderDetails. However, because we have an underlying Many to Many Relationship, I have created a query for the Subforms' Record Source which has fields from both tblOrderDetails and tblProducts.  Moreover, the second ProductId field is actually a combo box which uses data from tblProducts as its Row Source.

Lets take a closer look at the subform's design and underlying query:

Figure 3 (Above): The Order Details Subform design grid.

Figure 4: The Query used for the Subform's Record Source.

As you can see, ProductId, ProductId, and Quantity are derived from tblOrderDetails, and CostPerUnit is derived from tblProducts.  ProductId is, of course the foreign key from tblProducts. There is also a Calculated Field (Total) which multiplies the CostPerUnit by Quantity to provide the actual purchase price.

So why then do we have two ProductId's?  The reason for this is that the second ProductId does not actually display the Id number per se. Since this field is represented by a Combo Box control on the subform, the data which is displayed can be different to that which it is bound to.  So in order to make the subform more user friendly, the control is set up to display the product's ItemName from tblProducts whilst being bound to the tblOrderDetails.ProductId field. This is done by setting the combo box Control Source property to ProductId,  and its Row Source property to tblProducts. As such, using the Combo Box in this way has enabled the subform to obtain information from the products table on the right hand side of the Many to Many Relationship. For detailed information on how to do work with Combo Boxes in this way, you might like to see my post on Customizing an Access Combo Box.

So what happens when the user comes to enter data in the subform? If  the user knows the productId number for the Order Item in question, he or she will enter that number in the first column of the subform.  Since this is the foreign key for the Products table, doing so will 'bring' any other subform field derived from tblProducts along with it.  In other words once the user enters a productId, data in the CostPerUnit field for that product record is displayed in the appropriate field of the subform.  In addition to this, the ItemName for the product is displayed in the Combo Box control, since its Row Source is bound to ItemName in tblProducts.  If, on the other hand, the user does not know the ProductId, he/she may leave the first field blank, and select a value from the Combo Box drop down list.  Since the Combo Box Control Source is bound to ProductId of tblOrderDetails, and its Row Source, as we know, is bound to ItemName of tblProducts, this is effectively the same as entering the ProductId number directly.  What's more, data from tblProducts is 'brought' over to the subform as before.  Hence, the first productId field of the subform is filled in automatically.

We see, therefore, that this relatively simple and user friendly form not only represents all tables in this Many to Many Relationship, but also provides a practical example of how the Many to Many Relationship works in action.

Using a Subform Link to Open a form at a Specific Record

Imagine a scenario where you are looking at a record displayed on a form. The form contains a subform displaying a number of related records summarized in datasheet view.  This tip shows how we can open a new form at a specific record when we click a particular link on the subform.  As we shall see, it is a convenient way to navigate between forms when there is an underlying Many to Many Relationship structure in place.

To do this we are going to use the example of a Customer Order form.  The main section of the form displays the Customer Order, and the Subform displays the Order Details stored in the junction table.  When a user clicks the product link in the Order Details subform, the Product Form opens at the record for that particular product.

Figure 1: The Orders Form.  Clicking the product link in the Order Details Subform
opens the Product Form (see Figure 2 below) at that particular record.

Figure 2: The Products Form displaying the record specified in the Subform in Figure 1 above.

When the user clicks the link, the textbox's On_Click Event fires, triggering a short VBA subroutine.  This is the section of code responsible for opening the Product Form at the relevant record:

Dim varWhereClause As String
varWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause

It begins by defining a string variable to hold an SQL Where Clause.  The next line sets the string variable.  Notice how the end of the string references the productId field of the subform field that has been clicked.  The final line uses the DoCmd OpenForm Statement to open the Products Form.  The varWhereClause string variable is used as the statement's WhereCondition, thereby opening the form at that particular product record.


Here is the full procedure for putting all this in place:

1. Create the main Customer Order Form (with the Order Details Subform).  
2. Create the Products Form.
3. Next you need to go back and edit the Order Details Subform.
   
   
4. Click on the ProductId field, and then open the PROPERTIES SHEET. 
5. Under the FORMAT tab, change the IS_HYPERLINK property to YES.  Then change the DISPLAY_AS_HYPERLINK property to SCREEN_ONLY.  This changes the appearance of the ProductId field to a hyperlink style.
6. Under the EVENT tab of the PROPERTIES SHEET, select the ON_CLICK cell in the grid.  Then click the three dot symbol on it's right to open the CHOOSE BUILDER dialogue box.  
7. Select CODE BUILDER from the list, and click OK to open the VBA Editor.
8. Past the code listed below in between the lines, "Private Sub ... " and "Exit Sub", in the VBA Editor.

On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "ID = " &  Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next

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.