none
How to access a control on a subform using a variable name RRS feed

  • 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?

    Tuesday, February 20, 2018 11:53 PM

Answers

  • 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)

    • Marked as answer by BVitter Wednesday, February 21, 2018 10:10 AM
    Wednesday, February 21, 2018 12:07 AM
  • You can use

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


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

    • Marked as answer by BVitter Tuesday, March 6, 2018 12:11 AM
    Sunday, March 4, 2018 11:28 AM

All replies

  • 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)

    • Marked as answer by BVitter Wednesday, February 21, 2018 10:10 AM
    Wednesday, February 21, 2018 12:07 AM
  • 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


    • Edited by Dirk Goldgar Wednesday, February 21, 2018 12:08 AM
    Wednesday, February 21, 2018 12:08 AM
  • 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
    Wednesday, February 21, 2018 12:35 AM
  • 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

    Wednesday, February 21, 2018 1:42 AM
  • 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.

    Wednesday, February 21, 2018 2:14 PM
  • 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

    Friday, March 2, 2018 7:57 PM
  • 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

    Friday, March 2, 2018 8:15 PM
  • 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

    Sunday, March 4, 2018 12:51 AM
  • You can use

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


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

    • Marked as answer by BVitter Tuesday, March 6, 2018 12:11 AM
    Sunday, March 4, 2018 11:28 AM
  • Thank you, that works great

    Tuesday, March 6, 2018 12:11 AM