Home

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 Automatic Subforms

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 Select All
  7. In the Tables/Queries combo box, select Table: Videos
  8. Double-click the following fields to select them: Title, CopyrightYear, Length, and Rating
     
    Form Wizard
  9. Click Next
  10. In the second page, click by Directors (or make sure it is selected)
     
    Form Wizard
     
    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
     
    Form Wizard
  14. Click Finish
  15. After viewing the form, close it

Sub-Forms and Sub-Report Design

 

Introduction

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.

The Datasheet Subform

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 23/8
  7. If the Field List is not displaying, on the Ribbon, click Design and, in the Tools section, click Add Existing Fields Field List.
    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 Right
     
    Subform
  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
     
    Subform
  26. Save and close the subform
  27. In the Navigation Pane, under the Forms bar, double-click MusicAlbums to open it
     
    Music Collection
  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 Use Control Wizard (or click it).
    To add the new subform, in the Controls section of the Ribbon, click the Subform/Subreport button Subform/Subreport
  30. Click the body of the Tracks tab on the form
     
    Music Collection
  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
     
    Music Collection
  35. Save and preview the form
     
    Music Collection
  36. Close the form

The Continuous 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:

Continuous Form

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:

Continuous Form

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
     
    Query
  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 Label
  18. Click just under the Form Header bar on the left side:
     
    Sub-Form
  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:
     
    Sub-Form
  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
     
    Sub-Form
  26. In the Controls section of the Ribbon, click the Text Box 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
     
    Sub-Form
  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:
     
    Customers Records
    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:
     
    Customers
      
    Customers
  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:
     
    Customers Subform
  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:
     
    Cars Subform
  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
     
    Combo Box Wizard
  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]
     
    Query Builder
  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 Use Control Wizard.
    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 Sub-Form 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 Sub-Form 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:
     
    Rental Orders
    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
     
    Rental Orders
  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 Next Record 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 Next Record 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

    Bethesda Car Rental
  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 New Blank Record 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 Next Record 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 New Blank Record 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

    Bethesda Car Rental
  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:
     
    Yugo National Bank - Customers
  13. Close the form
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Home