Home

Using Queries

 

Sub-Forms and Sub-Reports

 

Introduction

When studying data relationships, we saw that tables could be configured to exchange information. Consequently, when using different forms in a database, the information can also flow among them. We may realize this only if we keep switching forms by opening some and closing others. Microsoft Access provides a convenient way to use and manage the flow of data among objects. It does this by making it possible to "embed" one form or report in another's body. A form (or report) that displays in the body of another form (or report) is called a sub-form (or sub-report).

As we will see in the next sections, a sub-form or a sub-report is primarily created like any other form or report, using the same approach and the same techniques. While or after creating a sub-form or a sub-report, you can save it and give it a name. On this site, to indicate that it is one, we will start the name of a sub-form with sbf and the name of a sub-report with sbr. Examples are sbfStudents or sbrTransactions. This allows us to know what it is intended for.

Practical Learning Practical Learning: Introducing Sub-Forms

  1. Start Microsoft Access and open the Solas Property Rental database you created in the previous lesson
  2. In the Database window, click Queries.
    To create a new query, double-click Create Query In Design View
  3. In the Show Table dialog box, double-click PropertyTypes and Properties
  4. Click Close
  5. In the Properties list, double-click PropertyCode
  6. In the PropertyTypes list, double-click PropertyType
  7. In the Properties list, double-click Address, PropertyNumber, City, Locality, RentalRate, PropertyID, OccupiedVacant
  8. Click the Criteria box of OccupiedVacant and type Vacant
  9. Clear its check box
  10. Save the query as PropertiesTo Rent and close it
  11. In the Database window, click Tables.
    To create a new table, double-click Create Table In Design View
  12. Create the following fields:
     
    Field Name Data Type Field Size Other Properties
    RentalAllocationID AutoNumber    
    TenantID      
    PropertyID      
    RentalRate      
    RentStartDate Date/Time   Format: Medium Date
    Input Mask:99\->L<LL\-00;0;_
    DueOn      
    Notes Memo    
  13. Right-click the first field and click Primary Key
  14. Save the table as RentalAllocations
  15. Set the TenantID's Data Type to Lookup Wizard
  16. In the first page of the wizard, accept the first radio button and click Next
  17. In the second page, click Tenants and click Next
  18. In the third page, double-click FileNumber, HOHName, HOHTitle, HOHMaritalStatus, CHOHName, CHOHTitle, CHOHMaritalStatus, TenantsRelationship, NumberOfAdults, and NumberOfChildren
  19. Click Next twice and click Finish
  20. Set its Caption to Tenant(s) and delete the 0 of the Default Value
  21. In the Lookup tab, set the Column Heads property to Yes
  22. Set the PropertyID's Data Type to Lookup Wizard
  23. In the first page of the wizard, accept the first radio button and click Next
  24. In the second page, click the Queries radio button
  25. Click PropertiesToRent and click Next
  26. In the third page, click the button used to select all fields and click Next
  27. Click Next twice
  28. When asked to select a field, click PropertyID
  29. Click Next and click Finish
  30. Set its Caption to Property and delete the 0 of the Default Value
  31. In the Lookup tab, set the Column Heads property to Yes
  32. Save and close the table
  33. In the Tables section of the Database window, click RentalAllocations.
    To create a new form, click the arrow of the New Object button on the Database toolbar and click AutoForm
  34. Save the form as RentalAllocations
 

Referring to a Sub-Form or a Sub-Report

In most cases, the creating an configuration of a sub-form or a sub-report is quite straightforward. In some cases, you will need to create an expression that refers to a sub-form or a sub-report, from its parent. For example, you may want to access a Windows control that is positioned on a sub-form or a sub-report. You cannot directly use the name of the control that is on the sub-form. If this were possible, what if you have a control on the form that holds the same name as an object on the sub-form or sub-report. How would the database engine know what object you are referring to.

To refer to a control on a sub-form or sub-report from the parent form or report, in Microsoft Access, enter the name of the sub-form or sub-report, followed by the period operator, followed by Form, followed by the exclamation point, and followed by the name of the object. Here is an example entered in the Control Source property of a text box of a form:

=[sbfRegistrantsByGender].[Form]![txtMembers]

This expression indicates that you are referring to an object named txtMembers that is positioned in a sub-form named sbfRegistrantsByGender.

To refer to a property of the sub-form or sub-report from its parent, enter the name of the sub-form or sub-report, followed by the period operator, followed by Form, followed by the period operator, and followed by the name of the property. You can then assign the appropriate value to the property. Consider the following example:

sbrTimeSheet.Form.Filter

This refers to the Filter property of a sub-report named sbrTimeSheet.

 

A Columnar Sub-Form

