Home

Relationship-Based Operations
 

Domain Aggregate Functions
 

Introduction

In previous lessons, we have seen what valuable role a relationship between two tables can play, allowing data from one to be directly available to another. In some cases, you will not want data from one table to be accessed just anyhow from another table. To manage the flow, you can set a condition that must be met. This also allows you to restrict a specific record instead of considering all records, simply because they happen to have a matching value in the foreign key of a child table. To set the condition that would be used to retrieve a specific record, you can use one of the functions referred to as domain aggregates.
 

Practical Learning Practical Learning: Introducing Domain Aggregate Functions
  1. Start Microsoft Access and create a blank database named Sure Stuff Hardware Store
  2. On the main menu, click Insert -> Table
  3. In the New Table dialog box, double-click Design View
  4. Create the following fields:
     
    Field Name Data Type Caption Field Size Other Properties
    ItemID AutoNumber Item ID   Primary Key
    ItemNumber   Item # 10  
    ItemName   Item Name 100  
    UnitPrice Number Unit Price Double Format: Fixed
  5. Save the form as StoreItems and switch it to Datasheet View
  6. Create the following store items (let Microsoft Access generate the Item ID numbers):
     
    Item ID Item # Item Name Unit Price
    1 100001 Outdoor Extension Cord 25' 125V 14.95
    2 100002 Cordless Phone With Answering Machine 85.95
    3 100003 Kitchen Faucet 64.95
    4 100004 Masonry Nail 4Pack 6.15
    5 100005 Concrete Screw Nail 5.95
    6 100006 Caulk Gun - 6 Pack 42.05
    7 100007 Two Switch Wall Plate - 10 Pack 23.25
    8 100008 Shower Extension Arm 13" 39.95
    9 100009 Oscillator Sprinkler 6Pack 38.95
    10 100010 Adjustable Spray Water Nozzle - 12Pack 52.85
    11 100011 Ceiling Fan 175.95
    12 100012 Steam Vacuum Cleaner 225.55
    13 100013 Maximum Security Double Cylinder Deadbolt Lock - 4Pack 152.75
    14 100014 Door Knocker 35.75
    15 100015 Adjustable Wrench 22.95
    16 100016 Ceiling Fixture 9.45
    17 100017 Bacterial Drainpipe Cleaner - 12Pack 105.15
    18 100018 Septic Tank Activator - 6Pack 68.95
    19 100019 Outdoor Patio Glass and Chairs 745.85
    20 100020 Multi-Purpose Corn Broom 62.95
  7. Close the table and click it in the Tables section of the Database window to make sure it is selected
  8. On the Database toolbar, click the arrow of the New Object button and click AutoForm
  9. Save the form as StoreItems and adjust its design as follows:
     
  10. Right-click the form and click Form Header/Footer
  11. Use the Command Button Wizard to create a button that would be used to close the form. Set the button's caption to Close and its name to cmdClose
  12. Save the form and close it
  13. In the Tables section of the Database window, double-click Create Table In Design View
  14. Complete the table as follows:
     
    Field Name Data Type Caption Field Size Format Other Properties
    ReceiptID AutoNumber Receipt #      Primary Key
    OrderDate   Order Date   dd-mmm-yyyy Input Mask: 99\->L<LL\-0000;0;_
    Default Value: =Date()
    Item1Number     10     
    Item1Name   100   
    Item1UnitPrice Number   Double Fixed  
    Item1Quantity Number   Integer    
    Item1SubTotal Number   Double Fixed  
    Item2Number     10    
    Item2Name     100    
    Item2UnitPrice Number   Double Fixed  
    Item2Quantity Number   Integer    
    Item2SubTotal Number   Double Fixed  
    Item3Number     10    
    Item3Name     100    
    Item3UnitPrice Number   Double Fixed  
    Item3Quantity Number   Integer    
    Item3SubTotal Number   Double Fixed  
    Item4Number     10    
    Item4Name     100    
    Item4UnitPrice Number   Double Fixed  
    Item4Quantity Number   Integer    
    Item4SubTotal Number   Double Fixed  
    Item5Number     10    
    Item5Name     100    
    Item5UnitPrice Number   Double Fixed  
    Item5Quantity Number   Integer    
    Item5SubTotal Number   Double Fixed  
    Item6Number     10    
    Item6Name     100    
    Item6UnitPrice Number   Double Fixed  
    Item6Quantity Number   Integer    
    Item6SubTotal Number   Double Fixed  
    ItemsTotal Number Items Total Double Fixed  
    SalesTaxRate Number Tax Rate Double Percent Default Value: 0.0775
    SalesTaxAmount Number Tax Amount Double Fixed  
    OrderNetPrice Number Order Price Double Fixed  
  15. Save the table as CustomersOrders and close it
  16. Using the New Object: AutoForm button on the Database toolbar, generate a form and save it as CustomersOrders
  17. Change its design as follows:
     
  18. Save the form
  19. On the form, click the text box under Qty label
  20. In the Events tab of the Properties window, double-click On Lost Focus, and click its ellipses button
  21. Create a new procedure and implement the event as follows:
      
    Private Sub EvaluateOrder()
    Dim dblItemsTotal As Double
    Dim dblTaxAmount As Double
    Dim dblNetPrice As Double
    
    dblItemsTotal = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                       Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                       Nz([Item5SubTotal]) + Nz([Item6SubTotal]))
    dblTaxAmount = CLng(dblItemsTotal * Nz([SalesTaxRate]) * 100) / 100
    dblNetPrice = dblItemsTotal + dblTaxAmount
    
    Me.ItemsTotal = dblItemsTotal
    Me.SalesTaxAmount = dblTaxAmount
    Me.OrderNetPrice = dblNetPrice
