title: Access VBA: Iterating controls on a form, displaying property only some control types have
body: <p>I'm trying to iterate through the controls on a form to display the ControlSource properties of those controls that support ControlSource.</p>
  
  <p>I've found it relatively easy to identify the controls that have ControlSource properties.  The problem is in accessing the property for those controls.  A generic Control object does not have a ControlSource property.  If I was using another language I would simply cast a control that is a listbox to a ListBox object, for example, to access the ControlSource property.  But my understanding is that in VBA there is no type inheritance so I can't cast one type of object to another.  </p>
  
  <p>So how do I access the ControlSource properties of those controls that support the property?</p>
  
  <p>Here's my code so far:</p>
  
  <pre><code>Private Sub IterateControlsOnForm()
  Dim frm As Form, formName As String
  ' The only controls that have a ControlSource property are either
  '   BoundObjectFrame controls or ListBox controls.
  Dim ctrl As Control
  Dim boundObjectFrame As boundObjectFrame, listBoxCtrl As listBox
  Dim boundObjectFrameTypes() As String, listBoxTypes() As String
  Dim ctrlType As String
  
      formName = "MAINFORM"
  
      ' Useful way of populating a string array - use Split function.
      '   Array function only works with Variants.
      boundObjectFrameTypes = _
          Split("CheckBox,ComboBox,CustomControl,GroupLevel", ",")
      listBoxTypes = Split("OptionButton,OptionGroup,TextBox,TextBox", ",")
  
      ' Assumes form is open.
      Set frm = Forms(formName)
      For Each ctrl In frm.Controls
          ' Ignore controls that do not have a ControlSource property.
          ctrlType = TypeName(ctrl)
          If IsStringInArray(ctrlType, boundObjectFrameTypes) Then
              ' **** FOLLOWING LINE FAILS ****
              Set boundObjectFrame = ctrl
              Debug.Print boundObjectFrame.Name &amp; "(" &amp; ctrlType &amp; ") " &amp; _
                  "ControlSource Property: " &amp; boundObjectFrame.ControlSource
          ElseIf IsStringInArray(ctrlType, listBoxTypes) Then
              ' **** FOLLOWING LINE FAILS ****
              Set listBoxCtrl = frm.Controls(ctrl.Name)
              Debug.Print listBoxCtrl.Name &amp; "(" &amp; ctrlType &amp; ") " &amp; _
                  "ControlSource Property: " &amp; listBoxCtrl.ControlSource
          End If
      Next ctrl
  End Sub
  </code></pre>
  
  <p>I've tried two methods of converting a generic Control object to a more specific control that has a ControlSource property.  See the two comments "<strong>** FOLLOWING LINE FAILS **</strong>".</p>