In the Northwind database, open the Tools menu and click Relationships. In the Relationships window, note that the Customers table has a one-to-many relationship with the Orders table, whereas the Orders table has a one-to-many relationship with the Order Details table. Therefore, you can create a form with two subforms that will show the orders for each customer, as well as the order details (such as product IDs, quantities, etc.) of each order. Close the Relationships window.

In the Database window, click Forms and then double-click Create Form By Using Wizard. In the first dialog box within the Form Wizard, open the Tables/Queries drop-down menu. Use the vertical scrollbar to move to Table:Customers and select it. In the Available Fields pane, double-click CompanyName, ContactName, ContactTitle, and Phone to move them to the Selected Fields pane. These will be the fields in your main form.

Now let’s add the fields for the two subforms. Open the Tables/Queries drop-down menu and choose Table:Orders. Double-click OrderID, OrderDate, and ShipVia to add them to the Selected Fields pane. Open the Tables/Queries drop-down menu again and choose Table:OrderDetails. Double-click ProductID, UnitPrice, and Quantity. Click Next.

The dialog box you see on-screen now determines how the form/subform will be organized. Please note that Access has recognized the relationships among the tables you used for the form/subform, and it suggests a data hierarchy that puts customer data first, order data second, and order details last. In other words, as you move from customer to customer in the main form, you will see the customer’s orders in the first subform below. And as you move from order to order in the first subform, you will see the order details of each order in the second subform.

Access also lets you display your data By Orders or By Order Details. However, the By Customers choice is by far the most powerful and useful, so keep this selection as well as the Forms With Subform(s) selection. Click Next.

Access can display subforms in several ways. Most frequently, a simple datasheet will do, but a pivot chart can add a nice graphical touch. Keep the default datasheet selection for both subforms and click Next.

In this dialog box, it’s time to choose a style. Select Standard and click Next. The dialog box that appears on-screen now is where you can name your form and subforms. Edit the names in the fields to SC Customers, SC Orders Subform, and SC Order Details Subform, respectively. Leave the other options as they are and click Finish. Access will then create your form/subform.

Put Your Data To Good Use

Click the maximize button in the upper-right corner of your form. As you can see, this form could use some design changes to make it easier to view and navigate. But for now, let’s concentrate on learning how the form/subform works.

Note that this form/subform has three navigation bars: one below each of the two subforms and another one at the bottom of the window for the main form. In the main form’s navigation bar click the Next Record button three times (or edit the specific record field to 4 and press ENTER) to go to the Around the Horn customer record .

The orders for this customer are in the first subform, so click the field for Order ID 10383. As you’ll notice, the Order Details subform (shown below the first subform) changes to show the product data for this particular order.

You can now close the form/subform. In the Database window that now appears on-screen, you’ll see that Access added the SC Customers form, SC Orders subform, and SC Order Details subform, and each one is a separate object in your database.

Home Access 2003 Forms Use the Form Wizard to Create a Form and Subform
Search MS Office A-Z   |   Search Web Pages/ Design A-Z