How to access a control on a subform using a variable name

Question

Me(FieldName) works on the same form as the VBA code and Me!subFormx.Form!Field1Name works from VBA on the parent form but I want to access controls on a subform from VBA on the main form or from database module using a variable control name, ie

Control Field1Name, Field2Name, Field3Name, ... on SubFormx

What syntax would work from VBA on the main form to iterate through the control names?

Answer 1

Use

Me.Subformx.Form.Controls(ControlName)

or

    Dim ctl As Control
    For Each ctl In Me.Subformx.Form.Controls
        ...
    Next ctl

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

You can refer to controls on a subform from the parent form by way of a reference to the Form property of the subform control that displays the subform.  For example:

    Dim strControlName As String

    strControlName = "txtControlOnSubform"

    Debug.Print Me.MySubformName.Form.Controls(strControlName)

In the above,"MySubformName" is the name of the subform control (on the main form) that displays the form object having the control named "txtControlOnSubform".

If you want to iterate through all the controls on the subform, you don't have to know their names.  For example, this code might run on the main form:

    Dim ctl As Control

    For Each ctl In Me.MySubformName.Form.Controls
        Debug.Print ctl.Name, ctl.ControlType
    Next ctl

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Dim dbs As Object
Dim obj As AccessObject
Dim ctrl As Control
Set dbs = Application.CurrentProject
Dim Frm As Form
For Each obj In dbs.AllForms
    If obj.IsLoaded Then
        Set Frm = Forms![NAME OF MAIN FORM]![NAME OF SUB-FORM]
        With Frm
                For Each ctrl In Frm.Controls
                    MsgBox ctrl.Name
                Next ctrl
        End With
    End If
Next obj

Answer 4

Dim dbs As Object
Dim obj As AccessObject
Dim ctrl As Control
Set dbs = Application.CurrentProject
Dim Frm As Form
For Each obj In dbs.AllForms
    If obj.IsLoaded Then
        Set Frm = Forms![NAME OF MAIN FORM]![NAME OF SUB-FORM]
        With Frm
                For Each ctrl In Frm.Controls
                    MsgBox ctrl.Name
                Next ctrl
        End With
    End If
Next obj

I don't quite follow what you're doing here.  If you're going to specify the name of the form and the name of the subform, as in:

         Set Frm = Forms![NAME OF MAIN FORM]![NAME OF SUB-FORM]

... then what is the point in looping through all the open forms, as the rest of the code seems to be intending to do?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Dirk:

This is code I used in an application to open each form and change certain controls. In this case, no, you wouldn't need to do that. Just make sure the sub-form is loaded.

Answer 6

This worked great for a specific subform but now I need to access multiple controls on multiple subforms, e.g.

MainForm!Subform1, MainForm!Subform2, ...

Me!subform1.Form!ControlName1.Visible = True works but

Dim FormName as Form

Set FormName = Forms![MainForm]![subform1]

FormName.Controls(Name1).Visible = True does not work

subform1 and name1 are strings that are formed to point to specific controls on multiple subforms

Answer 7

Dim FormName as Form

Set FormName = Forms![MainForm]![subform1]

FormName.Controls(Name1).Visible = True does not work

You've left off a qualifier.  The above wouldn't work, because the expression "Forms![MainForm]![subform1]" would return a reference to a subform *control* object, not the form object that control is displaying.   However, this ought to work (unless something else is also wrong):

    Set FormName = Forms![MainForm]![subform1].Form

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

Thank you, that is one step closer, this works for a fixed subform name

The final step is to reference a variable subform name, e.g. SetFormName = Forms![MainForm]![subformN].Form where N is set based on VBA logic

Answer 9

You can use

Set FormName = Forms![MainForm].Controls("Subform" & N).Form

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 10

Thank you, that works great