CODEWITHSUNDEEP
ms-accessvbams-access-2010
Newd
Newd

Reputation: 2185

Error handling stopping after first loop through For Each loop

So I have already solved this problem, though I don't understand why my solution was required. I wanted to make a way to check properties on all forms and all controls within my database so I came up with the following code:

Public Function CheckPropertyAllForms()

    Dim obj As Object
    Dim ctl As Control

    Dim blnFound As Boolean

    For Each obj In CurrentProject.AllForms

        DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
        blnFound = False

        For Each ctl In Forms(obj.Name).Controls
        On Error GoTo Next_Control

            If Nz(ctl.ControlSource, "") <> "" Then
                If ctl.ControlSource = "Certain_Field" Then
                    blnFound = True
                End If
            End If

Next_Control:
        Next ctl

        If blnFound = True Then
            Debug.Print obj.Name
        End If

        DoCmd.Close acForm, obj.Name

    Next obj
End Function

However this code would only work once, the second time around it would display the error as if error handling was turned off. So I updated it to this: 

Public Function CheckPropertyAllForms()

    Dim obj As Object
    Dim ctl As Control

    Dim blnFound As Boolean

    For Each obj In CurrentProject.AllForms

        DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
        blnFound = False

        For Each ctl In Forms(obj.Name).Controls
        On Error GoTo Err_Handler

            If Nz(ctl.ControlSource, "") <> "" Then
                If ctl.ControlSource = "Certain_Field" Then
                    blnFound = True
                End If
            End If

Next_Control:
        Next ctl

        If blnFound = True Then
            Debug.Print obj.Name
        End If

        DoCmd.Close acForm, obj.Name

    Next obj

Exit_Handler:
    Exit Function

Err_Handler:
    Resume Next_Control
End Function

Which works exactly how I want it to, however I couldn't find an answer online as to WHY this was happening with my first set of code. Could someone let me know what is happening with my first version of this function to cause the error handling to quit?

Edit: I should also point out that clearly the error handling will be needed because not all controls have a control source. Additionally I don't have a control type check because this function gets updated when a different property is going to be checked.

Upvotes: 0

Views: 329

Answers (1)

David Zemens
David Zemens

Reputation: 53653

Along with your On Error statement, you need to have an On Error GoTo 0 statement, too. In your case, it would probably go right before your Next_Control and you could actually get rid of that so your loop would look like this:

    For Each ctl In Forms(obj.Name).Controls
    On Error Resume Next

        If Nz(ctl.ControlSource, "") <> "" Then
            If ctl.ControlSource = "Certain_Field" Then
                blnFound = True
            End If
        End If
    On Error GoTo 0
    Next ctl

The reason for this is that in your first case, the error handler never clears the error object, and there can only be one error active at a time. When an error is active, and another error raises, the handler fails.

Upvotes: 1

Related Questions