End Sub

Private Sub Item1Quantity_LostFocus()
    [Item1SubTotal] = Eval([Item1UnitPrice] * [Item1Quantity])
    EvaluateOrder
End Sub
  22. Return to Microsoft Access and, on the form, click the second text box under Quantity
  23. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2Quantity_LostFocus()
    [Item2SubTotal] = Eval([Item2UnitPrice] * [Item2Quantity])
    EvaluateOrder
End Sub
  24. Return to Microsoft Access and, on the form, click the third text box under Quantity
  25. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3Quantity_LostFocus()
    [Item3SubTotal] = Eval([Item3UnitPrice] * [Item3Quantity])
    EvaluateOrder
End Sub
  26. Return to Microsoft Access and, on the form, click the fourth text box under Quantity
  27. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4Quantity_LostFocus()
    [Item4SubTotal] = Eval([Item4UnitPrice] * [Item4Quantity])
    EvaluateOrder
End Sub
  28. Return to Microsoft Access and, on the form, click the fifth text box under Quantity
  29. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5Quantity_LostFocus()
    [Item5SubTotal] = Eval([Item5UnitPrice] * [Item5Quantity])
    EvaluateOrder
End Sub
  30. Return to Microsoft Access and, on the form, click the sixth text box under Quantity
  31. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item6Quantity_LostFocus()
    [Item6SubTotal] = Eval([Item6UnitPrice] * [Item6Quantity])
    EvaluateOrder
End Sub
  32. Return to the form and save it

Using a Domain Aggregate Function

A domain aggregate function has the following syntax:

DFunctionName(Expression, Domain, Criteria)

The DFunctionName factor is the name of the function. The Expression argument can be the name of a column on which the function will act. It can also be a calculation-based expression. The Domain argument can be the name of a table or that of a query that doesn't require an external value. The Criteria argument is optional. If passed, it can specify the condition used to select a specific record.

Consider the following table:

Some of the domain aggregate functions resemble the SQL aggregate functions we reviewed in the previous lesson. There are additional others:

  • DCount: The DCount() function can be used to count the number of records of another table or query. When calling this function, the first argument should always be the name of a column. Here is an example:
      
    =DCount("Title","Videos")

    This code returns the total number of videos that have a value in the Title column. The DCount() function doesn't count the records where the Expression value is NULL. As mentioned in the syntax of the domain aggregate functions, you can use a criterion to select specific records. Here is an example:

    =DCount("Title","Videos","Rating = 'R'")

    In this case, the function will return the number of videos that are rated R from our table.
     

  • DSum: The DSum() function can calculate the total of (numeric) values of a column in a table (or query). Here is an example that will return the amount we spent buying the videos of the above table:
      
    =DSum("PriceBought","Videos")
  • DAvg: The DAvg() function is used to calculate the average of (numeric) values of a column of a table. Here is an example that produces the average price of the videos of the above table:
      
    =DAvg("PriceBought","Videos")
  • DMin and DMax: The DMin() (or the DMax()) function is used to calculate the minimum (or the maximum) of the values of a column in another table or query. If there is only one value in the group, both functions return the same value.
  • DFirst and DLast: The DFirst() (or the DLast()) function is used to get the first (or the last) value of a column in a table or query. If there is only one value in the group, both functions return the same value.
  • DStDev and DStDevP: The StDev() function is used to calculate the standard deviation of the numeric values of a the records of a table or query. The DStDevP() function is used to calculate the standard deviation of a population sample of the numeric values of a the records of a table or query. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group. Here is an example:
     
  • DVar and DVarP: The DVar() function calculates the statistical variance of the (numeric) values of a table or query. The DVarP() function calculates the variance of a population. If there is no value or the same value in the considered group, these functions return NULL. Here is an example:
     
  • DLookup: The DLookup() function can be used to find one or more records that respond to a criterion from another table or query.

 

