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 contacts 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, 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 many-to-many relationship. The tblContactProducts table establishes the necessary link between the contacts and the products owned. Figure 15-6 shows the relationships.
Figure 15-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.
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 tblContact-Products 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 15-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, so 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 15-7, or use the sample query.
Figure 15-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, although you could use the tblCompanyContacts table as the record source for this form, 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 15-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, so 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 15-8, or use the sample query.
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 Form 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. Now, click Form Wizard in the Forms group on the Create tab. 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 15-9.
Figure 15-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 on this page and then click Next. 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 15-10.
You could probably use this form as is, but we’ll clean it up using the techniques in Design view and Layout view that you learned in previous chapters. Perform the following steps to perfect the design:
Switch to Design view if you are currently not in that view, and then open the Property Sheet window. In the Selection Type combo box at the top of the Property Sheet window, select Form to view the form’s properties. When you started the Form Wizard, you first highlighted a saved query object in the Navigation pane and then selected six fields to display in the form on the first page of the wizard. If you look at the Record Source property of the form in the Property Sheet window, the Form Wizard created a Structured Query Language (SQL) statement that contains only the six fields and embedded the SQL statement into the form’s Record Source property. Although we want to display only these six fields on the form, we still need the other fields in the underlying query to establish linking fields later in this chapter. Click the drop-down list of saved table and query objects on the Record Source property and select either the qryContactProductsSub query you built or the sample qxmplContactProducts query.
Delete the title label that Access created in the Form Header section on the form design grid. Because you’ve deleted the title, you can now move all the labels closer to the top of the form. Click in the Ruler to the left of the first label, Product, to highlight all the label controls in the Header section. Now, drag the labels up near the top of the form. Drag the bottom of the Form Header section up close to the label controls to get rid of the extra spacing in this section that we no longer need.
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.
All the controls are set to the Tahoma font with a font size of 8. Let’s change the font of these controls to use MS Sans Serif. To select all the labels and text boxes, click in the Ruler to the left of the first label control in the Form Header section, and then drag your mouse down the Ruler to the left of the first text box control in the form Detail section. Release the mouse and Access highlights all the label and text box controls in both sections. Now, select the MS Sans Serif font from the Font list in the Font group on the Format tab.
You can move the labels and text boxes a little closer to the left edge of the form. Click in the Ruler to the left of the first label control in the Form Header section, and then drag your mouse down the Ruler to the left of the first text box control in the form Detail section. Release the mouse and Access highlights all the label and text box controls in both sections. Use your left arrow key to move all the controls closer to the left side of the form grid.
You can reduce the width of the CategoryDescription controls (the Product Type column). Click in the Ruler above the Product Type label to highlight the label and text box, and drag the right edge of the controls closer to the left edge of the form until they are about 1 inch in width. (You can switch the form into Layout view and then scroll through the records to make sure you’ve allowed adequate space in the CategoryDescription.) You now have extra space to the right of the CategoryDescription controls that you don’t need. Click in the Ruler above the Product Name label and then drag your mouse to the right until you highlight the remaining controls. Now, use your left arrow key to move the controls closer to the CategoryDescription controls to close this gap.
The ProductName text box control does not need to be quite so wide. Click in the Ruler above the Product Name label to highlight the label and text box, and drag the right edge of the controls 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 in Layout view. Switch back to Design view when you are finished. The Form Wizard initially created this text box to be two lines high, but this is unnecessary now. Click the 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 the text box control, but now you have 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 To Fit command under the Size/Space command in the Sizing & Ordering group on the Arrange tab. Now, move the UnitPrice, DateSold, and SoldPrice controls farther to the left so they are next to the ProductName controls again.
The two price text box controls are wider than necessary, so you should reduce the width of these controls as well. Click in the Ruler above the UnitPrice label (the first Price label), hold down the Shift key and then click in the Ruler above the SoldPrice label (the second Price label) to select these four controls, 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. The DateSold control is currently too narrow to display the dates, so click in the Ruler above these controls, move your mouse pointer to the left edge of the control until it becomes a double-sided arrow, and then drag the left edge to expand the width until it is next to the UnitPrice control.
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”. (In metric, the measurements would be about 0.635 cm, 3.81 cm, and 5.079 cm, respectively.)
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.
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.
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 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 under the Align command in the Sizing & Ordering group on the Arrange tab, which 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.
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 (qryContactProductsSub). The result of your work should look something like Figure 15-11.
You can switch to the subform’s Form view to check your work. You can see the Continuous Forms view in Figure 15-12. Because this form isn’t linked in 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.
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 15-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.
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 Format tab to give all your labels a default bold font. Click the Text Box button in the Controls group on the Design tab, and then, in the Property Sheet window, change the Special Effect property to Flat, the Label Align property to Right, and the Label X property (the offset of the label to the right) to –.05”. (For metric, set the Label X property to –.15 cm.) Note that when you click the Text Box button in the Controls group, the top of the Property Sheet window shows Default Text Box.
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 these fields together onto your form about 2 inches from the left edge and near the top of the Detail design area. Drag 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 Sizing & Ordering 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 15-13.
You can use a couple of techniques to embed a subform in your outer form. First, you can cancel the selection of the Control Wizards button in the toolbox, 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 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 place it on 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 it onto your form at the left edge below the Position label and text box. Figure 15-14 shows this action in progress.
Figure 15-14. You can drag one form from the Navigation pane to 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 To Fit button under the Size/Space command in the Sizing & Ordering 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 15-15.
Figure 15-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 To Fit under the Size/Space command in the Sizing & Ordering group von 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 15-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 15-16.
Figure 15-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 15-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.
Inside Out: Use the Build Button to Set Master/Child Links
If you don’t want to type in the link field properties, you can also click the Build button on either the Link Master Fields or Link Child Fields property line to open the Subform Linker Dialog box shown here.
You can select the Master and Child fields from the respective combo boxes in the dialog box. When you click the drop-down lists, Access also displays the data type of each field next to the field name. Click Suggest and Access provides suggested link properties for the two forms.
Figure 15-17. You now have a form to display company contact information with a subform that displays the related products owned.
We don’t know 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 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.
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 9, 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 15-18. You can find this query saved as qxmplContactsSorted in the sample database.
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 Text Box, and change Label Align to Right and Label X (the offset of the label to the right) to –0.05”. (Use –0.15 cm for metric.) 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. Click Combo Box, and make the same adjustments to the default Label Align, Label X, Border Color, and Special Effects 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 the field about 1 inch from the left margin near the top of the design area. Add the Title, LastName, and Suffix fields, one at a time, in a column under ContactType. In a row aligned with the LastName text box, drag 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 15-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 frmXmplContacts1 in the sample database.
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 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 under the Size/Space command in the Sizing & Ordering group on the Arrange tab to properly size the subform control. Your result should look something like Figure 15-20. Save your form as frmContactsProducts. You can find this form saved as frmXmplContactsProducts in the sample database.
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 15-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 to work with the inner form easily. However, you cannot click the File tab on the Backstage view 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, right-click the subform control, and then click Subform In New Window.
Switch to Form View to see the completed form, as shown in Figure 15-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 15-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 Microsoft 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 25, “Automating Your Application with Visual Basic,” on the companion CD.
Inside Out: Using Subreports in Forms
Access 2010 now includes the ability to embed reports directly into forms as subreports. The procedures and usage for subreports are the same as the ones for subforms that you just learned about. You can set the Source Object property to the name of a report and you can optionally set the Link Child Fields and Link Master Fields properties to have the subreport display matching values as you tab through the records in the main form.
In Chapter 9, you learned how to define a subdatasheet for a query. You can do the same thing with forms so long as the forms are saved to display 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 File tab on the Backstage view 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 15-22.
Notice that several of the columns are much wider than they need to be and the DateSold column is too narrow. 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 15-11) and are 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 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 is.
Switch to Datasheet view, and your form should look like Figure 15-23.
Figure 15-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 15-15), the captions from those labels display 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 15-24.
Figure 15-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.