Creating and Embedding Subforms

If you want to show data from several tables and be able to update the data in more than one of the tables, you probably need to use something more complex than a standard form. In the Conrad Systems Contacts database, the main contact information is in the tblContacts table. Contacts can have multiple contact events and might be associated with more than one company or organization. The information about companies is in the tblCompanies table.

Because any one contact might belong to several companies or organizations and each company probably has many contacts, the tblContacts table is related to the tblCompanies table in a many-to-many relationship. See Chapter 4, “Creating Your Database and Tables,” for a review of relationship types. The tblCompanyContacts table provides the link between companies and contacts.

Similarly, a particular contact within a company might own one or more products, and a product should be owned by multiple contacts. Because any one contact might have purchased many different products and any one product might be owned by multiple contacts, the tblCompanyContacts table is related to the tblProducts table in a manyto-many relationship. The tblContactProducts table establishes the necessary link between the contacts and the products owned. Figure 13–6 shows the relationships.

Figure 13–6: The Relationships window in the Conrad Systems Contacts application shows the relationships between companies, contacts, and products.

When you are viewing information about a particular contact, you also might want to see and edit the related company information and the product detail information. You could create a complex query that brings together the desired information from all five tables and use a single form to display the data, similar to the many-to-one employees form you built in the previous section. However, the focus would be on the contact products (the lowest table in the one-to-many relationship chain), so you would be able to see in a single form row only one product per row. You could design a form that has its Default View property set to Continuous Forms, but you would see the information from tblContacts and tblCompanyContacts repeated over and over.

Subforms can help solve this problem. You can create a main form that displays the contact’s information and embed in it a subform that displays all the related rows from tblCompanyContacts. To see the related product information, you could then build a subform within the form that displays the tblCompanyContacts data to show the product information from tblContactProducts.

Specifying the Subform Source

You can embed up to 10 levels of subforms within another form (a form that has a subform that also has a subform, and so on). It’s best to start by designing the innermost form and working outward because you must design and save an inner form before you can embed it in an outer one. In this exercise, you need to build a form on tblContactProducts, embed that in a form that shows data from tblCompanyContacts, and then finally embed that form and subform in a form to display contact information. But first, you must create the record sources for these subforms. Begin by designing the data source for the first subform.

In the example described previously, you want to create or update rows in the tblContactProducts table to create, modify, or delete links between company contact records in the tblCompanyContacts table and products in the tblProducts table. You could certainly base the subform directly on the tblContactProducts table and display the product name via a combo box on the form that looks up the name based on the value in the ProductID field. However, the user might find it useful to have the current list price for the product displayed to be sure the product isn’t being sold at the wrong price. To do that, you need a query linking tblContactProducts and tblProducts.

Begin by opening the ContactsDataCopy.accdb sample database, and then start a new query in Design view. In the Show Table dialog box, add the field lists for the tblContactProducts and tblProducts tables to the Query window, and then click Close. You want to be able to update all the fields in the tblContactProducts table, so copy them to the design grid. You can do so by using the all fields indicator (*). Add the ProductName, CategoryDescription, UnitPrice, and TrialVersion fields from the tblProducts table.

Your query should look similar to the one shown in Figure 13–7. (This query is saved as qxmplContactProducts in the sample database.) Notice that the tblProducts table has a one-to-many relationship with the tblContactProducts table. This means that you can update any field in the tblContactProducts table (including all three primary key fields, as long as you don’t create a duplicate row) because the tblContactProducts table is on the many side of the relationship. Save and close the query so that you can use it as you design the subform. You can save your query as qryContactProductsSub, as shown in Figure 13–7, or use the sample query.

Figure 13–7: You can use this query to update the tblContactProducts table from a subform while displaying related information from the tblProducts table.

Next, you need a query for the form to display the information from tblCompanyContacts. You’ll embed a subform to display contact products in this form and ultimately embed this form in the outermost form to display contact data. Again, you could use the tblCompanyContacts table as the record source for this form, but you might want to display additional information such as company name and department name from the related tblCompanies table. You also want to restrict the rows displayed to the one row for each contact that defines the default company for the contact.

Start a new query on the tblCompanyContacts table. Add the tblCompanies table to the design grid. You should see a link between the two tables on the CompanyID field in each. Close the Show Table dialog box after you add the two tables. In the design grid, include the CompanyID, ContactID, Position, and DefaultForContact fields from tblCompanyContacts. Under the DefaultForContact field, enter a criterion of True to restrict the output to the records that define the default company for each contact. Add the CompanyName and Department fields from the tblCompanies table.

