Creating Multitable Forms in MS Access

By Susan Harkins

Inside Microsoft Office 95

A Publication of The Cobb Group

Published June 1997

Sometimes, you'll want to add data to two or more Access tables on the same form. Other times, you'll need to base an entry in one table on data that's stored in another table. In these situations, you'll want to set up and use a multitable form for data entry. For example, Figure A shows a multitable form included with Access' sample Northwind database.

Figure A: The sample Northwind database includes some multitable forms, such as this one.

Although it isn't readily apparent, this Customer Orders form includes information from three tables and is made up of three forms. The top form displays the customer's name, the middle form shows the orders made by that customer, and the bottom form shows the products included in the middle form's selected order. When you choose a different customer, different data appears in the lower two forms. When you choose a different record in the middle form, the bottom form displays data specific to that selected record.

If you didn't use a multitable form, you'd have to display a different screen for each combination of company name, order, and order detail. For example, to display the same amount of information shown in Figure A—one customer, five orders, and three products—you'd have to display 15 individual screens, each containing one item from each of the three tables.

Fortunately, you don't have to go to this trouble, because Access lets you place forms for multiple tables on a single form. In any multitable Access form, you have a main form and one or more subforms. The main form displays data, as any form does, and serves as a container for the subform(s). The subform displays data that for the most part relates to one of the main form's fields. Although the technique may seem difficult, creating a subform is a snap, as we'll show you in this article. Also, the possibilities are almost endless. Once you master this simple technique, you may find yourself redesigning all your forms.

On This Page

Creating the forms
Creating a supplier subform
Using the form and subform
Creating a product subform
Variations

Creating the forms

The best way to show you how to create subforms is by demonstrating the process with an example. Before you begin, you need two tables. The tables must include a common field that you can use to link them. The table's fields must be the same field type and length, although they don't necessarily need to have the same name. For our example, we'll use the Products and Suppliers tables shown in Figure B.

Figure B: We'll create a multitable form for these two tables

Once you've identified the tables you want to use, you should decide which form will be the main form and which will be the subform. Let's suppose you primarily want to view the products but you also want to know each product's supplier. In this case, you'll want to display product data in the main form and supplier data in the subform.

Let's begin by creating the product form. In the Database window, choose the Forms tab and click the New button. Highlight the AutoForm: Columnar option, choose the Products table in the dropdown list box at the bottom of the New Form dialog box, and then click OK. Next, click the Save button (), enter the name F_Products in the Save As dialog box, and click OK.

At this point, you might want to add a title to the form. To do so, choose the Design View option () on the Form View dropdown list, increase the size of the Form Header band, choose the Label tool () from the Toolbox, drag to draw a text box, and type Pet Care Products in the label box. You can increase the label's font size by selecting the box and choosing a larger number in the Font Size box on the Formatting toolbar. Be sure to save the revised form.

You create the subform in the same manner; it becomes a subform only when you place it on a main form. However, you'll want to format it so it will display its information as compactly as possible. To create the supplier form for this example, return to the database window and follow the previous procedure. This time, however, use the text Pet Care Suppliers for the label and save the form as F_Suppliers. Finally, close the supplier form.

Creating a supplier subform

