Using For Each loops

The specific number assigned to each item in a collection isn't terribly important. What is important is the fact that VBA provides some special commands for looping through a collection (also called enumerating a collection), where the code looks at each object in a collection either to get information about it or to change it. The special code is a slight variation on the For...Next loop called a For Each...Next loop. The basic syntax for the For Each...Next loop is

For Each objectType in collectionName

'...code to be performed on each object Next where objectType is one of the object type names listed in the second column of Table 8-1, and collectionName is the name of a collection from the third column in that same table. Note that some collections are actually specific objects as well. For example, in Table 8-1, formName needs to be replaced with the name of an open form, and ctrlName needs to be replaced with the name of a specific control on an open form.

Table 8-1 Object Types and Collection Names for For Each... Next Loops


Object Type

Collection Name













Open form


Application.Forms (Open forms)

Open report


Application.Reports (Open reports)






Forms![formName]![ctrl Name].Properties

Recordset field ADODB.Field recordsetName .Fields

Recordset field ADODB.Field recordsetName .Fields

The Forms collection refers to all forms that are currently open. The AllForms collection refers to all forms in the current database, whether they're open or not.

For example, here's a For Each...Next loop that looks at each object in the

Forms collection.

For Each AccessObject in CurrentProject.AllForms

'...code to act on each form goes here Next

Here's a For Each...Next loop that looks at each control on an open form named MyForm:

For Each Control in Forms!MyForm.Controls '...code to act on each control goes here Next

Look at an example you can actually try out. You'll want to open some database that already contains some tables and forms to try this out. Within that database, click the Modules button in the database window and then click New to create a new, empty module. Now you're in the VBA editor.

From the menu bar in the VBA editor, choose InsertOProcedure. In the Add Procedure dialog box that opens, type some simple name (like test), choose Sub as the function procedure type, and click OK. You see the lines Public Sub test() and End Sub in the Code window.

With the procedure, type some code to test. For example, you could type the following For Each loop to try out looping through the AllForms collection:

For Each AccessObject In CurrentProject.AllForms

Debug.Print AccessObject.Name Next

When executed, the For Each...Next loop will repeat once for each form that's contained within the current database. Within the loop, the Debug. Print statement just prints the name of the current object in the collection (using its Name property).

As you can read in Chapter 12, Debug.Print is often used as a debugging tool. Here, you use Debug.Print just to see the name of each object that the

For Each...Next loop encounters.

Any time that you add a Sub procedure to a module, you can test it out just by typing its name (without the following parentheses). In this case, the procedure is named test. So after you get the whole procedure typed into the Code window, as in the top of Figure 8-3, you can type test into the Immediate window and press Enter. That causes the code to run. With each pass through the loop, the code prints the name of the next form in the database. For example, Figure 8-3 shows the results of running the test procedure in one of my databases.

Type test and press Enter to run procedure. Procedure named test

Figure 8-3:

Testing some code in the Immediate window.

Figure 8-3:

Testing some code in the Immediate window.

Name of each form in the current database.

If you change the name CurrentProject.AllForms to CurrentData. AllTables in the test procedure shown in Figure 8-3 and then run the procedure again, the code lists the name of every table in the current database. Likewise, changing CurrentData.AllTables to CurrentData.AllQueries lists all the queries in the current database.

Assume now that you want to create a For Each loop that looks at each control on an open form named Products Form. (This code works only in a database that has a form named Product Form and when that form is open.) In this case, Forms![Products Form].Controls is the name of the collection, and each object in the collection is a control. Thus, a For Each loop to display the name of each control in the Immediate window looks like this:

For Each Control In Forms![Products Form].Controls

Debug.Print Control.Name Next

All objects in Access have a .Name property that returns the name of that particular object. All collections have a .Count property that reflects the number of items in the collection.

0 0

Post a comment