Accessing Subforms

<< Click to Display Table of Contents >>

Navigation:  Sub Forms >

Accessing Subforms

Rick Dobson          

The Access subform wizards do much of the work in connecting a main form to its subform. In this article, Rick Dobson Dobson goes beyond the wizards to show you how to perform two new tasks with a subform. He also takes you behind the scenes to increase your understanding of how to access the subform inside a subform control.

A main/subform design is a standard way of presenting data in Access applications. In this type of form design, one form (a subform) synchronizes the data that it displays based on the data displayed on the main form. For example, a subform shows the line items for the sales order number displayed on the main form. The subform refreshes the line items that it shows when the order number on the main form changes. It's not uncommon for one main form to have multiple subforms. Each of the subforms shares one or more field values with the main form. It's those shared values that allow Access to synchronize the rest of the subform values with the data showing on the main form.

You can think of the main form as a parent to its subforms. In managing the relationship between main and subforms with code, it's useful to recall that a Subform control on the main form represents the subform that the control contains. The Subform control has a Form property, which lets the code from your main form access the controls and recordset values on the subform. By tapping the Subform control and its Form property, you can perform many tasks, including enumerating the subforms on a form or summarizing the data in subforms into a control on the main form.

The sample forms

In order to discuss this topic, I'll need some example forms. The accompanying Download file for this article imports the Categories, Order Details, Orders, Products, and Suppliers tables from the Northwind.mdb file. Built-in parent-child relationships exist between selected pairs of these tables, including Orders and Order Details, Products and Suppliers, as well as Categories and Products. When you use the Access 2002 or Access 2003 AutoForm Wizard with the Orders, Products, or Categories tables, you automatically create a main/subform. Earlier versions of Access require you to create separate main and subforms and then drag the child form from the Database window to the design view of the parent form in order to create a main/subform.

I created three main/subforms for the sample application with the AutoForm tool:

• frmOrders–The main form is based on the Orders table; the subform is based on the Order Details table.

• frmProducts–The main form is based on the Products table; the subform is based on the Order Details tables.

• frmCategories–The main form is based on the Categories table; the subform is based on the Products table.

In addition, I created a standalone form (using the AutoForm Wizard) based on the Suppliers tables and called it frmSubSuppliers. I dragged this form from the Database window onto the frmProducts form in design view. As a result, the frmProducts form includes two subforms–one automatically inserted by AutoForm and another manually added in design view.

It frequently happens on main forms that you want to show a summary, such as a count or a sum, of the data on a subform. To demonstrate, I added an unbound TextBox to the frmOrders form to display the total extended price for the detail lines on the subform. Code behind the form totals the product of three column values on the subform for all the rows on the subform.

In addition, I added two unbound TextBox controls to the frmProducts form to show the number of rows in each of its subforms. Populating these TextBoxes requires counting the rows in two separate subforms. You can easily program the contents of these unbound controls if you have a solid grasp of the Form property for the Subform control (and related issues).

Enumerating the forms in a database

Main/subforms are forms with one or more Subform controls. Therefore, enumerating the forms in a project lists both the standard forms (forms without one or more Subform controls) and the main/subforms in a project. Before highlighting some of the special features of a main/subform, I'll start with a program that enumerates all the forms in a project. You'll need to build on this technique for listing ordinary forms when you focus exclusively on main/subforms and their Subform controls.

The FormNameAndSource procedure shown here lists each of the forms in a project along with its record source. However, when a form includes a main form and a subform, the record source will be only for the main form. Since all the forms in the sample project are main/subforms (except frmSubSuppliers), all these forms show just the record source for the main form:

Sub FormNameAndSource()

Dim abj1 As Access.AccessObject

For Each abj1 In CurrentProject.AllForms

    str1 = str1 & vbCrLf & "Form name: " & _

        abj1.Name & vbCrLf

    If abj1.IsLoaded = True Then

        str1 = str1 & vbTab & _

            "record source: " & _

            Forms(abj1.Name).RecordSource & _

            vbCrLf

    Else

        DoCmd.OpenForm abj1.Name

        str1 = str1 & vbTab & _

            "record source: " & _

            Forms(abj1.Name).RecordSource & _

            vbCrLf

        DoCmd.Close acForm, abj1.Name

    End If

Next abj1

Debug.Print str1

End Sub