Practical Learning Practical Learning: Using a Domain Aggregate Function
  1. On the form, click the first text box under Item Number
  2. In the properties window, double-click On Lost Focus
  3. Click its ellipsis button and implement the event as follows:
      
    Private Sub Item1Number_LostFocus()
On Error GoTo ErrorOccurred

    ' In the StoreItems table, look for an item that matches the item number
    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item1Number & "'")) Then
        ' If you find it, retrieve the corresponding item name
        [Item1Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item1Number & "'")
        [Item1UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item1Number & "'")
        ' Since you found the item, set the quantity to 1 as default
        [Item1Quantity] = "1"
        ' Set the default sub-total to the unit price
        [Item1SubTotal] = [Item1UnitPrice]
        ' and evaluate the order
        EvaluateOrder
    Else
        ' If you didn't find any item with this item number,
        ' reset the current item to nothing selected
        [Item1Number] = "000000"
        [Item1Name] = ""
        [Item1UnitPrice] = "0.00"
        [Item1Quantity] = "0"
        [Item1SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item1Number] = "000000"
        [Item1Name] = "Miscellaneous"
        [Item1UnitPrice] = "0.00"
        [Item1Quantity] = "0"
        [Item1SubTotal] = "0.00"
        Item1Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  4. Return to Microsoft Access and, on the form, click the second text box under Item Number
  5. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2Number_LostFocus()
On Error GoTo ErrorOccurred

    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item2Number & "'")) Then
        [Item2Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item2Number & "'")
        [Item2UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item2Number & "'")
        [Item2Quantity] = "1"
        [Item2SubTotal] = [Item2UnitPrice]
        EvaluateOrder
    Else
        [Item2Number] = "000000"
        [Item2Name] = ""
        [Item2UnitPrice] = "0.00"
        [Item2Quantity] = "0"
        [Item2SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item2Number] = "000000"
        [Item2Name] = "Miscellaneous"
        [Item2UnitPrice] = "0.00"
        [Item2Quantity] = "0"
        [Item2SubTotal] = "0.00"
        Item2Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occurred when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  6. Return to Microsoft Access and, on the form, click the third text box under Item Number
  7. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3Number_LostFocus()
On Error GoTo ErrorOccurred

    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item3Number & "'")) Then
        [Item3Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item3Number & "'")
        [Item3UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item3Number & "'")
        [Item3Quantity] = "1"
        [Item3SubTotal] = [Item3UnitPrice]
        EvaluateOrder
    Else
        [Item3Number] = "000000"
        [Item3Name] = ""
        [Item3UnitPrice] = "0.00"
        [Item3Quantity] = "0"
        [Item3SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item3Number] = "000000"
        [Item3Name] = "Miscellaneous"
        [Item3UnitPrice] = "0.00"
        [Item3Quantity] = "0"
        [Item3SubTotal] = "0.00"
        Item3Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  8. Return to Microsoft Access and, on the form, click the fourth text box under Item Number
  9. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4Number_LostFocus()
On Error GoTo ErrorOccurred

    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item4Number & "'")) Then
        [Item4Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item4Number & "'")
        [Item4UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item4Number & "'")
        [Item4Quantity] = "1"
        [Item4SubTotal] = [Item4UnitPrice]
        EvaluateOrder
    Else
        [Item4Number] = "000000"
        [Item4Name] = ""
        [Item4UnitPrice] = "0.00"
        [Item4Quantity] = "0"
        [Item4SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item4Number] = "000000"
        [Item4Name] = "Miscellaneous"
        [Item4UnitPrice] = "0.00"
        [Item4Quantity] = "0"
        [Item4SubTotal] = "0.00"
        Item4Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  10. Return to Microsoft Access and, on the form, click the fifth text box under Item Number
  11. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5Number_LostFocus()
