Home

Sub-Forms and Sub-Reports
   

Introduction

A subform (or a subreport) is a form that placed in the body of an existing form (or report). The subform (or subreport) is usually made to show some records. The records of the subform (or subreport) must come from a table, a SQL expression, or a query.
 

Practical LearningPractical Learning: Introducing Subforms
  1. Start Microsoft Access
  2. In the File Name text box, replace the name with College Park Auto Repair3
  3. Click Create
  4. On the default table, double-click ID
  5. Type RepairOrderID and press Enter
  6. Right-click the Table1 and click Design View
  7. Set the name of the table to RepairOrders
  8. Click OK
  9. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption Default Value
    RepairOrderID       Repair Order ID   
    CustomerName   50   Customer Name   
    Address   60      
    City   40      
    State   40      
    ZIPCode   20   ZIP Code  
    Make   50      
    Model   50      
    CarYear Number Integer   Year  
    ProblemDescription Memo      Problem Description  
    TaxRate Number Double Percent  Tax Rate 0.0575 
    Recommendations Memo        
  10. Save and close the table
  11. On the Ribbon, click Create
  12. To create a new table, in the Tables section, click Table
  13. Double-click ID
  14. Type PartUsedID and press Enter
  15. Right-click the Table1 and click Design View
  16. Set the name of the table to PartsUsed
  17. Click OK
  18. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption
    PartUsedID       Part Used ID
    RepairOrderID Number Long Integer   RepairOrderID
    PartName   50   Part Name
    UnitPrice Number Double Currency Unit Price
    Quantity Number Byte    
  19. Save and close the table
  20. On the Ribbon, click Create
  21. In the Tables section, click Table
  22. Double-click ID
  23. Type JobPerformedID and press Enter
  24. Right-click the Table1 and click Design View
  25. Set the name of the table to JobsPerformed
  26. Click OK
  27. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption
    JobPerformedID       Job ID
    RepairOrderID Number Long Integer   RepairOrderID
    JobPerformed   100   Job Performed
    JobPrice Number Double Currency Job Price
  28. Save and close the table

Creating a Relationship

To have a meaningful subform (or subreport), there must be a relationship between the record source (table or query) of the subform (or subreport) and the record source (table or query) of the subform (or subreport).

Practical LearningPractical Learning: Creating a Relationship
  1. On the Ribbon, click Database Tools
  2. In the Relationships section, click Relationships
  3. On the dialog box, double-click Parts Used
  4. Double-click RepairOrders
  5. Double-click JobsPerformed
  6. Click Close
  7. In the RepairOrders table, drag RepairOrderID and drop it on top of RepairOrderID on the PartsUsed table
  8. In the Edit Relationship dialog box, click Enfore Referential Integrity
  9. Click the other two check boxes
     
    Edit Relationships
  10. Click Join Type...
  11. Click the 2: radio button
  12. Click Create
  13. Create the same type of relationship from the RepairOrderID of the RepairOrders table to the RepairOrderID of the JobsPerformed table

    Relationships
  14. Close the Relationships window
  15. When asked whether you want to save, click Yes

Creating a Subform

A subform is primarily designed like a normal form. In most cases, you should set its Default View to Continuous Forms

Practical LearningPractical Learning: Creating a Subform
  1. On the Ribbon, click Create
  2. In the Forms section, click Form Wizard
  3. In the first page of the wizard, select PartsUsed in the combo box
  4. Click the Select All button Select All
     
    Form Wizard
  5. Click Next
  6. In the second page of the wizard, click Tabular
     
    Form Wizard
  7. Click Next
  8. In the third page of the wizard, change the title to sbfJobsPerformed
  9. Click Finish
  10. Right-click the title bar of the form and click Design View
  11. Double-click the button at the intersection of the rulers to access the click Property Sheet
  12. In the Property Sheet, click Format
  13. Change the following properties:
    Navigation Button: No
    Dividing Lines: Yes
  14. Click the Form Footer bar
  15. In the Property Sheet, double-click Visible to set it to No
  16. Design the subform as follows:

    Jobs Performed
  17. Save and close the form
  18. On the Ribbon, click Create
  19. In the Forms section, click Form Design
  20. Right-click the form and click Form Header/Footer
  21. To save the form, right-click its tab and click Save
  22. Set the name to sbfPartsUsed and click OK
  23. Double-click the button at the intersection of the rulers to access the click Property Sheet
  24. In the Property Sheet, click All
  25. Change the following properties:
    Record Source: Parts Used
    Default View: Continuous Forms
    Navigation Button: No
    Dividing Lines: Yes
  26. Click the Form Footer bar
  27. In the Property Sheet, double-click Visible to set it to No
  28. In the Tools section of the Ribbon, click Add Existing Fields
  29. From the Fields list, drag RepairOrderID and drop in the Detailt section of the form
  30. Click its accompanying lable and press Delete
  31. Design the subform as follows:

    Parts Used
  32. Save and close the form

Creating the Hosting Form

After creating a sub-form, you must create the form that will host it. The form is a normal form whose Record Source has a field that is related to the subform. To create the sub-form, you can use a wizard. 

Practical LearningPractical Learning: Creating a Form
  1. On the Ribbon, click Create
  2. In the Forms section, click Form Design
  3. Right-click the form and click Form Header/Footer
  4. Click the button at the intersection of the rulers
  5. In the Property Sheet, click Data
  6. Set the Record Source to RepairOrders
  7. Save the form as RepairOrders
  8. Design the form as follows:
     
    Repaird Orders
  9. From the Naviagation Pane, drag sbfPartsUsed and drop it in the Parts Used section of the form
  10. From the Naviagation Pane, drag sbfJobsPerformed and drop it in the Jobs Performed section of the form
  11. Complete the design of the form as follows:
     
    Repaird Orders
  12. Save the form
  13. To preview it, right-click its tab and click Form View
  14. Create a record as follows:
     
    Repaird Orders
  15. Close the form

Creating a Sub-Report

A subreport also starts like a normal report. If you plan to perform calculations on the subreport, you should (must) add the

Practical LearningPractical Learning: Creating a Subform
  1. On the Ribbon, click Create
  2. In the Reports section, click Report Design
  3. Right-click the report and click Report Header/Footer
  4. To save the report, right-click its tab and click Save
  5. Set the name to sbrPartsUsed and click OK
  6. Design the report as follows:
     
    Parts Used
  7. Save and close the report
  8. On the Ribbon, click Create
  9. In the Reports section, click Report Design
  10. Right-click the report and click Report Header/Footer
  11. To save the report, right-click its tab and click Save
  12. Set the name to sbrJobsPerformed and click OK
  13. Design the report as follows:
     
    Jobs Performed
  14. Save and close the report
  15. On the Ribbon, click Create
  16. In the Reports section, click Report Design
  17. Right-click the report and click Report Header/Footer
  18. To save the report, right-click its tab and click Save
  19. Set the name to RepairdOrders and click OK
  20. Design the report as follows:
     
    Repaird Orders

Repaird Orders
  21. Save the report
  22. Right-click the tab and click Print Preview
     
    Repaird Orders
  23. Close the report
 
 
     
 
Home Copyright © 2010-2016, FunctionX Home