How to loop through all controls in a form, including controls in a subform - Access 2007

Question

Hi there,

As the title of my question suggest, how is it possible to loop through all the controls in a form, including subforms.

For example I use the below sub routine to set the background colour of controls with the tag *

Public Sub colCtrlReq(frm As Form)
'  Sets background color for required field -> Tag = *
Dim setColour As String
setColour = RGB(255, 244, 164)
Dim ctl As Control
For Each ctl In frm.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
            If InStr(1, ctl.Tag, "*") <> 0 Then
                ctl.BackColor = setColour
            End If
        End If
Next ctl
Set ctl = Nothing
End Sub

How would alter this to catch the controls in a subform? Thanks in advance for any help or pointers.

Cheers Noel

Answer 1

Access the controls collection of the subform control's Form property.

Beware, the name of the subform control may not be the same as the name of the saved form object.

If your subform control is named SubformControlName, start from here:

For Each ctl In frm!SubformControlName.Form.Controls
    Debug.Print ctl.Name
Next

Update: From your comment, here's what I think you're looking for.

If you don't know the name(s) of your subform control(s) in advance, you can identify which of your form's controls are subform controls at run time.

For Each ctl In frm.Controls
    If TypeName(ctl) = "SubForm" Then
        Debug.Print ctl.Name & " is a SubForm"
        For Each ctlSub in ctl.Form.Controls
            Debug.Print ctlSub.Name
        Next 
    End If
Next

Answer 2

You can use recursion

Public Sub colCtrlReq(frm As Form)
'  Sets background color for required field -> Tag = *
Dim setColour As String
setColour = RGB(255, 244, 164)
Dim ctl As Control
For Each ctl In frm
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
            If InStr(1, ctl.Tag, "*") <> 0 Then
                ctl.BackColor = setColour
            End If
        ElseIf ctl.ControlType = acSubform Then
            colCtrlReq frm(ctl.Name).Form

        End If
Next ctl
Set ctl = Nothing
End Sub