13.3. Forms and Linked Tables
As you learned in Chapter 5, few tables are truly independent. Most are linked to others in a web of relationships. Forms can take advantage of these relationships to show linked information. You can use a single form to view (and edit) information about customers and their orders. Or, you can look at products and product categories. This freedom just isn't possible in the Datasheet view.
Note: Enterprising Access developers use join queries (Section 6.3) to show information from more than one table. However, you can't edit the linked information in a join query. In a properly designed form, you don't have this limitationyou can change the information in both the parent and child records.
13.3.1. Table Relationships and Simple Forms
Access is intelligent enough to notice relationships when you create a new form for a parent table. To see what this ability means in practice, select a table that's the parent of another table. You can use the ProductCategories table in the Boutique Fudge database, because every category's a parent record that's linked to one or more child records in the Products table. (You can also use the Customers table, because customers are linked to orders, or the Orders table, because orders are linked to order items. To try this out, use the Boutique Fudge database that's included with the downloadable content for this chapter.)
Figure 13-20 shows what happens if you select the ProductCategories table, and then choose Create Forms Form. Access creates a form that displays the records you expect (the categories), and the linked records in the child table (in this case, the products).
Note: Don't bother creating a split form or a multi-item form. Access ignores relationships when you create these form types.
If your table's the parent of more than one child table, then Access shows only records from one table. It chooses the first relationship it finds. If this relationship isn't the one you want, don't worryit's easy to change once you understand how the subform control works. The next section has the inside scoop.
13.3.2. The Subform Control
Access shows linked records using the subform control. You can add the subform control to any form to show linked records. It's available in the ribbon's Form Design Tools Design Controls section with all the other controls. If you add it by hand, then Access prompts you to pick the table you want to show.
Three properties determine what the subform control shows. First, the Source Object property identifies the object in the database that has the related records. You can choose an existing table, query, or form.
The next two propertiesLink Master Fields and Link Child Fieldslet you define the way the two tables are related. The master field's the field in the form, and the child field's the field in the source object. In the product category example, the master field's ID (in the ProductCategories table) and the child field's ProductID (in the Products table). Once this link's defined, Access knows how to filter the subform. It looks at the master field, and displays only records that have the same value in their child fields. In Figure 13-20, Access shows only the products in the current category.
Usually, the master field corresponds to the parent table, and the child field's in the child table. However, you can reverse this relationship. You could create a form of products that includes a subform that shows each product's matching category. When you use this approach, the subform includes only a single record (because only one parent's linked to any child).
Now that you understand how the subform control works, you can add it to your forms with wild abandon. There's no reason you can't add several subforms to show a whole collection of related data at once. If you're creating a form for the Customers table, then you could display two subformsone for the orders made by that customer, and one for the payments. You just need two subform controls with different data sources.
Tip: When your form includes a subform, consider using the anchoring features described in Section 18.104.22.168 so the subform grows to fit the available space when the form's resized.
13.3.3. Creating Customized Subforms
When you set the Source Object property to a table or query, Access always displays the linked records in a mini datasheet. If you're intent on customizing every last piece of your form, you may not want that behavior. Interestingly, Access lets you control exactly how linked records are shown, if you do a little more work.
The trick's to set the Source Object to the form that you want to show in the subform control. Then, the form appears in its Default view mode, which is whatever that form's Default View property is set to (see Section 12.3.5). You can show linked records in a tabular or stacked form. Figure 13-21 shows an example.
Depending on the effect you're trying to achieve, you may already have a suitable form lying around ready to use. If you're designing a form for the ProductCategories table, you can use the form you created for the Products table in a subform control. However, you'll often want to use a completely separate form so you can customize it just the way you want. In the products table example, you may want to show products differently in the subform than they appear in their own dedicated form. After all, there's less space available when you use the subform control, so you may choose a more compact format and leave out the report header altogether.
Tip: If you choose to create a dedicated form to use with a subform control, consider indicating that in the name. The name "ProductsInCategory Subform" suggests a form that's designed for use as a subform.
Sometimes, try as you might, there's no way to fit everything in the small subform area of a form. In this case, you have two options: Try to rearrange your subform to make it more compact, or use two separate forms. Section 14.3.1 in Chapter 14 shows how you can use navigation and filtering to show related records in a separate form.