An Overview Of Relating Subforms in Access
This is a common requirement in a database application. Here are a few random examples:
So, this requirement typically arises in databases where you are working with data in a one-to-many-to-many configuration.
This article is an introductory overview of the possibilities. The example used is not intended to be a complete solution. It has been simplified in order to illustrate the concepts. Screen shots are in Access 2003, but the same approaches can be applied in other versions of Access, including Access 2007.
We will suppose that a service provider wishes to see listed all the services booked on a specific date, in this case the same date as the service being shown on the current record of the main form. And then, they want to have access within this interface to some core information about the people registered on each of the services.
Here are three ways that this can be approached…
Access does not allow a continuous view form to have another continuous view form, or datasheet view form, embedded in its Detail section.
However, with a form in datasheet view, you can put a subform into the Detail section, with its Link Master Fields and Link Child Fields properties defined in the usual way, and the form will create it as a subdatasheet.
The form design can be done along these lines:
When opened, the form can look like this, if the sub-subform also has its Default View property set to Datasheet:
Here we see that Access automatically displays a column of [+]/[-] symbols, that can be used to expand or collapse the subform row, to show the related sub-subform data. Each row can be expanded/collapsed independently, so you can see more than one at once.
If you set the sub-subform’s Default View to Continuous Forms, you can then have access to some of the additional functionality supported by continuous forms that is not available in datasheets. For example, as illustrated below, formatting of Labels in the Header, Command Buttons, or Option Groups.
Form Footer Section
By making a fairly simple design change to the above, we create a different paradigm. Move the sub-subform from the Detail section into the Footer section of the subform, like this:
The first level subform no longer needs to be datasheet view, so it can be changed to a continuous form. With the sub-subform as a datasheet, we see something like this:
So the sub-subform in the footer automatically refreshes to show the related data to whatever is the current subform record. Unlike the subdatasheet concept, in this case you can only see related data for one record at a time. But this provides a nice intuitive interface for this type of functionality.
Once again, the sub-subform can also be continuous view, allowing the richer interface options:
Side By Side
Another approach is to display both subforms on the main form, alongside each other. This approach has an advantage if the nature of the data means that there can be a large number of related records for each “one” side record.
This is designed so that the “many” side subform shows the related data to the current record on the “one” side subform. To achieve this, a hidden control is placed on the main form, referencing the key field on the “one” side subform. And then this control is used as the Link Master Fields property of the “many” side subform. In the illustration below, this linking control is shown in green.
So in Form view we get something like this…
The right-hand subform automatically refreshes to show the data related to whatever is the current record in the left-hand subform.
Another good example of this concept can be seen by scrolling to the bottom of this page:
The concepts explained above, using this very simple example, can be adapted to many scenarios where there is a requirement to see multiple data in related subforms.