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

That

Others have answered our question, but you really should change this: "For Each ctl In frm" -- to "For Each ctl In frm.Controls". The default collection of a form is actually a union of the Controls and Fields collection, so what you've written could give unexpected results. – Lorineloriner

Cheers David thanks for the advice, have corrected the above code. – That

Re comment "For Each ctl In frm" -- I could not reproduce this problem with Access 2010. – Tribe

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

Tribe

Nice one Remou, that looks exactly like the technique that I am after. – That

@Remou does this achieve its purpose? :-) – Unfit

Great answer. I noticed that you can simplify frm(ctl.Name).Form to ctl.Form (verified in Access 2016). – Jackleg

But how invoke this procedure? using form on load event or anything else? Calling this procedure for each control using on click or so event is so laborious! – Adonic

Answer 2

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

Frightful

Cheers HAnsUp. If you didnt have the name(possibly names) of the subforms in a form, how could you loop through the controls then? – That