Your query should look like the one shown in Figure 13–8. (This query is saved as qxmplContactCompaniesDefault in the sample database.) Notice that the tblCompanies table has a one-to-many relationship with the tblCompanyContacts table. This means that you can update any field in the tblCompanyContacts table (including the primary key fields, as long as you don’t create a duplicate row) because the tblCompanyContacts table is on the many side of the relationship. Save the query so that you can use it as you design your form. You can save your query as qryContactCompaniesDefault, as shown in Figure 13–8, or use the sample query.

Figure 13–8: You can use this query to update the tblCompanyContacts table from a subform while displaying related information from the tblCompanies table.

You’re now ready to start building the forms and subforms.

Designing the Innermost Subform

For the innermost subform, you’ll end up displaying the single ProductID field bound to a combo box that shows the product name. After you choose a ProductID, you want to show the user the product category, name, and list price-but in controls that can’t be updated. (You don’t want a user to be able to accidentally change product names and list prices via this form!) Of course, you need the DateSold and SoldPrice fields from tblContactProducts so that you can update these fields.

For this purpose, you could use a form in either Datasheet or Continuous Forms view. It’s simple to build a subform designed to be used in Datasheet view because you need to include only the fields you want to display in the Detail section of the form, without any regard to alignment or placement. Access takes care of ordering, sizing, and providing column headers in the datasheet. However, we like to use Continuous Forms view because that view lets you control the size of the columns-in Datasheet view, a user can resize the columns, including shrinking a column so that it’s no longer visible. Furthermore, if the subform is in Single Form view or Continuous Forms view, the Size To Fit command will make the subform control on the outer form the right size. If the subform is in Datasheet view, however, the Size To Fit command will size the control to the size of the subform in Form view, not to an even number of datasheet rows wide or high. Also, the user is free to resize the row height and column width in Datasheet view, so how you size the subform control in Design view is only a guess.

It turns out that the Form Wizard does a good job assembling this first subform for you. Click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Queries under Filter By Group. Select either the qryContactProductsSub query you built or the sample qxmplContactProducts query in the Navigation Pane. Click More Forms in the Forms group on the Create tab, and then click Form Wizard to start the wizard. You’re going to ask the wizard to create a tabular form, which displays the fields you select in the order you select them in Continuous Forms view.

You don’t need the CompanyID and ContactID fields-as you’ll learn later, the form in which you’ll embed this subform will supply these values via special properties you’ll set in the subform control. First, click the ProductID field to select it, and click the single right arrow to move it to the Selected Fields list. Choose the additional fields you need in this order: CategoryDescription, ProductName, UnitPrice, DateSold, and SoldPrice. (If we had planned ahead, we could have placed the fields in this sequence in the query we’re using as the record source.) Click Next to go to the next page in the wizard, as shown in Figure 13–9.

Figure 13–9: When you use the Form Wizard to build a form on a query using two tables, the wizard offers data layout choices.

Although you won’t take advantage of the wizard features shown on this page this time, it’s interesting to note the options if you click the By tblProducts option. The wizard offers to build a form on tblProducts and a subform on tblContactProducts or to build two separate forms that are linked with a command button. In this case, you want to build a single continuous form, so click By tblContactProducts, and then click Next to go to the next page. Choose the Tabular layout and the None style on the next two pages. On the final page, give your new form a name such as fsubContactProducts, select the Modify The Form’s Design option, and click Finish. Your result should look like Figure 13–10.

Figure 13–10: The Form Wizard created a continuous form to edit contact product information.

You could probably use this form as is, but we’ll clean it up using the techniques in Design view and Layout view you learned in previous chapters. Perform the following steps to perfect the design:

1. Switch to Layout view, and delete the title label that Access created in the Form Header section.

2. Because you’ve deleted the title, you can now move all the labels closer to the top of the form. Click the first label, Product; rest your mouse pointer on the middle of the label until it becomes double-sided crosshairs; and then drag the label up near the top of the form. You’ll notice that Access moves all the other labels as well because these controls are in a tabular control layout.

3. Select all the labels, and click the Bold button in the Font group on the Format tab to make the captions more readable. You’ll notice that Access adjusts the width of all the controls to make room for the larger text.

