A subform is a form embedded inside a parent form that shows related data, typically the many side of a one-to-many relationship.
Before we can add a subform to a main form we have to define the subform. To do this create a new form using the wizard as before and choose the Tracks table as the source. Choose Tabular as the layout so the tracks will appear in rows. Edit the new form in Design view to create a suitable layout.
To convert the CDID field to a combo box click repeat the procedure described in the previous section. on each in turn and choose Format/Change To/Combo Box. Then right click the field and choose Properties. Select the Row Source property and click the dieresis to build a query for the Row Source.
Select the Row Source property and click on the dieresis to run the SQL query generator. Choose the CDs table and close the tables box. Choose the SerialNumber field and then close the query, clicking Yes to save the SQL. In this case there is no need to add 'DISTINCT' before the field name but for other fields this may be useful.
We also created a form for the CDs table so either open this now or else make a new one with the Form Wizard. In Design view extend this form at the bottom and on the right so there is room for the Tracks subform. Click the subform button in the Toolbox and then drag out a rectangle for the subform. The SubForm Wizard now starts. Choose either a form or a table or query for the subform:
The next step of the wizard requires you to specify the relationship between the main form and the subform.
The list shows a reasonable description of what we want, tracks displayed by the SerialNumber field in the CDs table, so we can accept it without defining our own links. Finish the wizard by accepting the name and we end up with something like this:
This form now includes all the information about a CD and all the tracks on any CD. This form can be used to view CDs and their related tracks or it can be used to create new records and edit existing ones. The one thing that cannot be done with a form like this is to edit the primary key of the CDs table as this has records linked to it and this would cut them off from their parent.
With four tables now in the database, relationships defined between those tables and a series of queries and forms the music application is already quite complex. Careful thought must be given to the way in which data will be entered into the database. For example, the Labels and MusicType tables need to be edited separately as they are used primarily for lookups. On the other hand we might develop some additional forms for these tables in relation to the CDs table so that users can look up the definitions of music types or access label information, perhaps using this to send emails to the record companies.
Subforms can be nested within each other for up to 10 levels - a form within a form within a form, etc. In the case of the example above we might embed this form and subform inside another subform, and that one inside another, and so on.
As well as adding subforms to display related data it is also possible to do this using a multi-table query. To do this we create a query based on two or more tables, such as CDs and TrackDetails, add all the fields and then create a new form based on this query. By arranging the fields carefully and using formatting tools such as lines and rectangles we can create roughly the same effect as the form and subform above.