The FormNameAndSource procedure uses an AccessObject, abj1, to iterate through the members of the AllForms collection in the CurrentProject. An AccessObject provides the minimum of information about whatever object it refers to but will work with any object in an Access application (for example, forms, reports, tables, and queries). An AccessObject object in the AllForms collection is referring to something very different from a Form object in the Forms collection in a project. Objects in the Forms collection correspond exclusively to open forms. AccessObject objects in the AllForms collection correspond to forms whether or not they're open. The AccessObject object and its corresponding collections were introduced into Access starting with Access 2000.

A member of the AllForms collection has an IsLoaded property that returns a Boolean value of True when a form is open and a value of False otherwise. The FormNameAndSource gathers all the form names with their RecordSources to a string variable. However, a form's RecordSource property isn't available unless a form is open. My FormNameAndSource procedure takes advantage of the IsLoaded property to open an Access form if it's not already open. When a form is open, the procedure simply adds the form's RecordSource property to the string variable (str1) that summarizes all the forms in a project. If a form isn't open, then the procedure opens the form before attempting to append the form's RecordSource property value to str1.

After finishing with a previously closed form, the procedure returns the form to its former closed state. After iterating through all the members of the AllForms collection, the procedure displays the form names along with the record sources in str1 by invoking the Print method of the Debug object. By interspersing vbTab and vbCrLf constants in str1, the output is formatted with blank lines between forms (making form names appear on separate lines from their record sources), and indenting record sources from their forms.

Enumerating the subforms in a project

There's no built-in collection for enumerating subforms in a project (or even within a form). However, each Access form has a Controls collection. Only the Subform control has the properties for managing a subform, such as the SourceObject property. The SourceObject property points at the RecordSource for a subform. Therefore, you can present data about the subforms in a project by searching each of the project's forms for Subform controls and then displaying selected properties of those Subform controls, such as their Name and SourceObject properties.

My CallSubFormNameControlSource and SubFormNameControlSource procedures (see the following code block) show how to enumerate all the subforms in the current project. CallSubFormNameControlSource iterates through the forms in a project and either immediately calls SubFormNameControlSource or opens the form before calling the sub procedure. If the main procedure opens a form before calling its subroutine, the main procedure closes the form after control returns to it:

Sub CallSubFormNameControlSource()

Dim abj1 As Access.AccessObject

For Each abj1 In CurrentProject.AllForms

    If abj1.IsLoaded = True Then

           SubFormNameControlSource (abj1.Name)

    Else

        DoCmd.OpenForm abj1.Name

        SubFormNameControlSource (abj1.Name)

        DoCmd.Close acForm, abj1.Name

    End If

Next abj1

End Sub

Sub SubFormNameControlSource _

    (frmName As String)

Dim frm1 As Access.Form

Dim str1 As String

Set frm1 = Forms(frmName)

Debug.Print "Main form's name: " & frm1.Name

Dim int1 As Integer

For int1 = 0 To frm1.Controls.Count - 1

    If TypeOf frm1.Controls(int1) Is SubForm Then

        str1 = str1 & "Subform name: " & _

          frm1.Controls.Item(int1).Form.Name & _

          vbCrLf

        str1 = str1 & vbTab & _

            "Subform control name: " & _

            frm1.Controls.Item(int1).Name & _

            vbCrLf

        str1 = str1 & vbTab & _

            "Subform SourceObject name: " & _

            frm1.Controls.Item(int1). _

            SourceObject & _

            vbCrLf

    End If

Next int1

If str1 <> "" Then

    Debug.Print str1

    str1 = ""

Else

    Debug.Print "No sub forms on main form."

    Debug.Print

End If

End Sub

The SubFormNameControlSource procedure starts by setting a reference to the form named in its argument (frmName) and printing its argument's value to the Immediate window. Then, the procedure loops through the controls on the form designated in its argument, using a For...Next structure. An If...Then statement within the For...Next structure filters for controls with a Subform type. The test in the If...Then statement uses a TypeOf function to filter for Subform control types.

After finding a Subform control, the procedure assigns three items to a string (str1) that summarizes subform specifications on the main form:

• The Subform control's Name property.

• The Subform control's SourceObject property.

• The subform's name. The subform within the Subform control is accessed through the Subform control's Form property, giving the line:

      frm1.Controls.Item(int1).Form.Name

The other two properties are properties of the Subform control (rather than the form that it references). Therefore, these items don't include the Form property when designating them.

