Creating a Subform

Some forms use fields from multiple tables. One of the most common forms involves a one-to-many relationship between two tables. For example, an order form would include a single order date and customer, but the order might involve several different products. Thus there are two tables involved: an orders table with information about the order and a detailed orders table with data about the products purchased. The user should not have to enter the order date for each product. This can be avoided with a subform, a form embedded within a main form. The user enters the order date and other general information in the main form, and the individual products are listed in the subform. Access then stores the appropriate data in each table without the user being aware that multiple tables are involved.

Create a Subform

In the Database window, click Forms on the Objects bar, and then double-click the Create Form By Using Wizard icon.

Click the Tables/Queries list arrow, and then select the table that will appear in the main form.

The table contains general information (the one table).

Select the fields from the table that will appear in the main form. Make sure you include the common field that links the one table to the many table.

Click the Tables/Queries list arrow, and then select the table that will appear in the subform.

The table contains detailed information (the many table).

Select the fields from the table that will appear in the subform. Do not include the common field you entered in the previous step, since this will appear in the main form.

Click Next to continue.

Click the option to view the data by the one table.

Click the Form With Subform(s) option.

Click Next to continue.

Specify whether you want the subform to be laid out in tabular or datasheet format. Click Next to continue.

Specify a style for the form. Click Next to continue.

Enter a name for the form and subform. Specify whether to open the form for viewing or to modify the form's design in Design view.

Click Finish.

The form and subform are ready for data entry or further editing.

Did You Know? You can create a form with tables in a one-to-one relationship. If the tables have a one-to-one relationship, use the Form Wizard to create the form, including the common field only once in the field list. The wizard will create a single form, combining the fields from both tables. You can create a linked form. If you want a linked form instead of a subform (so that the form appears in response to the user clicking a button), click the Linked Forms option when the Form Wizard asks you how you want to view your data.