When programmatically referencing subform controls, you need to keep a couple of things in mind. First, the subform is a control on the main form. This means you cannot refer to the subform controls directly. Instead, you must reference the Form property of the subform control. Then you can reference the subform controls in the standard way. See Chapter 1 for an introduction to using VBA to work with controls on forms. Second, when dealing with subforms with multiple levels of nesting, you can selectively choose to deal with only the levels your application requires. Therefore, if you have a set of forms with three levels (main, subform within main, and subform within subform within main), you can deal with just the top two levels if that's all your application requires.
The following sample demonstrates the syntax for processing the main/subform shown in Figure 6-9. Although the form includes two levels of subforms, this sample processes just the main form and its subform. (The code sample beginning deals with the three levels of forms simultaneously.) The sample you're about to see begins by demonstrating a couple of syntax conventions for referencing subform control values. Next it contrasts conventions that expose the record sources of the main form and the subform. Then the code illustrates how to count the total number of controls on the main and subforms. The sample concludes by enumerating the first 10 records on the main form and printing the subform records for each.
The procedure in this code sample starts by opening the frmMyOrders form, the main form in the example. After opening this form so that it becomes a member of the Forms collection, the sample sets a form object reference, frm1, to the frmMyOrders form. Then it sets a control reference, ctl1, to the main form's subform control. This control has the name MyOrderDetails-the same name as the record source behind the subform. The next two lines of code demonstrate two different programming styles for referencing the OrderID text box control on the subform. The style that explicitly invokes the Form property of the subform control is very robust; you will often need to reference subform objects and properties with this syntax. In addition, the syntax makes it clear that the subform is a control on the main form that has a Form property.
The next two blocks of code demonstrate the similarity between referencing properties and collections for the main and subforms. The RecordSource property represents an SQL string or an object name representing the data behind the form. Because queries exist for both the main and subforms, the references to their record sources return the query names. Notice that ctl1.Form represents a form (the subform) in the same way that frm1 represents the main form. A form's Controls collection contains all the controls on a form. In addition to text boxes and combo boxes, controls can include labels, lines, and subform controls. The Count property of the Controls collections of the main and subforms returns the count of all the controls—not just those that display data.
Perhaps the most interesting segment of the sample is the portion that contains three nested loops. The outer loop passes through the first 10 records on the main form. The middle loop iterates through the subform records for the current main form record. The inner loop searches the subform controls for either text box or combo box controls. When the inner loop finds either type of control, the sample prints a short message stating the name of the control and its current value.
The syntax in these three loops reinforces some familiar concepts and introduces some new ones. Notice that the Form property of the subform control exhibits a Recordset object. The procedure uses the RecordCount property of this object to loop as many times as there are subform records for a main form record. The procedure also invokes the MoveNext method of the subform's Recordset object to navigate from the first subform record through the last. Within a subform record, the procedure iterates through the Controls collection of the subform. The TypeOf function ascertains whether the current control is either a text box or a combo box. Let's take a look at the syntax now:
Sub SyntaxForSubForms()
Dim frm1 As Form
Dim ctl1 As Control
Dim ctl2 As Control
Dim int1 As Integer
Dim int2 As Integer
'Open a main/subform
DoCmd.OpenForm "frmMyOrders"
'Assign pointers for main form and subform
'control; MyOrderDetails is the name of the
'subform control on the frmMyOrders main form
Set frm1 = Forms("frmMyOrders")Set ctl1 = frm1.MyOrderDetails
'Two different ways to print the OrderID control value
'on the subform
Debug.Print ctl1!OrderID
Debug.Print ctl1.Form.Controls("OrderID")Debug.Print
'Print the record source settings for the main form
'and the subform
Debug.Print frm1.RecordSource
Debug.Print ctl1.Form.RecordSource
Debug.Print
'Print the number of controls on the main and subforms
Debug.Print frm1.Controls.Count & _
" controls are on the main form."Debug.Print ctl1.Form.Controls.Count & _
" controls are on the subform."Debug.Print
'Move to the form's first record, and loop through the
'next 10 main form records and the subform records
'corresponding to each main record; within each subform
'record, loop through the controls on the subform
DoCmd.GoToRecord , , acFirst
For int1 = 1 To 10
Debug.Print vbCrLf & "Data for record " & int1 & "." For int2 = 0 To ctl1.Form.Recordset.RecordCount - 1 For Each ctl2 In ctl1.Form.Controls If TypeOf ctl2 Is TextBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a text box that equals " & ctl2 & "." ElseIf TypeOf ctl2 Is ComboBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a combo box that equals " & ctl2 & "." End If Next ctl2 ctl1.Form.Recordset.MoveNext Debug.Print String(5, "-") Next int2 DoCmd.GoToRecord , , acNextNext int1
DoCmd.Close acForm, "frmMyOrders"
'Clean up objects
Set ctl1 = Nothing
Set frm1 = Nothing
End Sub
This next sample builds on the previous one by adding another form level. Happily, adding another level of subform does not substantially change how you programmatically access controls. This section demonstrates how to access the subdatasheet of a subform on a main form. You still have to refer to the Form property of the subform control on the main form. In addition, you need to usethis same referencing scheme for the subform control on the subform. This second reference provides a path to subdatasheet values.
There is another critical trick for accessing the control values in a subdatasheet for a subform: the subdatasheet must be open. You do this by setting the main form's subform SubdatasheetExpanded property to True. Without this step, references to subdatasheet control values can generate a run-time error. Although this won't always happen, keep in mind that it's good practice to set the SubdatasheetExpanded property to True whenever your code needs access to the controls on the subdatasheet of a subform.
In this sample, at the subdatasheet level the code merely prints the values of the subdatasheet row. The subdatasheet row specifies the additional detail, such as on-hand inventory or supplier name and contact information for the product in an individual line item of an order. See Figure 6-9 for a sample of the data that is available in the subdatasheet.
The code sample begins by opening the frmMyOrders form and creating three object references, which simplify the expressions throughout the procedure. The first object reference, frm1, points at the main form. The second reference, ctl1, points at the subform control on the main form, and the third reference, ctl3, points at the subdatasheet on the subform.
The next two blocks of code print the RecordSource property settings for the main form, its subform, and the subdatasheet on the subform. Notice that the code sets the SubdatasheetExpanded property to True before attempting to reference the subdatasheet. Next the procedure illustrates the syntax for counting the controls on the main form and the two subforms nested successively below it.
The next block repeats the code for enumerating the subform control values for each record on the main form. In addition, this sample accesses the control values on the subdatasheet. This provides the additional product detail described a moment ago.
The loop for the subform nested within a subform is less complicated than the loop for a subform on the main form. There are several reasons for this. Only one subdatasheet row exists per subform record. All the controls with data on the nested subform are text boxes. The program follows:
Sub SyntaxForSubDatasheetOnSubForm()
Dim frm1 As Form
Dim ctl1 As Control
Dim ctl2 As Control
Dim ctl3 As Control
Dim ctl4 As Control
Dim int1 As Integer
Dim int2 As Integer
'Open a main/subform
DoCmd.OpenForm "frmMyOrders"
'Assign pointers for main form and subform
'control
Set frm1 = Forms("frmMyOrders")Set ctl1 = frm1.MyOrderDetails
Set ctl3 = ctl1.Form.MyProducts
'Print the record source settings for
'the main form, the subform, and the
'expanded subdatasheet of the subform
Debug.Print frm1.RecordSource
Debug.Print ctl1.Form.RecordSource
ctl1.Form.SubdatasheetExpanded = True
Debug.Print ctl3.Form.RecordSource
Debug.Print
'Print the number of controls on the main and subforms
Debug.Print frm1.Controls.Count & _ " controls are on the main form."Debug.Print ctl1.Form.Controls.Count & _
" controls are on the subform."Debug.Print ctl3.Form.Controls.Count & _
" controls are on the subdatasheet."'Move to the form's first record, and loop through the
'next 5 main form records and the subform records
'corresponding to each main record; within each subform
'record, loop through the controls on the subform
DoCmd.GoToRecord , , acFirst
For int1 = 1 To 5
Debug.Print vbCrLf & "Data for record " & int1 & "." For int2 = 0 To ctl1.Form.Recordset.RecordCount - 1 For Each ctl2 In ctl1.Form.Controls If TypeOf ctl2 Is TextBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a text box that equals " & ctl2 & "." ElseIf TypeOf ctl2 Is ComboBox Then Debug.Print String(5, " ") & ctl2.Name & _ " is a combo box that equals " & ctl2 & "." End If Next ctl2'Loop through the controls on the subdatasheet
'returning just text boxes and their values
For Each ctl4 In ctl3.Form.Controls If TypeOf ctl4 Is TextBox Then Debug.Print String(10, " ") & ctl4.Name & _ " is a text box that equals " & ctl4 & "." End If Next ctl4 ctl1.Form.Recordset.MoveNext Debug.Print String(5, "-") Next int2 DoCmd.GoToRecord , , acNextNext int1
DoCmd.Close acForm, "MyOrdersMainSub"
'Clean up objects
Set ctl1 = Nothing
Set ctl3 = Nothing
Set frm1 = Nothing
End Sub
An
excerpt from
Programming Microsoft Access Version 2002
By
Rick Dobson