Normally a sub-form is created like a regular form. This means that you can use the Form Wizard or design it from scratch. What makes it a sub-form is its ability to be laid in the body of another form. This is possible only if the record sources of both the form and the sub-form are related. Starting with Microsoft Access 2000, if you use either the Form Wizard or the AutoForm to create a form, a sub-form based on its child table would also be created and added to the form.

From our experience so far, we know that a columnar form displays one record at a time. You can also create a sub-form like that and only one related record would display. This is convenient for data selection where one record from a child would be necessary to display.

 

Practical Learning Practical Learning: Creating Columnar Sub-Forms

  1. In the Database window, click Forms and click New
  2. In the New Form dialog box, click Design View if necessary.
    In the combo box, select Tenants and click OK
  3. Design the form as follows:
     
  4. Set the form's properties as follows:
    Record Selectors: No
    Navigation Buttons: No
  5. Save the form as sbfTenants and close it
  6. To create a new form, click New
  7. In the New Form dialog box, click Design View if necessary.
    In the combo box, select PropertiesToRent and click OK
  8. Save the form as sbfProperties and design it as follows:
     
  9. Set the form's properties as follows:
    Record Selectors: No
    Navigation Buttons: No
  10. Save and close the form
  11. If necessary, open the RentalAllocations form.
    In the Toolbox, make sure the Control Wizard button is down. Click Subform/Subreport and click an empty area in the left section of the form
  12. In the first page of the wizard, click sbfTenants and click Next
  13. Click Next and click Finish
  14. Once again, in the Toolbox, click Subform/Subreport and click an empty area in the right section of the form
  15. In the first page of the wizard, click sbfProperties and click Next
  16. Click Next and click Finish
  17. Complete the design of the form as follows:
     
  18. Save the form and switch it to Form View
     
  19. Create a few records
     
  20. Close the form
 

A Tabular Sub-Form

While a columnar form displays one record at a time, a tabular form can display more than one record. In fact, only its height controls the number of records that this type of form can display at a time. As far as the design is concerned, there are many similarities between both forms. The main difference is their Default View property. That of the column form is set to Single Form while the tabular form uses the Continuous Form option.

 

Practical Learning Practical Learning: Creating a Tabular Sub-Forms

  1. Open your Yugo National Bank database
  2. In the Database window, click the Forms button.
    To create a new form, in the Forms section of the Database window, click Design View.
    In the bottom combo box, select Customers and click OK
  3. Save the form as AccountTransactions
  4. Design the form as follows:
     
  5. On the toolbox, make sure the Control Wizard button is down.
    Click Combo Box and click on the left side of the Account Status label
  6. In the first page of the wizard, accept the first radio button and click Next
  7. In the second page of the wizard, click AccountTypes and click Next
  8. In the third page of the wizard, double-click AccountType and click Next twice
  9. In the combo box of the fifth page, select AccountTypeID
  10. Click Next and click Finish
  11. Change the name of the new combo box to AccountTypeID and change the caption of its accompanying label to Account Type:
  12. Set the form's properties as follows:
    Caption: Yugo National Bank - Account-Related Transactions
    Allow Edits: No
    Allow Deletion: No
    Allow Addition: No
    Auto Center: Yes
    Min Max Enabled: Min Enabled
  13. Save and close the form
  14. To create a new form, in the Forms section of the Database window, double-click Create Form Using Wizard
  15. In Tables/Queries combo box, select Transactions
  16. In the Available Fields, double-click TransactionTypeID, TransactionDate, DepositAmount, WithdrawalAmount, and ServiceCharge
  17. Click Next
  18. In the second page of the wizard, click Tabular and click Next
  19. In the third page, click Standard and click Next
  20. Change the name of the form to sbfTransactions and click Finish
  21. Switch the sub-form to Design View and complete its design as follows:
     
  22. Save the sub-form
  23. On the Toolbox, click the Text Box and click the left section under the Form Footer bar
  24. Delete its accompanying label and, in the Properties window, change its properties as follows:
    Name: txtDeposits 
    Control Source: = Sum(DepositAmount)
    Format: Fixed
    Visible: No
    Top: 0
    Height: 0.0215"
  25. On the Toolbox, click the Text Box and click the middle section under the Form Footer bar
  26. Delete its accompanying label and, in the Properties window, change its properties as follows:
    Name: txtWithdrawals
    Control Source: = Sum(WithdrawalAmount)
    Format: Fixed
    Visible: No
    Top: 0
    Height: 0.0215"
  27. On the Toolbox, click the Text Box and click the middle section under the Form Footer bar
  28. Delete its accompanying label and, in the Properties window, change its properties as follows:
    Name: txtServiceCharges
    Control Source: = Sum(ServiceCharge)
    Format: Fixed
    Visible: No
    Top: 0
    Height: 0.0215"
  29. Set the properties of the sub-form as follows:
    Allow Edits: No
    Allow Deletion: No
    Allow Addition: No
    Record Selectors: No
    Navigation Buttons: No
     
  30. Reduce the height of the Form Footer bar as much as possible:

  31. Save and close the sub-form
  32. In the Forms section of the Database window, right-click AccountTransactions and click Design View
  33. In the Toolbox, make sure the Control Wizard button is down.
    Click Subform/Subreport and click the empty area under Account Name
  34. In the first page of the wizard, click sbfTransactions and click Next
  35. In the second page of the wizard, accept the established relationship.
    Click Next and click Finish
  36. Change the accompanying label's caption to Account's Transactions
  37. In the Toolbox, click the Text Box and click below the subform on the left
  38. Change the caption of its accompanying label to Total Deposits:
  39. Change the properties of the new text as follows:
    Name: txtTotalDeposits
    Control Source: =[sbfTransactions].Form!txtDeposits
    Format: Fixed
  40. In the Toolbox, click the Text Box and click below the previously added text box
  41. Change the caption of its accompanying label to Total Widthdrawals:
  42. Change the properties of the new text as follows:
    Name: txtTotalWidthdrawals
    Control Source: =[sbfTransactions].Form!txtWithdrawals
    Format: Fixed
  43. In the Toolbox, click the Text Box and click to the right of the txtTotalDeposits text box
  44. Change the caption of its accompanying label to Total Charges:
  45. Change the properties of the new text as follows:
    Name: txtTotalCharges
    Control Source: =[sbfTransactions].Form!txtServiceCharges
    Format: Fixed
  46. In the Toolbox, click the Text Box and click below the txtTotalCharges text box
  47. Change the caption of its accompanying label to Balance:
  48. Change the properties of the new text as follows:
    Name: txtBalance
    Control Source: =[txtTotalDeposits]-[txtTotalWithdrawals]-[txtTotalCharges]
    Format: Fixed
  49. Save the form and switch it to Form View
     
  50. Save the form
 