To add F_Suppliers to F_Products, open F_Products in Design view, click the F_Suppliers form in the Database window, and drag it to the main form. The main form will instantly resize to include the subform. (It doesn't matter where you drop the subform right now—you can move it later.) Next, double-click the main form's title bar to maximize the form so you can position the field controls and the subform more easily. We positioned the subform to the right of the product table's field controls, as shown in Figure C. We also deleted the supplier form's label.

Figure C: In Design view, position the main form's product data at the left and the subform's supplier data at the right.

To view the form at this point, click Form View () on the Form Design toolbar. As you can see in Figure D, the main form displays the first record in the Products table, while the subform displays the first record in the Suppliers table. If you browse the product records, you'll notice that the records in the subform don't change. That's because you haven't linked the two forms yet. This is your next step.

Figure D: The form displays information from both tables, but the data doesn't appear to be related

Since you want the record displayed in the subform to relate to the record displayed in the main form, you'll need to link the two forms. To do so, return to Design view by clicking Design View on the Form View toolbar. Right-click the supplier subform and choose Properties from the shortcut menu to open the subform's property sheet.

To link the subform to the main form, you must link a duplicate field—generally, you link a primary key field in the subform table. In this case, the most obvious link is the Supplier field. So click the property sheet's Data tab and enter the reference Supplier for both the Link Child Fields and the Link Master Fields properties, as we've done in Figure E. At this point, choose Save As/Export… from the File menu, select the Within The Current Database As option, and specify the name F_Products&Suppliers. (We're giving the form and subform a new name because we're going to use our basic forms again later.)

Figure E: To link the forms, specify the Supplier field for both the Link Child Fields and Link Master Fields properties.

Using the form and subform

Now, return to Form view and browse the product records again. This time, the supplier data in the subform will change as you move to a product record that lists a different supplier. For instance, the supplier information is the same for records 1 through 3 in the Products table. As you move to record 4, which has different supplier data, Access will update the supplier data in your subform, as shown in Figure F.

Figure F: Once you link the two forms, Access will display the appropriate supplier data as you browse the product records.

Creating a product subform

Now let's suppose you want to display a supplier form but you need to display each supplier's products as well. To do so, you'll follow the same process we used earlier. But this time, F_Suppliers will be the primary form and F_Products will be the subform. To create this multitable form, you need to close the F_Products&Suppliers form and restore the Database window. Next, open F_Suppliers in Design view and then drag F_Products from the Database window to F_Suppliers. (Again, it doesn't matter where you drop the form at this point.) Now, maximize F_Suppliers so that it's easier to position the controls. Once you've done this, select the subform labeled Pet Care Products and position it to the right of the supplier field controls. Then, delete the subform's label.

To create the links, right-click the subform and choose Properties from the shortcut menu. Assign Supplier as both the Link Child Fields and Link Master Fields properties. Finally, save the form as F_Suppliers&Products using the File menu's Save As/Export… command.

Now, click the Form View button's Form View option () on the Form Design toolbar. As you can see in Figure G, the subform's navigational scrollbar—which displays the record indicator Record: 1 of 1—shows that there's only one record for the first supplier, Cats Pajamas. That's because we purchased only one item from this particular supplier.

Figure G: The subform's record indicator tells us that we carry only one of the Cats Pajamas products.

Display the record for the second supplier, Dog Gone Best. When you do, Access displays its first product, Doggie Dandies, in the product subform. But notice that the navigational scrollbar in the subform displays Record: 1 of 2. This means the subform contains a second record. Click the subform's Next button (), and Access displays a second product for Dog Gone Best—Puppy Love. If you continue in this manner, you'll find that Got Your Goat! supplies two of our products, Hare Care supplies three, and Kitty Palace supplies just one.

Variations

The technique we've shown you in this article barely scratches the surface of designing forms that contain subforms. For example, Access lets you make a subform invisible if its corresponding field in the main form is empty. In addition, you can design pop-up subforms that appear only when your cursor is on the related field in the main form. As the Northwind form shown in Figure A demonstrates, you can also link a subform to another subform that's linked to the main form.

If you'd like to explore some other possibilities, open the forms in Access' sample databases in Design view and then look at the forms' and subforms' properties sheets. The Data and Event tabs are particularly useful for learning how to relate information between a form and its subforms. In addition to the Northwind database, which is stored in the Access\Samples directory, you can make Access create databases with sample data by choosing New Database… from the File menu, clicking the Databases tab, and selecting a sample file. The Database Wizard will lead you through the rest of the process of creating the sample database.

Susan Harkins is editor-in-chief of The Cobb Group's Inside Microsoft Access

The article entitled "Creating Multitable Forms in Access" was originally published in Inside Microsoft Office 95, June 1997. Copyright © 1997, The Cobb Group, 9420 Bunson Parkway, Louisville, KY 40220. All rights reserved. For subscription information, call the Cobb Group at 1-800-223-8720.

We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as is," without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement , and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.