4. All the controls are set to the Calibri font with a font size of 11. This font and size is too big for our needs, so let’s change the font and reduce the size. To select all the labels and text boxes, click the Product label, move your mouse pointer to the top edge until it becomes a down arrow, click the mouse once, hold down the Shift key, and repeat the process with all the labels until all the controls are selected. Next, select the MS Sans Serif font from the Font list, and select 8. from the Font Size list in the Font group on the Format tab.

5. You can move the ProductID label and text box a little closer to the left edge of the form. Click the Product label, and move your mouse pointer to the middle of the left edge of the control until it becomes a double-sided arrow. Next, drag the left edge of the control closer to the left edge of the form. The ProductID controls are now wider than they were, so you can reduce their width by dragging the right edge of the label control to the left. (When you reduce the width, make sure you can still see the caption in the label.) Access moves all the other controls closer to the left side of the form after you reduce the width of the ProductID controls.

6. Now that you’ve reduced the font size, you could reduce the CategoryDescription controls (the Product Type column) in width. Click the Product Type label, and drag the right edge of the control closer to the left edge of the form. Because you’re looking at the form in Layout view, you can easily scroll through the records to make sure you’ve allowed adequate space in the CategoryDescription.

7. The ProductName text box control does not need to be quite so wide. Click the Product Name label, and drag the right edge of the control closer to the left edge of the form. Make sure you can still see all the data in this control by scrolling through a few of the records. Also, the Form Wizard initially created this text box to be two lines high, but this is unnecessary now. Click the first ProductName text box, move your mouse pointer to the bottom edge of the control until it becomes a double-sided arrow, and then drag the bottom edge up closer until the control is only one line high. Access changes the height of all the text box controls but still leaves a gap below the controls. (We’ll fix this in a minute.) To make sure you’ve sized the text box exactly one line high, you can double-click the edge of the text box or click the Size To Fit command in the Position group on the Arrange tab.

8. The two price text box controls are wider than necessary, so you should reduce the width of these controls as well. Click the UnitPrice label (the first price label), hold down the Shift key and select the SoldPrice label (the second price label), and reduce their width by dragging the right edge of either control closer to the left side of the form. Notice that Access resizes the text box controls for you. Also, because you resized the controls together, they both remain the same width.

9. Now that you’ve used Layout view to help resize the controls, switch to Design view to make the remaining changes. Click anywhere in the Detail section away from any controls to be sure no controls are selected. Right-click the ProductID text box control, click Change To on the menu, and then click Combo Box to convert the text box to a combo box. Open the Property Sheet window, and set Row Source to tblProducts, Column Count to 2, Column Widths to 0.25"; 1.5", and List Width to 2". Access increased the height of the other controls when you changed ProductID to a combo box. To make them all the same height, click the ProductID combo box, hold down the Shift key, and then click the CategoryDescription text box. Next, click the Size To Shortest or the Size To Fit button in the Size group on the Arrange tab.

10. You need to lock the three fields from tblProducts so that they cannot be updated via this form. Click the CategoryDescription text box control, and hold down the Shift key while you click the ProductName text box control and the UnitPrice text box control to add them to the selection. In the Property Sheet window, set Locked to Yes.

11. Because Access originally made the ProductName text box control a two-line control, it will display a vertical scroll bar when you switch to Form view. You sized the control in Layout view to be wide enough to display all product names, so you don’t need the scroll bar. Click the ProductName text box, go to the Property Sheet window, and set the Scroll Bars property to None to ensure that this control does not display a scroll bar.

12. Open the form footer by dragging down its bottom edge. Click the Text Box tool in the Controls group on the Design tab, and drop a text box in the Form Footer section under the SoldPrice text box control. Make your new control the same size as the SoldPrice control, and line them up using the Align Left or Align Right button in the Control Alignment group on the Arrange tab that you learned about in the previous chapter. Click the attached label, set its font to Bold, and in the Property Sheet window type Total: in the Caption property. Now move the label closer to the new text box. Click the new text box, and in the Property Sheet window set Control Source to =Sum([SoldPrice]), Format to Currency, Enabled to No, and Locked to Yes. Finally, select both the new label and text box controls, and change the font to MS Sans Serif and font size to 8.

All you have left to do is to shrink the bottom of the Detail section to eliminate the extra space below the row of controls, reduce the width of the form, select the form, set the form’s Scroll Bars property in the Property Sheet window to Vertical Only (your design should horizontally fit all the fields within the subform control on the main form so that the user won’t need to scroll left and right), and set the Navigation Buttons property to No. (You can use the vertical scroll bar to move through the multiple rows.)

