By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Reverse loop through controls on a form

P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. nullMsg = "You need to enter a value in: " & Chr(13)   'Message for msgbox
  2. nullTitle = "Value Required"   'Title for msgbox
  4. For Each ctl In Controls   'Loop through all the controls on (current) form
  5. '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
  6. If IsNull(ctl) And (ctl.Tag <> "Corrected Date") And (ctl.Tag <> "Progress Note ID") Then   
  7. controlName = controlName + (ctl.Tag & Chr(13))
  8. ctl.SetFocus
  9. End If
  10. Next ctl
  12. If Me.FormIDFK = 36 And IsNull(Me.ProgressNoteIDFK.Value) Then
  13. controlName = controlName + (Me.ProgressNoteIDFK.Tag & Chr(13))
  14. End If
  16. If Not IsEmpty(controlName) Then
  17. MsgBox nullMsg & controlName, vbExclamation, nullTitle
  18. Cancel = True
  19. End If
On line 5, I'd like for (last) to be first empty control.

Thanks for the help...
Feb 8 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 5K+
P: 8,623
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:
Expand|Select|Wrap|Line Numbers
  1. Dim intCtlCounter As Integer
  3. For intCtlCounter = Me.Controls.Count - 1 To 0 Step -1
  4.   Select Case Me.Controls(intCtlCounter).ControlType
  5.     Case acTextBox, acListBox, acComboBox, acCommandButton
  6.       Debug.Print Me.Controls(intCtlCounter).Name
  7.     Case Else
  8.       'Not interested in
  9.   End Select
  10. Next
Feb 9 '09 #2

P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl, ptr, i, controlName, nullMsg, nullTitle
  3. nullMsg = "You need to enter a value in: " & Chr(13)
  4. nullTitle = "Value Required"
  6. For i = Me.Controls.Count - 1 To 0 Step -1
  7. Select Case Me.Controls(i).ControlType
  8. Case acTextBox, acComboBox
  9. If IsNull(i) And (i.Tag <> "Corrected Date") And (i.Tag <> "Progress Note ID") Then
  10. contolname = controlName + (i.Tag & Chr(13))
  11. i.SetFocus
  12. End If
  13. Case Else
  14. End Select
  15. Next
Thanks again...
Feb 9 '09 #3

Expert 5K+
P: 8,623
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.
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. Dim strControls As String
  3. Dim varControls As Variant
  5. For Each ctl In Me.Controls
  6.   If ctl.Tag = "Required" And IsNull(ctl) Then
  7.     strControls = strControls & ctl.Name & ";"
  8.   End If
  9. Next
  11. If Len(strControls) <> 0 Then       'at least 1 Control missing Data
  12.   MsgBox "You must supply values for the following Controls:" & vbCrLf & vbCrLf & _
  13.           Left$(strControls, Len(strControls) - 1), vbExclamation, "Missing Data in Controls"
  14.             varControls = Split(strControls, ";")       'Load Control Names into an Array
  15.               'The Value at the Top of the Array will be the 1st Control missing Data
  16.               DoCmd.GoToControl varControls(0)
  17. End If
Feb 9 '09 #4

Expert Mod 15k+
P: 31,419
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.
Feb 9 '09 #5

Expert Mod 15k+
P: 31,419
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.
Expand|Select|Wrap|Line Numbers
  1. 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) :
Expand|Select|Wrap|Line Numbers
  1. If Not blnFound Then
  2.   ctl.SetFocus
  3.   blnFound = True
  4. End If
This processes through the items in forward order, but only selects the first one found (if any).
Feb 9 '09 #6

P: 579

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.
Feb 9 '09 #7

Expert Mod 15k+
P: 31,419
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).
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.
Feb 11 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.