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.
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 DocumentsMicrosoft PressAccess 2003 SBSFormsSubform folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 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.
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 the CategoryID fields in each of the tables.
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 about 1 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 portion of the Details section.
A white object appears in the form, and the first page of the Subform Wizard opens.
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 field in the main form, the wizard selects "Show Products for each record in Categories using CategoryID" as the "Choose from a list" option.
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.
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 .
The CategoryID text box and its label are deleted.
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.