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