VBA endless loop when doing "For Each" against "Me.Controls"

[cgiAlexis]

Using VBA 6.5 with Access 2003 I'm trying to see if the user has edited a field and made it a value that is not the one recorded in the table.

I was planning on making the code nice and neat and having VBA change background colours so the user gets a visual reference of what they have changed. The code is also meant to change the background back if the user changes the value back to the original.

Currently I run a public sub that looks like this:

Sub AmendUpdate()
 
Dim ctlCollection As Variant
Dim ctl As Control
Dim ctlName As String
Dim OldVal, CurrVal As Variant
Dim lngRed As Long, lngGreen As Long
lngRed = RGB(255, 0, 0)
lngGreen = RGB(0, 255, 0)
 
Set ctlCollection = Me.Controls
On Error GoTo BoxColour_err
 
    For Each ctl In Me.Detail.Controls
        If ctl.ControlType = acTextBox Then 'Look at all text boxes
            If ctl.Tag = "AmendCheck" And Left(ctl.ControlSource, 1) <> "=" Then  'Looks only for bound controls that are also tagged for check
            ctlName = ctl.Name
            OldVal = Nz(Me.Controls(ctlName).OldValue, "")  'Change Null to Zero length
            CurrVal = Nz(Me.Controls(ctlName).Value, "")
                If CurrVal <> OldVal Then   'Check that it has changed from .OldValue
                    ctl.BackColor = lngRed  'Make it red if it has
                    GoTo BoxColour_nextCtl  'Go to the next control
                Else: ctl.BackColor = lngGreen    'Detecting the opposite is true for debug
                    GoTo BoxColour_nextCtl  'Go to the next control
 
                End If
            End If
        End If
 
BoxColour_nextCtl:   ' <--- THIS NEEDS FIXING
    'MsgBox "I changed something!"  'Debug the loop problem
    Next ctl
 
BoxColour_err:
    'MsgBox "I broke!"  'Same as above but for failed loops
    Resume BoxColour_nextCtl
 
BoxColour_end:
    'Beep
    Exit Sub
 
End Sub
 

But it ends up looping endlessly while looking through all the text box controls that have the tag "AmendCheck". When I interrupt it we see that the tagged boxes are changed to green (For debug).
I've put my own notes in to these other people's code so if it appears I've misunderstood something then you can see it easily.

For now I guess the first hurdle is to make sure that For Each loop only asseses each of the controls in the set once before ending.

Resources from:
Social.msdn shows detailed use of the code
Easy to understand explaination of the required code
Bytes.com trying to get the .OldValue with BeforeUpdate
Tried to use this
Proof that I used a search engine
Some info I used to understand where to point this code

This is all in aid of the database I'm still developing from this post.

Regards and thanks in advance,
Alexis

[Rabbit]

There's no need for the GoTo lines. Also line 23 should be split up. Also, your exit sub needs to come before your error handling.

[cgiAlexis]

Grade A derpness, thanks very much for your perserverance Rabbit.

If there is anything to learn from this it's that most of the time VBA errors are from very simple things like simply giving the code every possible way to run into an infinite loop as I did with my initial code.

Now I'm off to look into making the code run constantly without locking up VBA, seeing why it doesn't process date changes, blocking write changes to the table until the user is ready to commit all changes to a record.