Sectional and Tabbed Forms

Subforms and Subreports

Introduction

A relational database allows you to separate data in objects so that these objects can hold different pieces of information and make data available to other objects that need it. Sooner or later, users constantly need information held by one form or report while they are working on another form or report. There are various ways you can solve such a problem. One solution is to "embed" one form or report into another form or report.

A subform (or a subreport) is a form (or a report) that you position inside of another form (or another report). In order to "include" one form (or report) into another form (or report), both objects must have a relationship. The form (or report) that is hosting the other form (or the other report) is the parent. The form (or report)  that is added to the parent is called the child form (or child report). The parent object must have a primary key that "links" or relates it to a foreign key in the child object.

Microsoft Access provides various techniques you can use to create a subform. Probably the simplest technique consists of using a wizard. This allows you to specify the table or query that is holding the parent records, followed by the table or query that has the child records. Of course, both lists must be able to communicate through the primary key of the parent list and a foreign key in the child list.

To generate a form that contains a subform, start the Form Wizard. In the first page of the wizard, in the Tables/Queries combo box, select the parent object and, in the Available Fields list, select the fields you want to display on the form. Then, in the Tables/Queries combo box again, select the child list. In the Available Fields list, select the fields that the subform should display. Continue with the wizard. In the second page, you must be able to identify the relationship that will control the link between both list. In other words, you must be able to identify the primary key from the parent list and the foreign key from the child list. Once this is clear, you can continue. In the third page of the wizard, you will decide how you want the subform to display, as a tabular list or as a datasheet. After making this decision, you can continue. The fourth page of the wizard allows you to select a preset design you want to apply to the form (and the subform). There is no particular design you need to follow for a subform. After making the selection, you can click Next and Finish. 

Practical Learning: Automatically Creating an Auto-Subform

1. Start Microsoft Access
2. Open the Video Collection3 database you started in in Lesson 35. If you did not create it, from the resources that accompany these lessons, open the Video Collection3a database
3. On the Ribbon, click Create
4. In the Forms section, click More Forms -> Form Wizard
5. In the Tables/Queries combo box, select Table: Directors
6. Click the Select All button 
7. In the Tables/Queries combo box, select Table: Videos
8. Double-click the following fields to select them: Title, CopyrightYear, Length, and Rating
    
   
9. Click Next
10. In the second page, click by Directors (or make sure it is selected)
     
    
     
    Click Next
11. In the 3rd page, click the Tabular radio button
     
    
12. Click Next
13. In the 4th page of the wizard, accept the name of (main) form as Directors.
    Change the name of the subform to sbfVideos
     
    
14. Click Finish
15. After viewing the form, close it

Sub-Forms and Sub-Report Design

The Form Wizard allows you to quickly create a sub-form. This would be fine if you just want to review records associates from one table to another. As effective as it can be, it can also provide some functionality you do not need and do not want. Besides that, the Form Wizard imposes some aspects you must simply accept. The alternative it to design your own subform, and you have various options:

• You can create a form using the Form Wizard, then design a separate subform and add it to the form
• You can create both the form and its subform using the Form Wizard, then modify the design
• You can design both the form and the subform separately, then join them

All these techniques indicate that you should be able to configure the functionality of the subform as it relates to the form that will host it. When designing a subform, you must also specify its appearance. You have the options as datasheet, tabular, or just regular form.

As mentioned in Lesson 8, a form can appear as a datasheet like a table. This makes the form appear with columns and records whose intersections are cells. You can also create a subform that display as a datasheet.

To create a subform that would display as a datasheet, start the form in Design View. In the Property Sheet, set its Default View to Datasheet. When designing the form, you can position the controls anywhere because their positions would not be obvious on the form. Also, the sizes of the labels and fields would not show on the form. When designing the form, you work in Design View. The Datasheet View appears only if you change the view. Although you can use the different sections of a form, for a Datasheet form, the sections would appear only in the Design View, not in Datasheet View.

When adding controls or other objects to a subform (or a form) that would display in Datasheet View. Only the controls you display in the Detail section would appear when the subform comes up. The objects in the Queries sections would not appear. You can use this feature to your advantage. For example you can add unbound controls in a Form Footer section to hold some values or perform some calculations, then refer to those controls in the parent form.

Practical Learning: Using a Datasheet Subform

1. Open the Music Collection1 database 
2. On the Ribbon, click Create
3. To create a new form, in the Forms section, click Form Design
4. Access the Property Sheet for the form and set its Record Source to AlbumTracks
5. Save the form as sbfTracks
6. Reduce the width of the form so its right border is at 2^3/8
7. If the Field List is not displaying, on the Ribbon, click Design and, in the Tools section, click Add Existing Fields .
   In the Field List, click TrackNumber
