Home

An Overview Of Relating Subforms in Access

This is a common requirement in a database application. Here are a few random examples:

• You manage financial transactions in your database. You have a form based on a Bank Deposit, where you use a continuous view or datasheet subform to list all payments received. And then, for each payment, you need to split the amount in order to allocate to potentially more than one account code.
• You are running a tournament for a team sport. For each Grade/League/Division of the tournament, you want to see a list of the teams entered. And then, within the same user interface, you want the ability to see information about the players registered for each team.

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…

Subdatasheet

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:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Conclusion

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.

Steve Schapel