locked
list of fields in a form? RRS feed

  • Question

  • Is there a query or any other quick method that will render a list of the fields used in a form or other object?

    Thx!


    —nick

    Friday, June 22, 2012 2:25 PM

Answers

  • dim ctl as control
    For Each ctl In Me.Controls
       'get the property you want (control source or name)
    next

    ctl.name

    or

    ctl.controlsource


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Friday, June 22, 2012 2:37 PM left off s in controls
    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:36 PM
  • If you mean controls, then yes:

    Public Sub ListControls(ByVal someForm As Form)
        Dim vCtrl As Control
        For Each vCtrl In someForm.Controls
            Debug.Print vCtrl.Name; " :: "; TypeName(vCtrl)
        Next
    End Sub

    If not, what do you mean?


    jmh

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:39 PM
  • Not trying to be difficult, but I just need to make sure you're using the terminology correctly!

    Are you asking to get a list of fields from the form's Recordset that are being used on the form, or are you asking to get a list of controls that are on the form?

    To get a list of all of the controls on a form, you'd use code like:

    Dim ctl As Control
      For Each ctl In Forms("NameOfForm").Controls
        Debug.Print ctl.Name
      Next ctl

    Recognize that the form must be open for this to work. As well, the code will simply list any subform controls that might exist: not the controls contained on forms displayed on the subform.

    To get a list of fields from the form's recordset, you'd loop through the controls again, but only worry about those controls that have their ControlSource property set:

    On Error Resume Next
    Dim ctl As Control
      For Each ctl In Forms("NameOfForm").Controls
        If Len(ctl.ControlSource) > 0 Then
          Debug.Print ctl.ControlSource
        End If
      Next ctl
    The "On Error Resume Next" is a lazy way of handling the fact that not every control type has a ControlSource property (but, of course, those that don't have a ControlSource property can't be bound to the form's recordset, and therefore don't matter for what you're trying to do!). If you prefer not being lazy, you could check for the ControlType of each control as you're looping through, and only check the ControlSource property for those control types that have one.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:42 PM
  • THe word Fields is kind of ambifuous in this context, but either way the form has to be open.  A list of the controls on a form is the form's Controls collection and the form's design view displays it near the top left of the design window.  If you want to display it in the immediate window, the code could be:

      For each ctl in Forms![name of form].Controls
         Debug.Print ctl.Name
       Next ctl

    You can view the list of record source fields in the form's design view Field List window.  Or you can use code when the form is open in form view:

       For Each fld In Forms![name of form].Recordset.Fields
          Debug.Print fld.Name
       Next fld

    This information should also be included in the Documenter report.

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:45 PM

All replies

  • dim ctl as control
    For Each ctl In Me.Controls
       'get the property you want (control source or name)
    next

    ctl.name

    or

    ctl.controlsource


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Friday, June 22, 2012 2:37 PM left off s in controls
    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:36 PM
  • If you mean controls, then yes:

    Public Sub ListControls(ByVal someForm As Form)
        Dim vCtrl As Control
        For Each vCtrl In someForm.Controls
            Debug.Print vCtrl.Name; " :: "; TypeName(vCtrl)
        Next
    End Sub

    If not, what do you mean?


    jmh

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:39 PM
  • Not trying to be difficult, but I just need to make sure you're using the terminology correctly!

    Are you asking to get a list of fields from the form's Recordset that are being used on the form, or are you asking to get a list of controls that are on the form?

    To get a list of all of the controls on a form, you'd use code like:

    Dim ctl As Control
      For Each ctl In Forms("NameOfForm").Controls
        Debug.Print ctl.Name
      Next ctl

    Recognize that the form must be open for this to work. As well, the code will simply list any subform controls that might exist: not the controls contained on forms displayed on the subform.

    To get a list of fields from the form's recordset, you'd loop through the controls again, but only worry about those controls that have their ControlSource property set:

    On Error Resume Next
    Dim ctl As Control
      For Each ctl In Forms("NameOfForm").Controls
        If Len(ctl.ControlSource) > 0 Then
          Debug.Print ctl.ControlSource
        End If
      Next ctl
    The "On Error Resume Next" is a lazy way of handling the fact that not every control type has a ControlSource property (but, of course, those that don't have a ControlSource property can't be bound to the form's recordset, and therefore don't matter for what you're trying to do!). If you prefer not being lazy, you could check for the ControlType of each control as you're looping through, and only check the ControlSource property for those control types that have one.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:42 PM
  • THank you, both, ANd yes, I do mean controls. sorry for the terrminology gaff.

    But (either of you) where would you put this code? In the form itself?


    —nick

    Friday, June 22, 2012 2:43 PM
  • THe word Fields is kind of ambifuous in this context, but either way the form has to be open.  A list of the controls on a form is the form's Controls collection and the form's design view displays it near the top left of the design window.  If you want to display it in the immediate window, the code could be:

      For each ctl in Forms![name of form].Controls
         Debug.Print ctl.Name
       Next ctl

    You can view the list of record source fields in the form's design view Field List window.  Or you can use code when the form is open in form view:

       For Each fld In Forms![name of form].Recordset.Fields
          Debug.Print fld.Name
       Next fld

    This information should also be included in the Documenter report.

    • Marked as answer by Nick Vittum Friday, June 22, 2012 2:49 PM
    Friday, June 22, 2012 2:45 PM
  • THank you all for your quick response, Got it!

    —nick

    Friday, June 22, 2012 2:50 PM