The Access subform wizards do much of the workin connecting a main form to its subform. In this article, Rick Dobson goes beyond the wizards toshow you how to perform two new tasks with a subform. He also takes you behindthe scenes to increase your understanding of how to access the subform inside asubform control.
A main/subform design is a standard way of presentingdata 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 mainform. For example, a subform shows the line items for the sales order numberdisplayed on the main form. The subform refreshes the line items that it showswhen the order number on the main form changes. It’s not uncommon for one mainform to have multiple subforms. Each of the subforms shares one or more fieldvalues with the main form. It’s those shared values that allow Access tosynchronize the rest of the subform values with the data showing on the mainform.
You can think of the main form as a parent to itssubforms. In managing the relationship between main and subforms with code,it’s useful to recall that a Subform control on the main form represents thesubform that the control contains. The Subform control has a Form property,which lets the code from your main form access the controls and recordsetvalues on the subform. By tapping the Subform control and its Form property,you can perform many tasks, including enumerating the subforms on a formor 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 exampleforms. The accompanying Download file for this article imports the Categories,Order Details, Orders, Products, and Suppliers tables from the Northwind.mdbfile. Built-in parent-child relationships exist between selected pairs of thesetables, including Orders and Order Details, Products and Suppliers, as well asCategories and Products. When you use the Access 2002 or Access 2003 AutoFormWizard with the Orders, Products, or Categories tables, you automaticallycreate a main/subform. Earlier versions of Access require you to createseparate main and subforms and then drag the child form from the Databasewindow 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 theAutoForm Wizard) based on the Suppliers tables and called it frmSubSuppliers. Idragged this form from the Database window onto the frmProducts form in designview. As a result, the frmProducts form includes two subforms–one automaticallyinserted by AutoForm and another manually added in design view.
It frequently happens on main forms that you want toshow a summary, such as a count or a sum, of the data on a subform. Todemonstrate, I added an unbound TextBox to the frmOrders form to display thetotal extended price for the detail lines on the subform. Code behind the formtotals the product of three column values on the subform for all the rows onthe subform.
In addition, I added two unbound TextBox controls tothe 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 asolid 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 Subformcontrols. Therefore, enumerating the forms in a project lists both the standardforms (forms without one or more Subform controls) and the main/subforms in aproject. 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’llneed to build on this technique for listing ordinary forms when you focusexclusively on main/subforms and their Subform controls. The FormNameAndSource procedure shown here lists eachof the forms in a project along with its record source. However, when a formincludes a main form and a subform, the record source will be only for the mainform. Since all the forms in the sample project are main/subforms (exceptfrmSubSuppliers), all these forms show just the record source for the mainform:
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 theCurrentProject. An AccessObject provides the minimum of information aboutwhatever object it refers to but will work with any object in an Accessapplication (for example, forms, reports, tables, and queries). An AccessObjectobject in the AllForms collection is referring to something very different froma Form object in the Forms collection in a project. Objects in the Formscollection correspond exclusively to open forms. AccessObject objects in theAllForms collection correspond to forms whether or not they’re open. TheAccessObject object and its corresponding collections were introduced intoAccess starting with Access 2000.
A member of the AllForms collection has an IsLoadedproperty that returns a Boolean value of True when a form is open and a valueof False otherwise. The FormNameAndSource gathers all the form names with theirRecordSources to a string variable. However, a form’s RecordSource propertyisn’t available unless a form is open. My FormNameAndSource procedure takesadvantage of the IsLoaded property to open an Access form if it’s not alreadyopen. When a form is open, the procedure simply adds the form’s RecordSourceproperty to the string variable (str1) that summarizes all the forms in aproject. If a form isn’t open, then the procedure opens the orm beforeattempting to append the form’s RecordSource property value to str1.
After finishing with a previously closed form, theprocedure returns the form to its former closed state. After iterating throughall the members of the AllForms collection, the procedure displays the formnames along with the record sources in str1 by invoking the Print method of theDebug object. By interspersing vbTab and vbCrLf constants in str1, the outputis formatted with blank lines between forms (making form names appear onseparate lines from their record sources), and indenting record sources fromtheir forms.
Enumerating the subforms in a project
There’s no built-in collection for enumeratingsubforms in a project (or even within a form). However, each Access form has aControls collection. Only the Subform control has the properties for managing asubform, such as the SourceObject property. The SourceObject property points atthe RecordSource for a subform. Therefore, you can present data about thesubforms in a project by searching each of the project’s forms for Subformcontrols and then displaying selected properties of those Subform controls,such as their Name and SourceObject properties.
My CallSubFormNameControlSource andSubFormNameControlSource procedures (see the following code block) show how toenumerate all the subforms in the current project. CallSubFormNameControlSourceiterates through the forms in a project and either immediately callsSubFormNameControlSource or opens the form before calling the sub procedure. Ifthe main procedure opens a form before calling its subroutine, the mainprocedure 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 bysetting a reference to the form named in its argument (frmName) and printingits argument’s value to the Immediate window. Then, the procedure loops throughthe controls on the form designated in its argument, using a For…Nextstructure. An If…Then statement within the For…Next structure filters forcontrols with a Subform type. The test in the If…Then statement uses a TypeOffunction to filter for Subform control types.
After finding a Subform control, the procedure assignsthree items to a string (str1) that summarizes subform specifications on themain 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:
The other two properties are properties of the Subformcontrol (rather than the form that it references). Therefore, these items don’tinclude the Form property when designating them.
SubFormNameControlSource writes to the Immediatewindow after looping through all the controls on a form (provided that str1isn’t a zero-length string). The str1 string reports on all the Subformcontrols and their corresponding subforms on the form. Since the For…Nextloop iteratesthrough all the controls on the form, it will display informationfor all the Subform controls on a form. If str1 is a zero-length string, theprocedure writes to the Immediate window that there are no subforms on the mainform.
Figure 1 shows the Immediate window output fromSubFormNameControlSource. As you can see, all of the four forms from the sampleapplication are listed. In addition, the output precisely describes the subformstatus of each form. For example, the output indicates that the frmSubSuppliersform has no subforms, but the frmProducts form has two subforms. ThefrmCategories and frmOrders forms each have a single subform. The output givesthe name of the Subform control for each subform.
Going one step further, through the Form property ofthe Subform control you can access the values on a subform that synchronizewith the current record on a main form.
Enumerating the values on a subform
Being able to enumerate the values on a subform can bevery useful. For example, it lets you accumulate or develop computed fieldvalues on the main form based on subform values. In fact, you can even developa computed value for each row on a subform and then sum the computed valuesacross rows. The rows displayed in the subform will automatically changewhenever the data on the main form changes. Therefore, you must specify mainform row values when enumerating subform values. TheCallEnumerateSubformControlValues and EnumerateSubformControlValues proceduresdemonstrate 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 mainform and optionally designates a range of rows on the main form for which toenumerate values on a subform. The procedure starts by referencing the mainform 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. Youcan use the CallSubFormNameControlSource and SubFormNameControlSourceprocedures to generate appropriate code for your own applications.
One main reason for designating a main form is to openit. CallEnumerateSubformControlValues closes the main form just before ending.When modifying this code for your own project, you’ll need to alter the mainform name and the Subform control name.
EnumerateSubformControlValues lists the values on thesubform 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 endingrow on the main form for enumerating subform recordset values.
CallEnumerateSubformControlValues shows the syntax forboth ways of calling EnumerateSubformControlValues. If you don’t specifystarting and ending main form row values, EnumerateSubformControlValues usesdefault values of 1 and 2 to designate the first and second rows as thestarting and ending rows.
EnumerateSubformControlValues begins by moving to thestarting row (intFirstRow) on the main form for enumerating subform values.This starting row will either be a value explicitly passed to the procedurefrom its calling procedure or the default value of 1. The heart of theprocedure is a set of nested For…Next loops that perform the enumerationeventually displayed in the Immediate window. The outer loop designates thestarting 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 recordsetmatching the current row on the main form. The innermost loop passes throughall the controls on the subform. As the code passes through each control, itprints both the control’s name and value for the current row in the subform’srecordset. Both the second and third loops specify the collection of values orobjects over which they iterate by using the Form property of the Subformcontrol (ctl1). Before returning to the top of the outer loop, the procedureadvances the current record for the main form by one using the DoCmd object’sGoToRecord method.
Figure 2 shows an excerpt from the output ofEnumerateSubformControlValues with a starting row value of 2. Notice thatlookup values, such as the product name for ProductID in the Order Detailstable, don’t appear. If you require the lookup values (which are product namesin this case), you need to code the translation yourself. On the other hand,the numeric UnitPrice, Quantity, and Discount column values appear in a formatsuitable for computing extended price.
Summing subform values
One important reason for understanding how toenumerate subform values is to be able to summarize subform data on a mainform. Figure 3 shows the form frmOrders, which contains a TextBox (it’s the onejust above the subform, with the label Total Extended Price). The TextBoxcontains the sum of the extended price for each row in the subform. Theextended price for a row is the product of the values in the Quantity and UnitPrice columns multiplied by 1 minus the value in the Discount column. The formin Figure 3 doesn’t show values for main controls that don’t permit updates (forinstance, Customer, Employee, and Ship Via), or for controls for which there’sno value (for example, Ship Region).
The Form_Current event procedure for frmOrderscomputes the totalextended price for the line items in an order and formatsthe computed value as currency in the TextBox for total extended price. Byusing the Form’s Current event to hold this code, the total extended pricevalue is always fresh whenever a user moves to a new record on the main form orwhen 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 areference to the Subform control (Child28). Then the procedure loops throughthe rows of the recordset in the subform that match the row showing on the mainform. Within the loop, the procedure computes the total extended price as thesum f the extended price for each row. The procedure uses the Form ropertyfor the Subform control (ctl1) to specify the End value in the For…Next loopand the Quantity, UnitPrice, and Discount control values on the subform. Anassignment statement at the close of the procedure formats the aggregatedextended price values as currency in the txtTotalExtendedPrice TextBox.
Counting subform rows
The frmProducts form illustrates a main/subform designwith 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 OrderDetails column values for the first product, Chai, along with a clipped versionof the subform for the Suppliers table. In this form there are two TextBoxcontrols just above and to the right of the Order Details subform. TheseTextBoxes show the number of rows in each subform. Since there’s only onesupplier per product in the Northwind database, the second TextBox will alwaysshow one, but subforms aren’t restricted to just one row in their recordset. Byusing two subforms, I can show a common scenario where there are multiplesubforms on a main form.
It’s actually easier to compute the count of rows in asubform than to compute a sum based on the rows in a subform’s recordset. Thisis because all you need to do is assign the RecordCount property for asubform’s recordset to the TextBox on the main form. There’s no need to loopthrough the rows in a recordset for a subform.
The Form_Current event procedure for frmProducts showsthe syntax for processing two subforms from a main form. First, the procedureassigns a reference to the Subform control (Child20) for the subform based onthe Order Details table. Then, the procedure uses the control’s Form propertyto return the subform’s recordset, which, in turn, allows me to access theRecordCount property value. The procedure assigns the value to txtOrderCount.Next, the procedure repeats the process for the Subform control pointing at theSuppliers 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
This article has drilled down on how to process anddisplay Subform control values on a main form. I showed two implementations ofthis kind of processing but, more importantly, provided the backgroundknowledge that will allow you to extend and adapt the two samples shown here.