Loop through all unbound controls on a form and cl

问题:

I would like to loop through all UNBOUND controls on my form and clear their data or reset their values. I have textboxes, comboboxes and checkboxes. Every time I try something like this:

Dim ctl As Control
    For Each ctl In Me.Controls
        If IsNull(ctl.ControlSource) Then
            ctl.Value = Nothing
        End If
    Next ctl

I get a runtime error saying:

438 This object doesn't support this property or method.

回答1:

That code loops through every control in the form's Controls collection. The collection includes controls, such as labels and command buttons, which are neither bound nor unbound ... so attempting to reference their .ControlSource generates that error.

For a control such as an unbound text box, its .ControlSource property is an empty string, not Null.

So as you loop through the controls, inspect the .ControlSource for only those control types you wish to target. In the following example I chose text and combo boxes. When the .ControlSource is a zero-length string, set the control's .Value to Null.

For Each ctl In Me.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox ' adjust to taste
        'Debug.Print ctl.Name, Len(ctl.ControlSource)
        If Len(ctl.ControlSource) = 0 Then
            ctl.value = Null
        End If
    Case Else
        ' pass
    End Select
Next