Using Queries

Sub-Forms and Sub-Reports

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.

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