8. Press and hold Shift
9. Then click TrackLength
10. Release Shift. This selects the TrackNumber, the TrackTitle, and the TrackLength fields
11. Click and drag the selected fields to the Detail section of the form. There is no need to change the positions or locations of the labels and text boxes
12. Click the TrackNumber text box
13. On the Ribbon, click Format and click the Right button
     
    
14. Double-click the button at the intersection of both rulers
15. In the Property Sheet, click Format and change the following properties:
    DefaultView: Datasheet
    Record Selectors: No
    Navigation Buttons: No
16. To switch the form to Datasheet View, right-click its title bar and click Datasheet View
17. Right-click the # column header and click Field Width...
18. Click Best Fit
19. Click the Track Title column header
20. On the Ribbon, click Home
21. In the Records section, click More Field Width
22. Type 24.75
23. Click OK
24. On the table, right-click the Length column header and click Field Width
25. Type 7.85 and press Enter
     
    
26. Save and close the subform
27. In the Navigation Pane, under the Forms bar, double-click MusicAlbums to open it
     
    
28. After viewing it, switch it to Design View and click the Tracks tab on the tab control
29. In the Controls section of the Ribbon, click the More button and make sure the Use Control Wizards option is selected (or click it).
    To add the new subform, in the Controls section of the Ribbon, click the Subform/Subreport button
30. Click the body of the Tracks tab on the form
     
    
31. In the first page of the Subform/Subreport Wizard, in the list of forms, click sbfTracks and click Next
32. In the second page of the wizard, make sure the Choose From A List radio button is selected. Also, in the Select One Of These Links list box, make sure the relationship is based on the AlbumID field. 
    Click Next
33. Accept the suggested name of the subform as sbfTracks and click Finish
34. Click the label that was added to the subform and press Delete (to remove the label). You may also need to move the subform and resize the tab sheet
     
    
35. Save and preview the form
     
    
36. Close the form

A variant of the datasheet is the tabular form. As you may know already, a tabular form displays its records in groups. Instead of showing one record at a time, the form can show as many records as its size allows:

This type of form is referred to as continuous because the records are displayed continuously in the same view. If the number of records is not too high, all of them would display. If there are more records than the form's size can allow to view, some records would be hidden. To view them, you can use the scroll bars.

To create a continuous form, you can use the Form Wizard where, in the second page of the wizard, you would select the Tabular option. To create a continuous form in Design View, set its Default View to Continuous Forms:

Unlike the datasheet form, a continuous form can be equipped with, and can display, other sections than the Detail section in Form View. This means that you can include additional fields in the Queries sections. Based on this, a typical design of a continuous form consists of creating some labels in the Form Header section and positioning their corresponding controls under them but in the Detail section. The fields can be horizontally aligned and adjacent each other. You should (strongly, if not must) avoid including Memo and OLE Objects (pictures, linked documents, etc) in a continuous form because such fields may take too much space. This would deceive the purpose of the continuous form.

Practical Learning: Designing a Subform or Subreport

1. Open the Ceil Inn1 database you started in Lesson 34 and continued in the previous lesson. If you did not create create it, from the resources that accompany our lessons, open the Ceil Inn4 database
2. On the Ribbon, click Create
3. To create a new query, in the Queries section, click Query Design
4. In the Tables tab of the Show Table dialog box, double-click Customers, Occupancies, and Rooms
5. On the Show Table dialog box, click Close
6. In the lists of fields, from the Occupancies list, double-click DateOccupied
7. In the Rooms, double-click RoomNumber
8. In the Occupancies list, double-click RateApplied, PhoneUse, and InternetFee
9. In the Customers list, double-click CustomerID
    
   
10. Save the query as CustomerRoomUse and close it
11. On the Ribbon, click Create
12. To create a new form, in the Forms section, click Form Design
13. Using the Property Sheet, set its Record Source to CustomerRoomUse
14. Save the form as sbfCustomerRoomUse
15. Change the following properties:
    Default View: Continuous Forms
    Record Selector: No
    Navigation Buttons: No
16. Right-click the form and click Form Header/Footer
17. In the Controls section of the Ribbon, click the Label
18. Click just under the Form Header bar on the left side:
     
    
19. Type Date Occupied and press Enter
20. If the same way, add labels with the captions Room #, Rate Applied, Phone Use, and Internet Use
21. Design and apply a font available to you. Here is an example:
     
    
22. To display the Field List, on the Ribbon, click Add Existing Fields
23. From the Field List, drag DateOccupied and drop it somewhere in the Detail section. Click its label to select it and press Delete to remove it
24. In the same way, add the other fields
25. Align them next to each other under their corresponding labels. Design them as you see fit under the Part label but in the Detail section
     
    
26. In the Controls section of the Ribbon, click the Text Box and click under the Form Footer
27. Using the Property Sheet, change its characteristics as follows:
    Name: txtTotalRateApplied
    Control Source: =Sum(RateApplied)
    Format: Fixed
