One-to-Many-to-Many Forms

A one-to-many-to-many form is one in which there are two subforms on a main form. The first subform contains multiple records associated with the displayed (current) record in the master form. The second subform contains multiple records associated with the current record within the first subform. (For an example of this technique, see the "Subform 2" form in AC208.MDB.)

For example, suppose the main form displays customer data. The first subform lists all of the orders placed by a given customer. The second subform contains all of the order item details for the current order in the first subform.

First, create the main form and the first subform, which display customers and their orders. These forms are linked on Customer ID. Next, create a second subform to display order details, and set the LinkMasterFields and LinkChildFields for the subform control to [Order ID].

For the second subform to work correctly, you need an Order ID field on the main form with which it is synchronized. Put a hidden control on the master form and set its ControlSource to the expression:

= [Orders Subform].Form![Order ID]

Since the details subform is linked to this hidden field, each time you change a record in the first subform, the second is re-queried.