Step out of loop when a condition is met

  • Thread starter Thread starter BruceM
  • Start date Start date
B

BruceM

I have the following code as part of validatation in a form's Before Update
event:

Dim ctl As Control, blnNoVal as Boolean

For Each ctl In Me.Controls
If IsNull(ctl) And ctl.Tag = "R" Then
blnNoVal = True
Else
blnNoVal = False
End If
Next ctl

If blnNoVal = True Then
Exit Sub
Else
' perform validation
End If

Five controls in one section of the form have "R" as the tag. If these
controls are also null, it means that the section of the form was not
started, which is OK. In that case the rest of the validation code is
skipped (Exit Sub). However, if any of the five has a value, they all need
a value. My idea was that if blnNoVal evaluates to False, validation needs
to occur. As the code stands, if the first four controls have a value but
the fifth does not, at the end of the loop blnNoVal will be True, and
validation will not run, but validation needs to run in order to inform the
user that the fifth control needs a value. I have been looking for a way to
step out of the loop and go on to the next section of code as soon as
blnNoVal evaluates to False. I don't know if this is a reasonable approach
in the first place, but it seems rather tidy except for the stated question.
 
It just occurred to me that maybe the loop should be in a separate function,
which would be called in place of the loop code. As a standalone function,
it should be possible to exit the function as soon as blnNoVal is False.
I'll give that a try soon, and post back if it works, unless somebody has a
comment or observation before then.
 
blnNoVal = False
For Each ctl In Me.Controls
If IsNull(ctl) And ctl.Tag = "R" Then
blnNoVal = True
End If
Next ctl

or (better)

blnNoVal = False
For Each ctl In Me.Controls
If IsNull(ctl) And ctl.Tag = "R" Then
blnNoVal = True
Exit For
End If
Next ctl
 
Thanks for the reply. I used a variant of option 2. As it happens blnNoVal
is declared at the module level, and is set to False elsewhere. In my
attempt to streamline the question I left that out. Anyhow, that Exit For
line was the one I sought, and it worked as intended. I had some minutes of
aggravation until I remembered to set the code to break only on unhandled
errors. I had set it to break on all errors for some diagnostic reason,
then left it there. When I clicked a custom Next Record button, but the
Before Update event was cancelled as the result of the validation code, I
received an error message that I thought I had trapped ("You can't go to the
specified record"). Once I set the code to break only on unhandled errors
all was well, but it was moderately stressful while it lasted.
 
Back
Top