28. Add another text box to the form footer section, change its characteristics as follows:
    Name: txtTotalPhoneUse
    Control Source: =Sum(PhoneUse)
    Format: Fixed
29. Add another text box to the form footer section, change its characteristics as follows:
    Name: txtTotalInternetFee
    Control Source: =Sum(InternetFee)
    Format: Fixed
     
    
30. Click the Form Footer bar and, in the All tab of the Property Sheet, set its Visible value to No
31. Save, preview and close the sub-form
32. In the Navigation Pane, under Forms, double-click Customers
33. After viewing the form, switch it to Design View
34. In the Navigation Pane, under Forms, drag sbfCustomerRoomUse and drop it on the form under the Notes memo
35. Add four text boxes under the subform and design them as follows:
     

    Label's Caption Text Box Name Format Control Source Phone Use: txtPhoneUse Fixed =[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse] Internet Use: txtInternetUse Fixed =[sbfCustomerRoomUse].[Form]![txtTotalInternetFee] Rate Total: txtRateTotal Fixed =[sbfCustomerRoomUse].[Form]![txtTotalRateApplied] Net Pay: txtNetPay Fixed =Nz([txtPhoneUse])+NZ([txtInternetUse])+Nz([txtRateTotal])

36. Save and preview the form:
     
37. Close the form

The Single Form

A single form is the type of form that displays its data one record at a time. This concept, which was used on most forms we have created so far, can also be applied to a sub-form. The single layout can be valuable if you want to show one record from a related form as it is linked to a record on the current form. While a Datasheet form can clearly show that it is an embedded object, the fields of a single form can easily be mixed with those of the hosting form but it is usually determined to be apart.

A single subform is designed like a regular form where fields are created in the Detail section and other optional controls can be added to the other sections of the form. When selecting the fields that would be part of the subform, make sure you include only those that can be useful in the form that will host the subform.

Practical Learning: Designing a Single Subform

1. Open the Bethesda Car Rental2 database you started in Lesson 35 and continued in the previous lesson
2. On the Ribbon, click Create
3. To create a new form, in the Forms section, click Form Design
4. Save the form as sbfCustomers
5. Using the Property Sheet, set the following characteristics:
   Record Source: Customers
   Scroll Bars: Neither
   Record Selectors: No
   Navigation Buttons: No
6. On the Ribbon, click Add Existing Fields
7. Design the form as follows:
    
   
8. Save, preview, and close the subform
9. To create a new form, on the Ribbon, click Create and, in the Forms section, click Form Desig n
10. Save the form as sbfCars
11. In the Property Sheet, click Record Source and click its ellipsis button
12. In the Show Table dialog box, double-click Cars and Categories
13. Click Close
14. In the list of fields, double-click CarID, TagNumber, Make, Model, CarYear, and Category (from the Categories table)
15. Close the Query Builder
16. When asked whether you want to save, click Yes
17. Using the Property Sheet, set the following characteristics:
    Scroll Bars: Neither
    Record Selectors: No
    Navigation Buttons: No
18. On the Ribbon, click Add Existing Fields
19. Design the form as follows:
     
    
20. Save, preview, and close the subform
21. On the Ribbon, click Create and, in the Forms section, click Form Design
22. Save the form as RentalOrders and change the following characteristics in the Property Sheet:
    Record Source: RentalOrders
    Caption: Bethesda Car Rental - Rental Orders
23. On the Ribbon, click Add Existing Fields
24. From the Fields List, drag RentalOrderID and drop it in the Detail section of the form
25. In the Controls section of the Ribbon, make sure the Use Control Wizard button is highlighted. Click Combo Box and click the Detail section of the form
26. In the first page of the wizard, make sure the first radio button is selected and click Next
     
    
27. In the second page of the wizard, click Table: Employees
     
    
28. Click Next
29. In the Available Fields list of the third page, double-click EmployeeNumber, LastName, and Title
     
    
30. Click Next
31. In the fourth page of the wizard, click the arrow of the Ascending combo box and select EmployeeNumber
     
    
32. Click Next
33. In the fifth page of the wizard, review the list and click Next
     
    
34. In the sixth page of the wizard, click the arrow of the combo box and select EmployeeID
     
    
35. Click Next
36. Accept the default label and click Finish
37. Using the Property Sheet, change the caption of the label to Processed By:
38. Click the combo box
39. In the Property Sheet, change its Name to cbxEmployeeID
40. Click the Data tab, click Row Source, and click its ellipsis button
41. Change the third column name to Employee Name: [LastName] & ", " & [FirstName]
     
    
42. Close the Query Builder
43. When asked whether you want to save, click Yes
44. While the combo box is still selected, in the Property Sheet, change the following characteristics:
    Column Widths: 0";0.85";1.3";1.75"
    List Width: 3.9"
