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.