For Each Loop
By Richard Rost
3 years ago
For Each Next Loops. Loop Thru Form Controls.
In this Microsoft Access tutorial, I'm going to teach you how to use a For Each Next Loop in VBA. We'll discuss the pros and cons. We'll see how to loop through all of the controls on a form, and then we'll limit that to just the text boxes with the ControlType property.
Members
Members will learn how to loop thru the elements of a dynamic array, and also learn how to clear all of the values in the text boxes on a form which requires some additional coding.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
VBA Loops Series
Links
Recommended Courses
Syntax
For Each Object In (Collection or Array)
' Your Code Here
Next
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, For each next loop, collections, control object, controls collection, typeof, controltype property, textbox, acTextBox
Subscribe to For Each Loop
Get notifications when this page is updated
Intro
In this video, we will explore the For Each...Next loop in Microsoft Access VBA. We will discuss how this loop works with collections like the Controls collection on a form, and I will show you how to use it to list all controls and filter for specific types such as text boxes. You will also learn about the differences between using ControlType and TypeOf with controls and see practical coding examples using these techniques.
Transcript
Today we are continuing our look at loops in VBA. We are going to be looking at the For Each...Next loop in Microsoft Access.
So far, we have covered For...Next, While...Wend, Do While, and Do Until. Today we are going to look at the For Each...Next loop.
Of course, I recommend you watch the four previous videos in this series. This is a developer-level video, so if you do not know how to program in VBA, watch this first. It teaches you everything you need to know to get started in about 20 minutes.
If you have not watched my Status Box video, go watch this. It is how I prefer to show information to the user in a status box, which is basically a text box on a form. These are free videos. They are on my website and my YouTube channel. Go watch them and come on back.
Continuing on with our loop database that we have been building over the last couple of videos, a For Each...Next loop is different from the other kinds of loops we have looked at so far. Whereas the For...Next and the While loops usually use values (X equals one to five, While X is less than 10, that kind of thing), the For Each loop looks at collections or arrays. It is more object-oriented than what we have been dealing with so far.
A collection is a group of related items, a collection of objects, or an array - a dynamic array of values, for example. Today, we are going to focus on collections.
What is a collection? A collection is a related group of things. For example, there is a Forms collection. There are all the forms in it. There is a Tables collection. Each form has a Controls collection, which contains all the controls on that form. The buttons, the text boxes, the images, the labels - that is all part of the controls on the form. That is the Controls collection.
There are a bunch of different types of controls in Access, and today I am just going to focus on one of them, the Controls collection. So, let us use that to loop through all of the controls on this form.
Let us go to design view. We have a bunch of buttons in here. Wait, so let us copy this guy. I am going to put this one up top over here and bring this down a bit. We are changing things up a little bit. Let us make this the For Each Control loop. Let us call it that. We are going to loop through all the controls on this form. We will call this the For Each button.
Let us right-click, build an event, and bring up our code builder.
Now, if we are going to loop through all of the controls in the Controls collection, we have to have a variable to put each control in. So we are going to say, give me the first control. Oh, that is a button. OK, put that in. Let us call it c for control. What is the next control? Oh, it is a text box. Now we will put that in c and we will look at its properties. We will need a placeholder variable, a temporary variable for each object.
So, dim c as a Control. That is a specific Access type that says a control can be any of those objects - a button, a label, a text box, a combo box, and so on. All the controls we know and love from Access.
Now, how do I loop through all of them on the current form? Well, it is For Each c, For Each control in Me.Controls. Controls is a property of Me, which is the form that you are on. So, loop through the Controls collection of Me (the current form) and give me each control one at a time. That is what that does. When you are done with the loop, Next, and it will loop through all of them.
What do we want to do with each control? Let us take a look at its name: status c.Name. This is my status function. That is it. Let us display the name of each control on this form in the status box. Save it, move over here, close it, open it back up again, and hit the button. There they are. There is a list of every control on this form - the For Each button, the Do Until Goal button, Label 25 because our labels do not have names, Label 25, Label 23, Label 21, the Interest Rate (that is over here), Full Amount (that is that text box), and so on. That is every control on this form.
You can look at other forms too, as long as they are open, like this guy - the customer form. You could come over here and say For Each c In Forms!CustomerF.Controls. Save it, come over here, hit the button. There are all of its controls - IsActive, CreditLimit, CustomerSince, FamilySize. That is all the controls on this one. As long as the form is open, if it is not open, that does not exist and you will get an error message. That is why I am going to set this back to Me.
Now, what if you want just the text boxes? You can look at the control type. ControlType is a property of a control. So, if c.ControlType equals acTextBox (that is a constant you have to know from Access - I will show you a list of them in a minute), then we will do that. This will show you just the text boxes.
Let us blank the status box before we begin so it is clear every time we run it. Save it. Close it. Open it. Hit it. There are just the text boxes. See? One, two, three, four, and of course five - the status box itself is a text box.
If you want a list of all these different control types, I will put a link to this down below in the description. Here they are - acTextBox, TabControl, ToggleButton, PageBreak, ListBox, Label - they are all in here.
I am also going to show you this: you can use TypeOf c Is TextBox. This is a slightly different way of writing the same thing. It does the same thing but it works differently. TypeOf c Is TextBox - these are VBA generic terms that will work across a lot of different applications, including Excel and others. I prefer using ControlType when dealing with Access.
This is my preferred way of working with Access. TypeOf will work with Excel and PowerPoint, but I like the Access-specific method. I will leave this code in here for you so you can see it in case you come across it and know what it is.
I always say this with Access, Office in general, and programming - there are a million ways to do the same things. You can find three or four different ways to do anything. I am just showing you the way that I like because I have been doing it this way for years.
If you like learning this stuff and want to learn more about For Each...Next loops, in my Access Developer 15 class, I use For Each loops when we deal with multi-select list boxes. That is another great way to work with For Each loops. Check that out. I will put a link to that down below.
Now, in the extended cut for the members, I am going to show you how to use a For Each loop to go through the items in a dynamic array. I showed you this in the array video extended cut. We loaded up a dynamic array with those ability score names - Strength, Intelligence, Wisdom, and so on. But what if you do not know how many names are in there? You can use a For Each loop to loop through them without knowing how many - there could be two, there could be 200. For Each will give you all of them.
We will do a second example. We are going to make a button that will clear all of the text box values on a form, like a reset button. So you have a bunch of stuff in here, you click the reset button, and it will loop through all the text boxes and clear them. That is not as easy as you might think. There are a couple of things you have to watch out for, but that will all be covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. All of them. There are hundreds, not just this one.
That is going to do it for today's TechHelp video. I hope you learned something. Live long and prosper, my friends. I will see you next time.
Quiz
Q1. What is the primary purpose of the For Each...Next loop in VBA for Access, as described in the video?
A. It increments integer variables by a fixed amount.
B. It loops through collections or arrays of objects or values.
C. It checks conditions before executing the loop body.
D. It only loops through integer values in a predefined range.
Q2. How is the For Each...Next loop different from the For...Next and While loops?
A. For Each...Next uses conditions instead of values.
B. For Each...Next only works with integer variables.
C. For Each...Next loops through collections or arrays rather than numeric sequences.
D. For Each...Next requires the loop counter to be incremented manually.
Q3. What is a collection in the context of Microsoft Access VBA?
A. A type of value only supported in Excel.
B. A group of related items, such as controls on a form.
C. A single control object.
D. An array that holds string values only.
Q4. Which of the following is NOT an example of a collection in Access as discussed in the video?
A. Forms collection
B. Controls collection
C. Tables collection
D. Integer collection
Q5. What is the correct way to declare a variable to hold each control in a For Each loop in Access VBA?
A. dim c as Object
B. dim c as Form
C. dim c as Control
D. dim c as TextBox
Q6. What does the Controls collection of a form contain?
A. All queries in the database
B. All tables in the database
C. All controls (buttons, labels, text boxes, etc.) on the form
D. All forms within the database
Q7. What does the keyword Me refer to in the context of Me.Controls in the video?
A. The current database
B. The currently open form
C. The currently focused control
D. The entire Access application
Q8. What property would you use to check the type of control in an Access form?
A. TypeName
B. ControlType
C. ControlName
D. ObjectType
Q9. If you want to loop through and take action only on text boxes, how could you check for that in code according to the video?
A. If c.Name = "TextBox"
B. If c.Type = acText
C. If c.ControlType = acTextBox
D. If c Is TextBoxLabel
Q10. What alternative VBA syntax was mentioned for checking if a control is a TextBox?
A. TypeName(c) = "TextBox"
B. c.Text = True
C. TypeOf c Is TextBox
D. c.Type = "TextBox"
Q11. Which statement is TRUE about using TypeOf ... Is in VBA according to the video?
A. It only works in Access and not in Excel.
B. It is a VBA generic syntax that works with various Office applications.
C. It works only with arrays.
D. It does not work with collections.
Q12. What will happen if you try to reference the Controls collection of a form that is not open?
A. VBA will ignore it and continue.
B. It will return an empty collection.
C. An error message will occur.
D. The form will automatically open.
Q13. What is one use case for the For Each...Next loop that was mentioned as being covered in the extended video cut?
A. Resetting all user passwords in the database
B. Looping through dynamic arrays of values
C. Adding new fields to a table
D. Sorting a list of forms alphabetically
Q14. Which of the following is an Access-specific constant used to identify text boxes in the Controls collection?
A. acButton
B. acLabel
C. acTextBox
D. acForm
Q15. What must be true for the For Each loop to successfully reference controls on a form named CustomerF using Forms!CustomerF.Controls?
A. CustomerF must be the only form in the database
B. The database must be closed
C. The CustomerF form must be open
D. The CustomerF form must be set as the startup form
Answers: 1-B; 2-C; 3-B; 4-D; 5-C; 6-C; 7-B; 8-B; 9-C; 10-C; 11-B; 12-C; 13-B; 14-C; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will continue our in-depth look at loops in VBA, with a special focus on the For Each...Next loop in Microsoft Access.
So far in this series, we have covered For...Next, While...Wend, Do While, and Do Until loops. The For Each...Next loop introduces some new concepts, and I recommend catching up with the previous lessons first if you have not seen them. These are aimed at developers, so if you are new to VBA programming, be sure to start with my introductory video. In about twenty minutes, that will give you the foundation you need.
If you have not watched my Status Box video yet, I suggest watching that as well. This covers an easy way to display information for users via a text box placed on a form. These resources are available for free on my website and YouTube channel.
Let's build on the loop database we have been developing together. The For Each...Next loop is different from the other types of loops we have discussed. While the earlier loops typically operate on numerical values or simple conditions (such as looping from 1 to 5, or while X is less than 10), the For Each loop is designed to process collections or arrays. It works in a more object-oriented fashion.
A collection in VBA is essentially a group of related items. For example, you have a Forms collection containing all your open forms, a Tables collection, and within each form, a Controls collection that holds all the controls used on that form. This includes buttons, text boxes, images, labels, and so on. Today, our focus will be on looping through the Controls collection of a form.
Switching to the design view of our form, you will see a number of buttons and other controls present. We will label one of the buttons to represent our For Each Control loop. The goal is to use this button to loop through every control on the current form.
To accomplish this, when building the event code for that button, we need a variable that can hold each control as we inspect it. I will declare a variable 'c' as a Control. This is a type that represents any interactive element on an Access form, whether it is a button, label, text box, combo box, or another control.
To loop through all the controls, I will set up a For Each loop using 'c' in Me.Controls. The 'Me' keyword refers to the form where the code is running, and Controls gives us access to the entire collection of controls on that form. The loop will cycle through each control one by one.
Inside the loop, we can examine properties of each control. For demonstration, I'll display the name of each control in the status box. After saving and running this on the form, pressing our button reveals a list of every control by name, including all buttons, text boxes, labels, and so forth. You will notice that labels often have generic names by default unless you assign one.
You can also loop through the controls on other open forms by referencing them in the Forms collection. For example, looping through Forms!CustomerF.Controls will iterate over all controls on the Customer form, provided it is open. If you try this on a closed form, you will get an error, so be aware.
What if your goal is to work with just one type of control, like text boxes? You can check each control's type using the ControlType property. If the ControlType equals acTextBox (which is the constant for text boxes in Access), the code will process only those controls. Before running the loop, I recommend clearing the status box for a clean display every time. Pressing the button now produces a list containing only the text boxes on the form, including the status box itself.
For your reference, I will provide a link with a full list of these control type constants, such as acTextBox, TabControl, ToggleButton, PageBreak, ListBox, and more. Additionally, another method you may encounter involves checking each control like this: TypeOf c Is TextBox. This syntax is slightly different but achieves the same result. 'TypeOf' is also useful if you are working in applications beyond Access, such as Excel or PowerPoint, though I prefer using ControlType in Access-specific projects. Both approaches remain valid, and I will leave both examples available for you.
Remember, with Access and programming in general, there are often many ways to solve a problem. I am sharing the strategies I have found most effective after years of experience, but you should always use what works best for you.
For those interested in learning more about For Each...Next loops, in my Access Developer 15 class, you will see this loop technique used for handling multi-select list boxes, which is a powerful application. Further details and links are available on my site.
In the Extended Cut of today's lesson, I will demonstrate how to use a For Each loop to cycle through the items in a dynamic array. This is especially useful if you do not know beforehand how many items are present, as the loop will handle any quantity. I previously covered loading a dynamic array with ability score names like Strength, Intelligence, Wisdom, and so on, and will show how to use For Each to process them all.
We will also create a second example where a button clears all the text box values on a form. This works like a reset button, and although it might seem simple, there are several details you need to handle properly. I will explain these steps thoroughly in the extended cut, which is available for Silver members and above. Members get access to all my extended cut videos - there are hundreds to explore.
That wraps up today's TechHelp lesson. You can find a complete video tutorial with step-by-step instructions on everything we discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Introduction to the For Each Next loop in VBA
Understanding collections in Microsoft Access
Using the Controls collection in a form
Declaring a Control variable in VBA
Looping through all controls on a form
Displaying Control.Name in a status box
Accessing controls on another open form
Filtering controls by ControlType property
Using acTextBox to isolate text boxes
Clearing the status box before output
Using TypeOf operator to check control type
Comparison between TypeOf and ControlType approaches
|