Because you didn’t choose all the fields from the query, the wizard tried to help you out by creating an SQL statement to fetch only the fields you used on the form. You’ll need all the fields for the subform filtering to work correctly. So, delete the SQL statement from the form’s Record Source property, and set the property back to the name of your query (qryContactProducts). The result of your work should look something like Figure 13–11.

Figure 13–11: Here is your subform to edit contact products in Design view.

You can switch to the subform’s Form view to check your work. You can see the Continuous Forms view in Figure 13–12. Because this form isn’t linked as a subform yet (which will limit the display to the current order), the totals displayed in the form footer are the totals for all orders. You can find this form saved as fsubXmplContactProducts in the sample database.

Figure 13–12: This is your contact products subform displayed in Continuous Forms view.

Inside Out-Using a Subform in Datasheet View 

If you’ll be using a subform in Datasheet view when it’s embedded in another form, you have to switch to Datasheet view to adjust how the datasheet looks and then save the subform from Datasheet view to preserve the look you want You must also use the Datasheet view of the form to make adjustments to fonts and row height The font in Datasheet view is independent of any font defined for the controls in Form view.

Also, if you build a tabular form such as the one shown in Figure 13–12 and then decide to use it as a subform in Datasheet view, you will see the field names as the column headings rather than the captions. In Datasheet view, columns display the defined caption for the field only when the bound control has an attached label. In a tabular form, the labels are detached from their respective controls and displayed in a separate section of the form design.

Designing the First Level Subform

You can now move on to the form to display the company contact information and act as a link between contacts and contact products. The purpose of the final form will be to view contacts and edit their contact products, so you don’t need to have anything fancy in the middle or allow any updates. To begin, click the Form Design button in the Forms group on the Create tab. Access opens a blank form grid in Design view.

Open the Property Sheet window, and select in the Record Source property either the query you built earlier (qryContactCompaniesDefault) or the sample query we provided (qxmplContactCompaniesDefault).

To make this form easy to build, set some control defaults first. Click the Label button in the Controls group on the Design tab, and click the Bold button in the Font group on the Design tab to give all your labels a default bold font. Click the Text Box button, and change Special Effect to Flat, Label Align to Right, and Label X, (the offset of the label to the right) to −.05".

Open the Field List window by clicking the Add Existing Fields button in the Tools group on the Design tab, and then click the Show Only Fields In The Current Record Source link (if necessary). Click the CompanyID field to select it, and hold down the Ctrl key while you click the CompanyName and Department fields to add them to the selection. Drag and drop these fields together onto your form about 2, inches from the left edge and near the top of the Detail section. Drag and drop the Position field onto the form directly below Department. If you have Snap To Grid turned on, it should be easy to line up the controls. Otherwise, select all the text box controls, and use the Align buttons in the Control Alignment group on the Arrange tab to line them up. Set the Locked property of all text box controls to Yes. Select the label control attached to the CompanyID text box, and change the caption from Company/Org.: to Company ID:. At this stage, your design should look like Figure 13–13.

Figure 13–13: Your form to display company contact information is now beginning to take shape.

Embedding a Subform

You can use a couple of techniques to embed a subform in your outer form. First, you can cancel the selection of the Use Control Wizards button in the Controls group on the Design tab, select the Subform/Subreport tool in the Controls group, and then click the upper-left corner of the outer form’s empty area and drag the mouse pointer to create a subform control. (If you leave the Use Control Wizards button selected, Access starts a wizard to help you build the subform when you place a subform control on your outer form. Because you already built the subform, you don’t need the wizard’s help.) After you have the subform control in place, set its Source Object property to point to the subform you built (or use the sample fsubXmplContactProducts).

A better way to embed the subform is to expand the Navigation Pane, find the form you want to embed as a subform, and then drag it from the Navigation Pane and drop it onto your form. To do this, expand the Navigation Pane if you collapsed it, open the Navigation Pane menu, click Object Type under Navigate To Category, and then click Forms under Filter By Group to display the list of forms in the database. Click the subform you built in the previous section (or the fsubXmplContactProducts form that we supplied), and drag and drop it onto your form at the left edge below the Position label and text box. Figure 13–14 shows this action in progress.

