Adding a Subform to a Form | MicrosoftВ® Office ExcelВ® 2003 Step by Step (Step By Step (Microsoft))

A form can display information (fields) from one or more tables or queries. If you want to display fields from several tables or queries in one form, you have to give some thought to the relationships that must exist between those objects.

In Access, a relationship is an association between common fields in two tables, and you can use it to relate the information in one table to the information in another table. For example, in the GardenCo database a relationship can be established between the Categories table and the Products table because both tables have a CategoryID field. Each product is in only one category, but each category can contain many products, so this type of relationship ”the most common ”is known as a one-to-many relationship .

As you create forms and queries, Access might recognize some relationships between the fields in the underlying tables. However, it probably won t recognize all of them without a little help from you.

Other Types of Relationships

In addition to one-to-many relationships, you can create one-to-one relationships and many- to-many relationships , but they are not as common.

In a one-to-one relationship, each record in one table can have one and only one related record in the other table. This type of relationship isn t commonly used because it is easier to put all the fields in one table. However, you might use two related tables instead of one to break up a table with many fields, or to track information that applies to only some of the records in the first table.

A many-to-many relationship is really two one-to-many relationships tied together through a third table. For example, the GardenCo database contains Products, Orders, and Order Details tables. The Products table has one record for each product sold by The Garden Company, and each product has a unique ProductID. The Orders table has one record for each order placed with The Garden Company, and each record in it has a unique OrderID. However, the Orders table doesn t specify which products were included in each order; that information is in the Order Details table, which is the table in the middle that ties the other two tables together. Products and Orders each have a one-to-many relationship with Order Details. Products and Orders therefore have a many-to-many relationship with each other.In plain language, this means that every product can appear in many orders, and every order can include many products.

In this exercise, you will first define the relationship between the Categories and Products tables in the GardenCo database. You will then add a subform to a form. For each category displayed in the main form, this subform will display all the products in that category.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Office 2003 SBS\Forms\Subform folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

On the Database toolbar, click the Relationships button to open the Relationships window.
If the Show Table dialog box isn t displayed, on the toolbar, click the Show Table button. Then double-click Categories and Products in the list displayed. Close the Show Table dialog box to view the Relationships window.
Click CategoryID in one table, and drag it on top of CategoryID in the other table.

Access displays the Edit Relationships dialog box, which lists the fields you have chosen to relate and offers several options.

Select the Enforce Referential Integrity check box, select the other two check boxes, and then click Create .

Tip

Access uses a system of rules called referential integrity to ensure that relationships between records in related tables are valid, and that you don t accidentally delete or change related data. When the Cascade Update Related Fields check box is selected, changing a primary key value in the primary table automatically updates the matching value in all related records. When the Cascade Delete Related Records check box is selected, deleting a record in the primary table deletes any related records in the related table.

Access draws a line representing the one-to-many relationship between theCategoryID fields in each of the tables.

Tip	You can edit or delete a relationship by right-clicking the line and clickingthe appropriate command on the shortcut menu.

Close the Relationships window, and click Yes when prompted to save the window s layout.
Open the Categories form in Design view.
Enlarge the Form window, and drag the Form Footer section selector down about1 inch to give yourself some room to work.
If the Toolbox isn t displayed, click the Toolbox button.
Make sure the Control Wizards button in the Toolbox is active (orange).
Click the Subform/Subreport button, and drag a rectangle in the lower portionof the Details section.

A white object appears in the form, and the first page of the Subform Wizard opens.

Tip
If prompted, follow the instructions to install this wizard.
Leave Use existing Tables and Queries selected, and click Next .
In the Tables/Queries list, click Table: Products .
Add the ProductName , CategoryID , QuantityPerUnit , UnitPrice , and UnitsInStock fields to the Selected Fields list by clicking each one and then clicking the > button.
Click Next to display the third page of the wizard.

Because the Category ID field in the subform is related to the Category ID fieldin the main form, the wizard selects Show Products for each record in Categories using CategoryID as the Choose from a list option.

Tip
If the wizard can t figure out which fields are related, it selects the Define my own option and displays list boxes in which you can specify the fields to be related.
Click Next to accept the default selection, and then click Finish , to accept the default name for the subform and complete the process.

Access displays the Categories form in Design view, with an embedded Products subform. The size and location of the subform is determined by the original rectangle you dragged in the form.
Adjust the size and location of the objects in your form as needed to view the entire subform.
Notice the layout of the subform in Design view, and then click View to switch to Form view.

The format of the subform has totally changed. In Design view, it looks like a simple form, but in Form view, it looks like a datasheet.
Switch back to Design view, make any necessary size adjustments, and if necessary, open the Properties dialog box.
Click the Form selector in the upper-left corner of the subform twice.

The first click selects the Products subform control, and the second click selects the form. A small black square appears on the selector.
On the Format tab of the Properties dialog box, change both Record Selectors and Navigation Buttons to No .

While on this tab, notice the Default View property, which is set to Datasheet. You might want to return to this property and try the other options after finishing this exercise.
Close the Properties dialog box, switch back to Form view, and drag the dividers between column headers until you can see all the fields.

Tip
You can quickly adjust the width of columns to fit their data by double-clicking the double arrow between column headings.
Click the navigation buttons to scroll through several categories. When you are finished, click the First Record button to return to the first category (Bulbs).

As each category is displayed at the top of the form, the products in that category are listed in the datasheet in the subform.
Click the category name to the right of the first product.

The arrow at the right end of the box indicates that this is a combo box.
Click the arrow to display the list of categories, and change the category to Cacti .
Click the Next Record navigation button to move to the next category (Cacti).

You can see that the first product is now included in this category.
Display the list of categories, and then restore the first product to the Bulbs category.

You don t want people to be able to change a product s category, so return to Design view. Then in the subform, click the CategoryID text box control, and press [DEL].

The CategoryID text box and its label are deleted.

Important

You included the CategoryID field when the wizard created this subform because it is the field that relates the Categories and Products tables. The underlying Products table uses a combo box to display the name of the category instead of its ID number, so that combo box also appears in the subform.

Save the form, switch back to Form view, and then adjust the width of the subform columns and the size of the Form window until you can clearly see the fields.
Close the Categories form, saving your changes to both the form and the subform.

CLOSE the GardenCo database.

Creating a Form and Subform by Using a Wizard

If you know when you create a form that you are going to add a subform, you can do the whole job with the Form Wizard, like this:

To create the form in your database, on the Objects bar, click Forms , and then click the New button on the database window s toolbar.
Click Form Wizard , select the form s base table from the list at the bottom of the page, and then click OK .
Verify that the table you selected is shown in the Table/Queries list, and then click the >> button to include all the fields in the new form.
To create the subform, display the Tables/Queries list, and click the name of the subform s base table.
Double-click the desired fields to add them to the list of selected fields, and then click Next .
Accept the default options, and click Next .
Accept the default Datasheet option, and click Next .
Click Finish to create the form and subform.

You can then clean up the form to suit your needs, just as you did in the previous exercise.