Reverse loop through controls on a form

[beacon]

Hi everybody,

This may be an easy one, but I'm having a lot of trouble with it. I have a continuous form and I want to validate that the user has entered something in each of the required fields in the BeforeUpdate event for the form.

This I can do...the fun part is that I want to do it in reverse so that when I call the SetFocus method, it goes to the first control that is empty as opposed to the last one.

To loop forward, I've used the For Each...Next loop, but as far as I know this can't be used to loop backwards. Here's my code:

nullMsg = "You need to enter a value in: " & Chr(13)   'Message for msgbox
nullTitle = "Value Required"   'Title for msgbox
 
For Each ctl In Controls   'Loop through all the controls on (current) form
'If the control is empty and not equal to the tags specified then concatenate a string to nullMsg to notify the user. Then set the focus to the (last) empty control
If IsNull(ctl) And (ctl.Tag <> "Corrected Date") And (ctl.Tag <> "Progress Note ID") Then   
controlName = controlName + (ctl.Tag & Chr(13))
ctl.SetFocus
End If
Next ctl
 
If Me.FormIDFK = 36 And IsNull(Me.ProgressNoteIDFK.Value) Then
controlName = controlName + (Me.ProgressNoteIDFK.Tag & Chr(13))
End If
 
If Not IsEmpty(controlName) Then
MsgBox nullMsg & controlName, vbExclamation, nullTitle
Cancel = True
End If
 

On line 5, I'd like for (last) to be first empty control.

Thanks for the help...

[ADezii]

@beacon
This is a rather odd request, and I hope I am reading it correctly, but to 'Reverse Loop' through the Controls Collection (Reverse Tab Order), you can use this as a Generic Template:

Dim intCtlCounter As Integer
 
For intCtlCounter = Me.Controls.Count - 1 To 0 Step -1
  Select Case Me.Controls(intCtlCounter).ControlType
    Case acTextBox, acListBox, acComboBox, acCommandButton
      Debug.Print Me.Controls(intCtlCounter).Name
    Case Else
      'Not interested in
  End Select
Next

[beacon]

Hi ADezii,

What I'm trying to do is make sure that the user entered a value into each required field and if they missed one (or more), put the tag into a string and print it in a msgbox.

If I have 3 controls (2 combos and 1 textbox) and the user fails to enter a value into one of the combos and the textbox (from left to right on the form), I want to collect the name of the control and print it to the screen. I want to be able to loop through in reverse because I want the first control on the form to get the focus after the test.

If I print out the combo and textbox tag, it will print them out in the correct order, however, if I try to set the focus, it's going to set the focus to the textbox instead of the combo...does that make sense?

It's basically a way to show the user all of the areas they might have skipped over on a form, then sets the focus to the first control that was skipped on the form.

I tried your code and used the IF statement I had embedded in my For Each...Next loop, but it gave me an 'Object Required' error.

Here's the code:

Dim ctl, ptr, i, controlName, nullMsg, nullTitle
 
nullMsg = "You need to enter a value in: " & Chr(13)
nullTitle = "Value Required"
 
For i = Me.Controls.Count - 1 To 0 Step -1
Select Case Me.Controls(i).ControlType
Case acTextBox, acComboBox
If IsNull(i) And (i.Tag <> "Corrected Date") And (i.Tag <> "Progress Note ID") Then
contolname = controlName + (i.Tag & Chr(13))
i.SetFocus
End If
Case Else
End Select
Next
 

Thanks again...

[ADezii]

@beacon
Here is a cute little Algorithm that I developed that should suit your needs. For any Control that requires some form of Data Input, simply set its Tag Property to Required in the Properties Window. The Generic Code Template will do the rest.

Dim ctl As Control
Dim strControls As String
Dim varControls As Variant
 
For Each ctl In Me.Controls
  If ctl.Tag = "Required" And IsNull(ctl) Then
    strControls = strControls & ctl.Name & ";"
  End If
Next
 
If Len(strControls) <> 0 Then       'at least 1 Control missing Data
  MsgBox "You must supply values for the following Controls:" & vbCrLf & vbCrLf & _
          Left$(strControls, Len(strControls) - 1), vbExclamation, "Missing Data in Controls"
            varControls = Split(strControls, ";")       'Load Control Names into an Array
              'The Value at the Top of the Array will be the 1st Control missing Data
              DoCmd.GoToControl varControls(0)
End If

[NeoPa]

Beacon, this is a perfect illustration of why it's so important to express your problem clearly in the first place. Most experts will try to answer the question posed, rather than the question you're really after (particularly if they have no way of knowing what that is).

My original (intended) answer is no longer valid, but I'll post it in case anyone else, with the question you posted, comes looking.

It's not possible to process through a For Each loop in reverse order. ADezii has already provided some code which processes through in reverse using the FOR code instead. However, it is possible to process forwards but to break out of the loop code when you have completed doing what you want to do. An Exit For after line #8 in the original code will do that for you.

[NeoPa]

As for your current request, that can be done fairly straightforwardly using a boolean (or flag) variable within your code.

Start by creating the variable. We'll call it blnFound.

Dim blnFound As Boolean

Set it to False before the loop if necessary. It starts as False by default anyway, so not generally necessary except for style.
Now, instead of line #8 (ctl.SetFocus) :

If Not blnFound Then
  ctl.SetFocus
  blnFound = True
End If

This processes through the items in forward order, but only selects the first one found (if any).

[beacon]

@NeoPa

Hi Neo,

I hope this doesn't offend because that's not my intent...just trying to clear things up so there won't be any hard feelings.

I actually thought I did a pretty good job of explaining the problem okay the first time. The second time I just elaborated a little further because it didn't seem like ADezii quite got it. The same outcome was there for both of my posts, which was to display all of the controls that were empty based on their tag, print the results in a message box, and then set the focus to the first control that was picked up during the search.

Setting the focus to the first control (since the For Each...Next loop I provided obviously was scooping up controls from left to right and returning the right-most control with the setfocus) was sole intention and I was hoping to do it using a loop because I'm trying to get better at coding.

Looking back, I'll agree that the title of my post could have been better, but the content was essentially the same, especially if you look at what the code does in each instance.

I will use the boolean flag and see how that turns out. It looks like that's exactly what I'm looking for.

Thank you Neo and ADezii, as always for all your help...I really do appreciate it and will explain myself better in the future.

[NeoPa]

@beacon
No offense taken. I can handle that not everyone in the whole world agrees with everything I say (although I'll do something about that as soon as I work out what).
@beacon
Reading it again, I have to say that I absolutely agree, and I apologise for the inappropriate post.

There's sometimes a difference between "Not guilty" and "Innocent". You fall squarely into the latter category in this case. This was my mistake. I paid too much attention to the title and not enough to the text.