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

Question

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

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