CODEWITHSUNDEEP
ms-accessvbams-access-2007
Nathaniel Bendinsky
Nathaniel Bendinsky

Reputation: 143

VBA Iterate over controls on main form while ignoring subform

I have a button which is meant to clear all active controls on a (search) form. However, the output of each search is displayed in a subform, and seems to be implemented as Text Boxes. (Though it appears as a spreadsheet, the Design View of the subform shows text boxes.)

Because of this, when I set the Value of all acTextBox controls on the form to "", it clears something on the subform as well and all further searches return #Name in all fields until the form is reopened.

Further, while this error is occurring, clicking on the subform's Form object generates a message warning that Object invalid or no longer set. Attempts to edit the associated method often cause the form to open in design view and take focus, which may or may not be significant. (This occurs as soon as a key is pressed in the method if the design view is not already open.)

The issue seems to be in the following block:

Dim ctl As Control
For Each ctl In Me.Controls
        If (ctl.ControlType = acTextBox) Then
            If Len(ctl.Properties("ControlSource")) = 0 Then
                ctl.Value = "" '***
            End If
        ElseIf ctl.ControlType = acCheckBox Then
            ctl.Value = False
        ElseIf ctl.ControlType = acComboBox Then
            ctl.Value = ""
        End If
Next ctl

The assignment followed by '*** is where I've narrowed down the problem, as commenting out that line prevents the issue from occurring-- but as setting the value of text boxes to "" is a significant portion of what I'm trying to do, I can't just get rid of that.

The ControlSource length comparison was an attempt to exclude subform items from the line following, as they are all bound and main-form items are not. It is nested (as opposed to If (... And ...)) because VBA does not have a lazy Or.

What needs to be done to iterate over all main-form controls without affecting subform controls?

Edit: Clarification of subform/query.

The subform is drawing data from a query with the following statement:

SELECT DW_Query.Map_Number, DW_Query.Map_Name, DW_Query.Map_Type, DW_Query.Town, DW_Query.Address, DW_Query.Street, DW_Query.Block, DW_Query.Lot, DW_Query.group, DW_Query.Folder, DW_Query.Latitude, DW_Query.Longitude FROM DW_Query;

It seems as if the Town field may be removed somehow, as there is sometimes an instance of RTE 2465, can't find the field 'Town' referred to in your expression. However, this occurs in the main form's Form_Open sub, on the following line: 

Me.DW_Query_subform.Form.RecordSource = "SELECT DW_Query.Map_Number, DW_Query.Map_Name, DW_Query.Map_Type, DW_Query.Town," & _
    "DW_Query.Address, DW_Query.Street, DW_Query.Block, DW_Query.Lot, DW_Query.group, DW_Query.Folder, DW_Query.Latitude, DW_Query.Longitude " & _
    "FROM DW_Query;" 'Set subform to use address query. (Faster)

Upvotes: 2

Views: 401

Answers (2)

John
John

Reputation: 1004

You can narrow your iteration for example clear only the controls in the Detail section 

Dim ctl As Control
For Each ctl In Me.Detail.Controls
        If (ctl.ControlType = acTextBox) Then
            If Len(ctl.Properties("ControlSource")) = 0 Then
                ctl.Value = vbnullString
            End If
        ElseIf ctl.ControlType = acCheckBox Then
            ctl.Value = False
        ElseIf ctl.ControlType = acComboBox Then
            ctl.Value = Null
        End If
Next ctl

Upvotes: 0

Gustav
Gustav

Reputation: 55831

You need to set to Null as the textbox may not accept an empty string:

Dim ctl As Control
For Each ctl In Me.Controls
        If (ctl.ControlType = acTextBox) Then
            If Len(ctl.Properties("ControlSource")) = 0 Then
                ctl.Value = Null
            End If
        ElseIf ctl.ControlType = acCheckBox Then
            ctl.Value = False
        ElseIf ctl.ControlType = acComboBox Then
            ctl.Value = Null
        End If
Next ctl

Upvotes: 1

Related Questions