45. In the Controls section of the Ribbon, click the More button and make sure the Use Control Wizards option is highlighted .
    Click Combo Box and click the Detail section of the form
46. In the first page of the wizard, make sure the first radio button is selected and click Next
47. In the second page of the wizard, double-click Table: Customers and click Next
48. In the Available Fields list of the third page, double-click DrvLicNumber and FullName
49. Click Next
50. In the fourth page of the wizard, accept the default and click Next
51. In the fifth page of the wizard, review the list and click Next
52. In the sixth page of the wizard, click the arrow of the combo box and select CustomerID
53. Click Next
54. Accept the default label and click Finish
55. Using the Property Sheet, change the caption of the label to Processed For:
56. Click the combo box. In the Property Sheet, change the following characteristics:
    Name: cbxCustomerID
    Column Widths: 0";1.35";1.5"
    List Width: 2.85
57. In the Controls section of the Ribbon, click Subform/Subreport and click the form under the previously added combo box.
    If you receive a Microsoft Office Access Security Notice, read it and click Open
58. In the first page of the wizard, in the list, click sbfCustomers and click Next
59. In the second page of the wizard, make sure it indicated that the tables are connected through the CustomerID field and click Next
60. Accept the suggestions of the third page and click Finish
61. In the Controls section of the Ribbon, make sure the Use Control Wizard button is highlighted. Click Combo Box and click the Detail section of the form
62. In the first page of the wizard, make sure the first radio button is selected and click Next
63. In the second page of the wizard, double-click Table: Cars and click Next
64. In the Available Fields list of the third page, double-click TagNumber, Make, and Model
65. Click Next
66. In the fourth page of the wizard, accept the default and click Next
67. In the fifth page of the wizard, review the list and click Next
68. In the sixth page of the wizard, click the arrow of the combo box and select CarID
69. Click Next
70. Accept the default label and click Finish
71. Using the Property Sheet, change the caption of the label to Car Rented:
72. Click the combo box. In the Property Sheet, change the following characteristics:
    Name: cbxCarID
    Column Widths: 0";0.75";0.85";1.55"
    List Width: 3.15"
73. In the Controls section of the Ribbon, click Subform/Subreport and click the form under the previously added combo box
74. In the first page of the wizard, in the list, click sbfCars and click Next
75. In the second page of the wizard, make sure it indicated that the tables are connected through the CarID field and click Next
76. Accept the suggestions of the third page and click Finish
77. Using the Fields List, add the other controls
78. Add additional text boxes and set their characteristics as follows:
     

    Label's Caption Text Box Name Format Control Source Sub-Total: txtSubTotal Fixed =Nz([RateApplied])*Nz([TotalDays]) Tax Amount: txtTaxAmount Fixed =CLng(Nz([txtSubTotal])*Nz([TaxRate])*100)/100 Rent Total: txtRentTotal Fixed =Nz([txtSubTotal])+Nz([txtTaxAmount])

79. Save the form and switch it to Form View
     
    
80. Close the form
81. Re-open the RentalOrders form in Form View (otherwise, open the Bethesda Car Rental5 database and open its RentalOrders form)
82. Enter the following information for the first record (ignore any field whose value is not given):
     

    Record	1 of 1
    Processed By:	28-485
    Processed For:	402-22-9644
    Car Rented:	CAM-422
    Condition:	Excellent
    Tank Level:	Half
    Mileage Start:	6422
    Start Date:	03/14/2011
    Order Status:	Car On Road

83. Click the Next Record button and complete the new record with the following data:
     

    Record	2 of 2
    Processed By:	80-485
    Processed For:	A-378-478-439-384
    Car Rented:	AFW-928
    Condition:	Excellent
    Tank Level:	1/4 Empty
    Mileage Start:	2028
    Start Date:	03/22/2011
    Order Status:	Car On Road

84. Click the Next Record button and complete the new record with the following data:
     

    Record	3 of 3
    Processed By:	35-079
    Processed For:	M-028-662-206-814
    Car Rented:	GMM-186
    Condition:	Good
    Tank Level:	Full
    Mileage Start:	10407
    Start Date:	03/25/2011
    Order Status:	Car On Road