Figure 13–14: You can drag and drop one form from the Navigation Pane onto the Design view of another form to create a subform.

Adding a subform in this way has the advantages that your new subform control will be sized correctly horizontally, will have a height to display at least one row, and will have some of its other properties automatically set. If the form you are designing has a table as its record source and Access can find related fields of the same name in the record source of the subform you’re adding, then Access automatically defines the link properties as well. You’ll have to set these properties yourself later in this exercise.

You don’t need the label that Access added to your subform control, so you can select it and delete it. Click the subform control to select it (if you click more than once, you’ll select an object on the form inside the subform control), drag the sizing handle in the middle of the bottom of the control so that it is about 2, inches high, and then click the Size To Fit button in the Size group on the Arrange tab to correctly size the control to display multiple rows. Move up the bottom of the Detail section of the outer form if necessary so that there’s only a small margin below the bottom of the resized subform control. Your form should look something like Figure 13–15.

Figure 13–15: The contact products subform, embedded in your form, displays the products owned by a company contact.

Inside Out-Sizing a Subform Control 

Sizing a subform that you display in Form view is quite simple. You might need to do this if you create the subform control directly on the form. Select the subform control, and then click Size To Fit in the Size group on the Arrange tab. In this case, you’re using a subform in Continuous Forms view, so Access will size the subform control to the correct width and to the nearest vertical height to fully display rows in the Detail section. Note that if your subform default view is Datasheet view, using the Size To Fit button won’t work unless the form’s Design view is exactly the same size as the datasheet. You have to switch in and out of Form view and manually adjust the size of the subform control.

You must set a couple of key properties to finish this work. If you remember from Figure 13–6, the tblCompanyContacts table is related to the tblContactProducts table on both the CompanyID and the ContactID fields. When you view records in an outer form and you want Access to filter the rows in the subform to show only related information, you must make sure that Access knows the field(s) that link the two sets of data. With the subform control selected, open the Property Sheet window, and set the Link Child Fields and Link Master Fields properties as shown in Figure 13–16.

Figure 13–16: Set the link field properties of the subform control to tell Access how the data in the outer form is related to the data in the inner form.

The Link Child Fields property refers to the “child” form-the one in the subform. You must enter the names of the fields in the record source of the form inside the subform that should be filtered based on what row you have displayed in the outer form, separated by semicolons. Likewise, the Link Master Fields property should contain the name(s) of the related field(s) on the outer form. In most cases, both properties will contain only one field name, but the names might not be the same. In this case, you know it takes two fields to correctly relate the rows. Switch to Form view, and your form should look like Figure 13–17. As you move from record to record in the outer form, Access uses the values it finds in the field(s) defined in Link Master Fields as a filter against the fields in the subform defined in Link Child Fields.

Figure 13–17: You now have a form to display company contact information with a subform that displays the related products owned.

We don’t know yet which contact owns these products because we haven’t built the final outer form yet to display contact information. You should return to Design view and make some adjustments to the length of the CompanyName, Department, and Position text boxes. You should also set the form’s Scroll Bars property to Neither and the Record Selectors property to No. You really don’t want users adding and deleting records in this outer form, so set Allow Additions and Allow Deletions to No. Save your form as fsubCompanyContactProducts. (Note that if you made any changes to the form inside the subform control, Access will also ask you whether you want to save that form, too.) You can also find this form saved as fsubXmplCompanyContactProducts in the sample database.

Inside Out-Access Might Create the Link for You 

If the record source of the outer form is a single table, Access automatically sets the Link Master Fields and Link Child Fields properties for you when it can find a related field in the table or query that you define as the record source of the form within the subform control. It does this when you either drag the subform to the main form or set the Source Object property of the subform control.

Specifying the Main Form Source

Now it’s time to create the main form. You need a table or a query as the source of the form. You want to be able to view (and perhaps update) the contacts who own the products shown in the form and subform you’ve built thus far, so your row source should include the tblContacts table. You don’t need any other related tables, but you might want to use a query so that you can sort the contacts by name.

Start a new query on the tblContacts table, and include all the fields in the design grid. Add criteria to sort in ascending order under LastName and FirstName. (You’ll recall from Chapter 7, “Creating and Working with Simple Queries,” that the sequence of fields in the design grid is important for sorting, so be sure that LastName is before FirstName in the query design grid.) Save your query as qryContactsSorted. Your query should look something like that shown in Figure 13–18. You can find this query saved as qxmplContactsSorted in the sample database.

