Loop through all unbound controls on a form and clear data

ms-access, ms-access-2010, vba

Solution

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

Problem

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.

Original source