SubFormNameControlSource writes to the Immediate window after looping through all the controls on a form (provided that str1 isn't a zero-length string). The str1 string reports on all the Subform controls and their corresponding subforms on the form. Since the For...Next loop iterates through all the controls on the form, it will display information for all the Subform controls on a form. If str1 is a zero-length string, the procedure writes to the Immediate window that there are no subforms on the main form.

Figure 1 shows the Immediate window output from SubFormNameControlSource. As you can see, all of the four forms from the sample application are listed. In addition, the output precisely describes the subform status of each form. For example, the output indicates that the frmSubSuppliers form has no subforms, but the frmProducts form has two subforms. The frmCategories and frmOrders forms each have a single subform. The output gives the name of the Subform control for each subform.

Going one step further, through the Form property of the Subform control you can access the values on a subform that synchronize with the current record on a main form.

200402_rd1
Figure 1

 

Enumerating the values on a subform

Being able to enumerate the values on a subform can be very useful. For example, it lets you accumulate or develop computed field values on the main form based on subform values. In fact, you can even develop a computed value for each row on a subform and then sum the computed values across rows. The rows displayed in the subform will automatically change whenever the data on the main form changes. Therefore, you must specify main form row values when enumerating subform values. The CallEnumerateSubformControlValues and EnumerateSubformControlValues procedures demonstrate how to enumerate subform values:

Sub CallEnumerateSubformControlValues()

Dim ctl1 As Access.Control

Dim strFormName As String

Dim strSubformControlName As String

strFormName = "frmOrders"

DoCmd.OpenForm strFormName

strSubformControlName = "Child28"

Set ctl1 = _

    Forms(strFormName).Controls. _

    Item(strSubformControlName)

EnumerateSubformControlValues _

    Forms(strFormName), ctl1, 2, 3

'EnumerateSubformControlValues _

'    Forms(strFormName), ctl1

DoCmd.Close acForm, strFormName

End Sub
 

Sub EnumerateSubformControlValues _

    (frm1 As Form, _

    ctl1 As Control, _

    Optional intFirstRow As Integer = 1, _

    Optional intLastRow As Integer = 2)

Dim ctl2 As Access.Control

Dim int1 As Integer

Dim int2 As Integer

DoCmd.GoToRecord acDataForm, frm1.Name, _

    acGoTo, intFirstRow

For int1 = intFirstRow To intLastRow

    Debug.Print String(2, vbCrLf) & _

        "Beginning of record: " & _

        int1; " on main form"

    For int2 = 1 To _

        ctl1.Form.Recordset.RecordCount

        Debug.Print vbCrLf & _

            "Beginning of record: " & _

            int2 & " on subform"

        For Each ctl2 In ctl1.Form.Controls

            Debug.Print ctl2.Name

            Debug.Print ctl2.Value

        Next ctl2

    Next int2

DoCmd.GoToRecord , , acNext

Next int1

End Sub

CallEnumerateSubformControlValues manages the main form and optionally designates a range of rows on the main form for which to enumerate values on a subform. The procedure starts by referencing the main form and the Subform control pointing to a subform. For the sample application, I used frmOrders as the main form and Child28 as the Subform control name. You can use the CallSubFormNameControlSource and SubFormNameControlSource procedures to generate appropriate code for your own applications.

One main reason for designating a main form is to open it. CallEnumerateSubformControlValues closes the main form just before ending. When modifying this code for your own project, you'll need to alter the main form name and the Subform control name.

EnumerateSubformControlValues lists the values on the subform for each of a range of rows on the main form. EnumerateSubformControlValues can be called in two ways:

• You can specify just the names for the main form and the Subform control.

• You can also specify a starting row and an ending row on the main form for enumerating subform recordset values.

CallEnumerateSubformControlValues shows the syntax for both ways of calling EnumerateSubformControlValues. If you don't specify starting and ending main form row values, EnumerateSubformControlValues uses default values of 1 and 2 to designate the first and second rows as the starting and ending rows.

EnumerateSubformControlValues begins by moving to the starting row (intFirstRow) on the main form for enumerating subform values. This starting row will either be a value explicitly passed to the procedure from its calling procedure or the default value of 1. The heart of the procedure is a set of nested For...Next loops that perform the enumeration eventually displayed in the Immediate window. The outer loop designates the starting and ending rows on the main form for the enumeration.

The next loop iterates over the rows in the subform. This loop depends on the RecordCount property for the subform recordset matching the current row on the main form. The innermost loop passes through all the controls on the subform. As the code passes through each control, it prints both the control's name and value for the current row in the subform's recordset. Both the second and third loops specify the collection of values or objects over which they iterate by using the Form property of the Subform control (ctl1). Before returning to the top of the outer loop, the procedure advances the current record for the main form by one using the DoCmd object's GoToRecord method.

Figure 2 shows an excerpt from the output of EnumerateSubformControlValues with a starting row value of 2. Notice that lookup values, such as the product name for ProductID in the Order Details table, don't appear. If you require the lookup values (which are product names in this case), you need to code the translation yourself. On the other hand, the numeric UnitPrice, Quantity, and Discount column values appear in a format suitable for computing extended price.

200402_rd2
Figure 2

 
Summing subform values

One important reason for understanding how to enumerate subform values is to be able to summarize subform data on a main form. Figure 3 shows the form frmOrders, which contains a TextBox (it's the one just above the subform, with the label Total Extended Price). The TextBox contains the sum of the extended price for each row in the subform. The extended price for a row is the product of the values in the Quantity and Unit Price columns multiplied by 1 minus the value in the Discount column. The form in Figure 3 doesn't show values for main controls that don't permit updates (for instance, Customer, Employee, and Ship Via), or for controls for which there's no value (for example, Ship Region).

 

200402_rd3
Figure 3

The Form_Current event procedure for frmOrders computes the total extended price for the line items in an order and formats the computed value as currency in the TextBox for total extended price. By using the Form's Current event to hold this code, the total extended price value is always fresh whenever a user moves to a new record on the main form or when the form initially opens:

Private Sub Form_Current()

Dim ctl1 As Access.Control

Dim int1 As Integer

Dim dbl1 As Double

Set ctl1 = Me.Child28

For int1 = 1 To _

    ctl1.Form.Recordset.RecordCount

    dbl1 = dbl1 + _

        (ctl1.Form.Controls("Quantity").Value * _

        ctl1.Form.Controls("UnitPrice").Value * _

        (1 - ctl1.Form.Controls("Discount"). _

        Value))

    ctl1.Form.Recordset.MoveNext

Next int1

Me.txtTotalExtendedPrice = _

    FormatCurrency(dbl1)

End Sub

The Form_Current procedure commences by setting a reference to the Subform control (Child28). Then the procedure loops through the rows of the recordset in the subform that match the row showing on the main form. Within the loop, the procedure computes the total extended price as the sum of the extended price for each row. The procedure uses the Form property for the Subform control (ctl1) to specify the End value in the For...Next loop and the Quantity, UnitPrice, and Discount control values on the subform. An assignment statement at the close of the procedure formats the aggregated extended price values as currency in the txtTotalExtendedPrice TextBox.

Counting subform rows

The frmProducts form illustrates a main/subform design with two subforms. The Products table is the record source for the main form. The Order Details and Suppliers tables are record sources for two subforms. Figure 4 shows an excerpt from the form with all of the main form and Order Details column values for the first product, Chai, along with a clipped version of the subform for the Suppliers table. In this form there are two TextBox controls just above and to the right of the Order Details subform. These TextBoxes show the number of rows in each subform. Since there's only one supplier per product in the Northwind database, the second TextBox will always show one, but subforms aren't restricted to just one row in their recordset. By using two subforms, I can show a common scenario where there are multiple subforms on a main form.

 

200402_rd4
Figure 4

It's actually easier to compute the count of rows in a subform than to compute a sum based on the rows in a subform's recordset. This is because all you need to do is assign the RecordCount property for a subform's recordset to the TextBox on the main form. There's no need to loop through the rows in a recordset for a subform.

The Form_Current event procedure for frmProducts shows the syntax for processing two subforms from a main form. First, the procedure assigns a reference to the Subform control (Child20) for the subform based on the Order Details table. Then, the procedure uses the control's Form property to return the subform's recordset, which, in turn, allows me to access the RecordCount property value. The procedure assigns the value to txtOrderCount. Next, the procedure repeats the process for the Subform control pointing at the Suppliers table to populate txtSupplierCount:

Private Sub Form_Current()

Dim ctl1 As Access.Control

'Point at first Subform control

Set ctl1 = Me.Child20

Me.txtOrderCount = _

    ctl1.Form.Recordset.RecordCount

'Point at second Subform control

Set ctl1 = Me.Suppliers

Me.txtSupplierCount = _

    ctl1.Form.Recordset.RecordCount

set ctl1 = nothing

End Sub
 

Note: You could use this direct code and skip the ctl1 control reference
Me!txtOrderCount = Me!Child20.Form.Recordset.RecordCount

 

This article has drilled down on how to process and display Subform control values on a main form. I showed two implementations of this kind of processing but, more importantly, provided the background knowledge that will allow you to extend and adapt the two samples shown here.

 

Your download file is called 402dobson.ZIP in the file SA2004-02down.zip

This can be purchased with all the other downloads on this page

 
Other Pages You Might Like To Read