Figure 13–18: This query sorts the contact records to be used in a form.

Creating the Main Form

Building the form for the tblContacts table is fairly straightforward. In fact, you can use the Form Wizard to build the basic columnar format form from the query you just created. We recommend that you build this form from scratch as you did to build the form for company contacts because there are only a few fields you need to include, and you want to place them differently than the wizard would. To begin, click the Form Design button in the Forms group on the Create tab. Access opens a blank form grid in Design view. Open the Property Sheet window, and select in the Record Source property either the query you just built (qryContactsSorted) or the sample query we provided (qxmplContactsSorted).

As you did with the company contacts form, set some control defaults first. Click the Label button in the Controls group on the Design tab, and click the Bold button in the Font group to give all your labels a default bold font. Click the Text Box button, and change Label Align to Right and Label X, (the offset of the label to the right) to −0.05". If you have Use Windows-Themed Controls On Forms enabled, also make sure that Border Color is set to #010000 and Special Effect is set to Sunken-your new form should have inherited these values from the Normal form we have saved in the database. Click the Combo Box button, and make the same adjustments to the default Label Align, Label X, Border Color, and Special Effect properties.

Disable Use Control Wizards in the Controls group on the Design tab, open the Field List window, click the ContactType field, and with the combo box control still selected, drag and drop the field about 1, inch from the left margin near the top of the design area. One at a time, add the Title, LastName, and Suffix fields in a column under ContactType. In a row aligned with the LastName text box, drag and drop the FirstName field to about 3 inches out and the MiddleInit field to about 5. inches out. (Access expands the width of the design area when you do this.) You can shrink the MiddleInit text box to about a half-inch wide. Click the ContactType control, and in the Property Sheet window change the Column Widths property to 1.25";0". By default Access set this control to display two columns, but you need to display only the first column. Now change the List Width property to 1.5" to shorten the column display when you open the list in Form view, and change the Column Heads property to No.

The sample design shown in Figure 13–19 has a space at the bottom of the Detail section where you can place the subform to display company contact and product data. You can find this main form saved as frmXmplContactsl in the sample database.

Figure 13–19: This is the start of your main form with space for a subform.

Now you’re ready to add the subform. This time, click the Subform/Subreport button in the Controls group on the Design tab (make sure the Use Control Wizards button is still turned off), and draw the control starting near the left edge under the Suffix combo box and extending to fill the blank area. Select the label control that came with the subform, and delete it. Select the subform control, open the Property Sheet window, and select the fsubCompanyContactProducts form you created earlier (or our sample fsubXmplCompanyContactProducts form) from the list in the Source Object property. Enter ContactID in the Link Child Fields and Link Master Fields properties. Finally, double-click one of the subform control sizing handles, or click the Size To Fit button in the Size group on the Arrange tab to properly size the subform control. Your result should look something like Figure 13–20. Save your form as frmContactsProducts. You can find this form saved as frmXmplContactsProducts in the sample database.

Figure 13–20: The new subform is embedded in the form to edit contacts.

In this case, the ContactID field from tblContacts on the outer form is the link to the related rows on the subform. If you recall, the combination of CompanyID and ContactID forms the link between the forms on the second and third level.

Inside Out-Editing the Form Inside a Subform Control 

Access 2000 introduced a feature that allows you to directly edit your subform after you have defined it as the source for your subform control. As you can see in Figure 13–20, the design of the fsubCompanyContactProducts form is visible in the subform control on the outermost form. Likewise, the design of the fsubContactProducts form is visible inside that You can click any control in the inner forms and change its size or adjust its properties using the property sheet or the contextual Ribbon tabs under Form Design Tools and Form Layout Tools. You might need to temporarily expand the size of the subform control in order to work with the inner form easily. However, you cannot click the Microsoft Office Button and then click Save As to save your changes to a different form definition. If you want to edit the form inside a subform control in its own window, rightclick the subform control, and then click Subform In New Window.

Switch to Form View to see the completed form, as shown in Figure 13–21. Because you properly set the linking field information for the subform controls, you can see the companies for each contact and the products for each company and contact in the subforms as you move from one contact to another. Note that the inner set of navigation buttons is for the first subform. Use the scroll bar in the innermost subform to move through the product detail records. Also, because you locked the controls in the first subform (the company contact information), you cannot edit the controls you see there.

Figure 13–21: You now have a form to edit contacts in a main form and products owned by the contact in subforms.