On Error GoTo ErrorOccurred

    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item5Number & "'")) Then
        [Item5Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item5Number & "'")
        [Item5UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item5Number & "'")
        [Item5Quantity] = "1"
        [Item5SubTotal] = [Item5UnitPrice]
        EvaluateOrder
    Else
        [Item5Number] = "000000"
        [Item5Name] = ""
        [Item5UnitPrice] = "0.00"
        [Item5Quantity] = "0"
        [Item5SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item5Number] = "000000"
        [Item5Name] = "Miscellaneous"
        [Item5UnitPrice] = "0.00"
        [Item5Quantity] = "0"
        [Item5SubTotal] = "0.00"
        Item5Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  12. Return to Microsoft Access and, on the form, click the sixth text box under Item Number
  13. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item6Number_LostFocus()
On Error GoTo ErrorOccurred

    If Not IsNull(DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item6Number & "'")) Then
        [Item6Name] = DLookup("ItemName", "StoreItems", "ItemNumber = '" & Item6Number & "'")
        [Item6UnitPrice] = DLookup("UnitPrice", "StoreItems", "ItemNumber = '" & Item6Number & "'")
        [Item6Quantity] = "1"
        [Item6SubTotal] = [Item6UnitPrice]
        EvaluateOrder
    Else
        [Item6Number] = "000000"
        [Item6Name] = ""
        [Item6UnitPrice] = "0.00"
        [Item6Quantity] = "0"
        [Item6SubTotal] = "0.00"
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        [Item6Number] = "000000"
        [Item6Name] = "Miscellaneous"
        [Item6UnitPrice] = "0.00"
        [Item6Quantity] = "0"
        [Item6SubTotal] = "0.00"
        Item6Number.SetFocus
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  14. Return to the form and save it
  15. Switch the form to Form View and process a few orders as follows:
     
  16. Close the form

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. Open the Solas Property Rental database you created in Lesson 22
  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 PropertiesToRent 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 Caption Other Properties
    RentalAllocationID AutoNumber Rental Alloc ID  
    DateProcessed Date/Time Date Processed Format: dd-mmm-yyyy
    Input Mask: 00\->L<LL\-0000;0;_
    TenantID   Tenant  
    PropertyID   Property  
    RentalRate   Rental Rate  
    RentStartDate Date/Time Rent Start Date Format: Medium Date
    Input Mask:99\->L<LL\-00;0;_
    DueOn   Due On  
    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
  21. To complete the database, create a new table in Design View and add the following columns:
     
    Field Name Data Type Caption Other Properties
    PaymentID AutoNumber Receipt # Primary Key
    PaymentDate Date/Time Payment Date Format: dd-mmm-yyyy
    Input Mast: 99\->L<LL\-0000;0;_
    TenantID   Tenant(s)  
    PropertyID   Property  
    PaymentType   Payment Type  
    PaymentAmount Currency Amount Default Value: Delete 0
    Notes Memo    
  22. Save the table as Payments
  23. Set the Data Type of TenantID to Lookup Wizard
  24. When the wizard starts, in the first page, accept the first radio button and click Next
  25. In the second page of the wizard, select Tenants and click Next
  26. In the Available Fields list, double-click FileNumber, HOHName, HOHTitle, CHOHName, and CHOHTitle
  27. Click Next and click Finish
  28. In the lower section of the window, click Lookup and set the Column Heads property to Yes
  29. Set the Data Type of PropertyID to Lookup Wizard
  30. When the wizard starts, in the first page, 
  31. accept the first radio button and click Next
  32. In the second page of the wizard, select Properties and click Next
  33. In the Available Fields list, double-click PropertyCode, Address, PropertyNumber, Locality, and RentalRate 
  34. Click Next and click Finish 
  35. In the Lookup tab of the lower section of the window, set the Column Heads property to Yes
  36. Set the Data Type of PaymentType to Lookup Wizard
  37. In the first page of the wizard, click the second radio button and click Next
  38. Click under Col1, type Cash and press the down arrow key
  39. Type Check and press the down arrow key
  40. Type Credit Card and click Next
  41. Accept the suggested label and click Finish
  42. Save the table and close it
  43. Use the AutoForm to generate a form for the Payments table and save the form as Payments
  44. Change its design as follows:
     
  45. Save and 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 © Yevol, 2007 Next