85. Close the Rental Orders form
86. Re-open the Rental Orders form
87. In the first record, add values to the following fields (ignore any field whose value is not given):
     

    Record	1 of 3
    Mileage End:	6514
    Total Mileage:	92
    End Date:	11/28/2009
    Total Days:	5
    Rate Applied:	24.95
    Order Status:	Order Completed

    
    
88. Go to the second record and add values to the following fields:
     

    Record	2 of 3
    Mileage End:	2152
    Total Mileage:	124
    End Date:	03/22/2011
    Total Days:	1
    Rate Applied:	28.95
    Order Status:	Order Completed

89. Click the New (Blank) Record button and complete the new record with the following data:
     

    Record	4 of 4
    Processed By:	80-485
    Processed For:	402-22-9644
    Start Date:	05/10/2011
    Order Status:	Order Reserved

90. Click the Next Record button and complete the record with the following data:
     

    Record	5 of 5
    Processed By:	80-468
    Processed For:	368-36-4838
    Car Rented:	RBL-618
    Condition:	Good
    Tank Level:	Empty
    Mileage Start:	8266
    Start Date:	03/16/2011
    Rate Applied:	35.95
    Order Status:	Car On Road
    Notes:	The customer has indicated that he will need the car for a month; that is, until mid-April 2011. The customer wanted to rent a Standard size car but there was none available. Therefore, although the customer is getting an SUV, we will apply the Monthly Standard rate.

91. Go to the third record and add values to the following fields:
     

    Record	3 of 5
    Mileage End:	10697
    Total Mileage:	290
    End Date:	03/28/2011
    Total Days:	3
    Rate Applied:	45.00
    Order Status:	Order Completed

92. Click the New (Blank) Record button and complete the new record with the following data:
     

    Record	6 of 6
    Processed By:	28-485
    Processed For:	S-738-384-838-784
    Car Rented:	DCC-713
    Condition:	Excellent
    Tank Level:	3/4 Full
    Mileage Start:	5002
    Start Date:	04/16/2011
    Order Status:	Car On Road

93. Go to the fifth record and add values to the following fields:
     

    Record	5 of 6
    Mileage End:	10114
    Total Mileage:	1848
    End Date:	04/15/2011
    Total Days:	30
    Rate Applied:	35.95
    Order Status:	Order Completed

    
    
94. Close the database

Lesson Summary

MOUS Topics

Exercises

Yugo National Bank

1. Open the Yugo National Bank2 database
2. Start a form in Design View and whose source is the Transactions table. Create it as a continuous form that includes the TransactionTypeID, the TransactionDate, the TransactionNumber, the DepositAmount, the WithdrawalAmount, and the ServiceCharge columns. Save the form as sbfTransactions. Configure to not allow data entry and not to show the navigation buttons
3. In the Form Footer  section, add a text box with the following characteristics:
   Name: txtDeposits
   Control Source: =Sum([DepositAmount])
   Format: $# ##0,00;($# ##0,00)
4. Still in the Form Footer  section, add another text box with the following characteristics:
   Name: txtWithdrawals
   Control Source: =Sum([WithdrawalAmount])
   Format: $# ##0,00;($# ##0,00)
5. Once again in the Form Footer  section, add another text box with the following characteristics:
   Name: txtServiceCharge
   Control Source: =Sum([ServiceCharge])
   Format: $# ##0,00;($# ##0,00)
6. Use the Visible property to hide the text boxes in the Form Footer section
7. Start a new form in Design View and whose source is the Customers table. Include the EmployeeID, the DateCreated, the AccountNumber, the AccountType, and the CustomerName fields. Save the form as CustomersTransactions. Configure to not allow data entry
8. Use the Subform/Subreport button and the Control Wizard button of the Ribbon to add the sbfTransactions sub-form to the form
9. In the Form Footer  section, add a text box with the following characteristics:
   Name: txtDeposits
   Control Source: =Sum([DepositAmount])
   Format: $# ##0,00;($# ##0,00)
10. Still in the Form Footer  section, add another text box with the following characteristics:
    Name: txtWithdrawals
    Control Source: =Sum([WithdrawalAmount])
    Format: $# ##0,00;($# ##0,00)
11. Once again in the Form Footer  section, add another text box with the following characteristics:
    Name: txtServiceCharge
    Control Source: =Sum([ServiceCharge])
    Format: $# ##0,00;($# ##0,00)
12. Design the form as you see fit. Here is an example:
     
    
13. Close the form