Note 

If you look at the frmContacts form in the Conrad Systems Contacts application, you’ll see a products subform on the Products tab that has no intervening company contacts subform. This form has some Visual Basic procedures that automatically supply the default company ID for the contact and disallow adding a product if the contact doesn’t have a default company defined. You can see how this code works in Chapter 20, “Automating Your Application with Visual Basic.”

Creating a Subdatasheet Subform

In Chapter 7, you learned how to define a subdatasheet for a query. You can do the same thing with forms as long as the forms are saved to be displayed in Datasheet view. The best way to see how this works is to create modified versions of the three forms you just built.

Start by opening your fsubContactProducts form (or the sample fsubXmplContactProducts form) in Design view. Change the Default View property of the form to Datasheet, change the Allow Datasheet View property to Yes, and save the form as fsubContactProductsDS. (Click the Microsoft Office Button, click Save As, and then click Save Object As. Type the new object name, and then click OK.) Switch to Datasheet view, and your form now looks like Figure 13–22.

Figure 13–22: The contact products subform was changed to be displayed in Datasheet view.

Notice that several of the columns are much wider than they need to be. If you scroll down to the bottom, you don’t see the subtotal that’s in the form footer anymore. Also, because the labels for these fields are in the form header (see Figure 13–11) and not attached to their respective controls, you see the actual field names instead of the field captions. Let’s not worry about the captions for now, but you should adjust the column widths to be more reasonable. You can do that by double-clicking the dividing line to the right of each column heading. This auto-sizes the columns to the widest data (or column caption) displayed. If the data you see isn’t representative of the widest data you might store, you need to adjust the width by hand. You must save the form again to preserve this sizing, so click the Save button on the Quick Access Toolbar, and then close the form. You can find this form saved as fsubXmplContactProductsDS in the sample database.

Next, open your fsubCompanyContactProducts form (or the fsubXmplCompanyContactProducts sample form) in Design view. Change the Default View property of the form to Datasheet. Click the subform control to select it, and change the Source Object property to point to the new datasheet subform you just saved-fsubContactProductsDS. Save the form as fsubCompanyContactProductsDS.

If you like, you can select the form again and change the Subdatasheet Height and Subdatasheet Expanded properties. Because both this form and the embedded subform are set to be displayed in Datasheet view, you can set these properties exactly as you would for a table or query. You can specify a specific height in inches that you want to reserve for the subdatasheet (the subform inside this form). If you leave the default value of 0", the subdatasheet opens to display all available rows when you click the plus sign on any row to expand the subdatasheet for that row. You can also change Subdatasheet Expanded to Yes to always expand all subdatasheets within the subform when you open the form (as though you clicked the plus sign on all displayed rows). For now, leave these properties as they are.

Switch to Datasheet view, and your form should look like Figure 13–23.

Figure 13–23: Your form now displays company contact information in Datasheet view with a subdatasheet to display products.

Because the controls on this form have attached labels (see Figure 13–15), the captions from those labels appear as the column headings. Notice that the subdatasheet form has its columns sized as you saved them when you designed the subform. You can resize the columns in either display and save the form to save the new column width settings. Keep in mind that your user is also free to resize the column widths. However, because these are forms, you have more control over what the user can do than you have in a query. Try to type something in the Company/Organization or Department column. Because the controls in the underlying form are locked, you won’t be able to update this information. Close this form now, and save it if you are prompted.

To finish putting this all together, you can now edit your frmContactsProducts form (or the frmXmplContactsProducts sample form) to use these new datasheet subforms. Open your form in Design view, click the subform control to select it, and change its Source Object property to fsubCompanyContactProductsDS. You also need to make the subform control about 7.75 inches wide because the subform in Datasheet view won’t fit in the current window. However, you can also shorten the height of the subform control to about 1.5 inches.

Save your modified form as FrmContactsProductsDS, and switch to Form view. Your form should now look like Figure 13–24.

Figure 13–24: Your modified form now allows you to edit contacts in a main form and products owned by the contact in subforms displayed in Datasheet view.

Remember that one of the shortcomings of designing your form this way is you have to make a “best guess” at the size of the subform window, and your users can modify the width of the columns in both datasheets as they wish. We personally don’t like this design very much, but you might find it useful to conserve vertical space in a subform design when displaying complex data levels. You can find this form saved as frmXmplContactsProductsDS in the sample database.