Data Filtering With Sub-Forms and Sub-Reports

After creating and configuring a sub-form or a sub-report, it is meant to display data that is related to the  value of the primary key of the parent form or report. If you perform data analysis on the parent object, the child may not show the appropriate record(s). If you want to filter data of the sub-form or sub-report, you must explicitly indicate it.

To filter data on the sub-form or sub-report from its parent, create a filter and pass it to the Filter property of the sub-form or sub-report using the expressions we defined earlier as to how to refer to a sub-form or a sub-report's property.

 

Practical Learning Practical Learning: Filtering Data of a Sub-Form

  1. Switch the AccountTransactions form to Design View and expand the area below the bottom text boxes
  2. On the Toolbox, click Text Box and click the area under Total Withdrawals
  3. Change the caption of its accompanying label to Show Transactions Between:
  4. Change its properties as follows:
    Name: txtTransStartDate
    Input Mask: 99\->L<LL\-00;0;_
  5. On the Toolbox, click Text Box and click the area on the right side of the previously added text box
  6. Change the caption of its accompanying label to and:
  7. Change its properties as follows:
    Name: txtTransEndDate
    Input Mask: 99\->L<LL\-00;0;_
  8. On the Toolbox, click the Command Button and click on the right side of the previously added button. If/when the Command Button Wizard starts, click Cancel
  9. Change its Name to cmdTransSubmit and its Caption to Submit
  10. On the Toolbox, click the Command Button and click on the right side of the previously added button. If/when the Command Button Wizard starts, click Cancel
  11. Change its Name to cmdTransAll and its Caption to Show All
     
  12. Right-click the Submit button and click Build Event...
  13. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
     
    Private Sub cmdTransSubmit_Click()
        Dim dteStartDate As Date
        Dim dteEndDate As Date
        
        dteStartDate = CDate(txtTransStartDate)
        dteEndDate = CDate(txtTransEndDate)
    
        Me!sbfTransactions.Form.Filter = "[TransactionDate] BETWEEN #" & _
    			dteStartDate & "# AND #" & dteEndDate & "#"
        Me!sbfTransactions.Form.FilterOn = True
    End Sub
  14. Return to the form
  15. Right-click the Show All button and click Build Event...
  16. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
     
    Private Sub cmdShowAll_Click()
        Me!sbfTransactions.Form.FilterOn = False
    End Sub
  17. Close Microsoft Visual Basic
  18. Switch the form to Form View and trying filtering some transactions
  19. Save and close the form
 

Previous Copyright © 2005-2009 FunctionX, Inc. Next