Using For Each loops

The specific number assigned to each item in a collection isn't terribly important. What is important is that VBA provides some special commands for looping through (or 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 Column 2 of Table 8-1, and collectionName is the name of a collection from Column 3. Note that some collections are specific objects, too. 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

Object Type

Collection Name

Table

AccessObject

CurrentData.AllTables

Query

AccessObject

CurrentData.AllQueries

Form

AccessObject

CurrentProject.AllForms

Report

AccessObject

CurrentProject.AllReports

Open form

Form

Application.Forms (Open forms)

Open report

Report

Application.Reports (Open reports)

Control

Control

Forms!("formName").Controls

Property

Property

Forms![formName]! [ctrlName].Properties

Recordset field

ADODB.Field

recordsetName.Fields

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

For example, this For Each...Next loop 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 FormslMyForm.Controls

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

For an example you can try out, open a database that already contains some tables and forms. Within that database, click the Create tab, and then select the Module command from the Macro drop-down menu 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 a 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.

Within 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 repeats 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 the AccessObject's 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.

Whenever 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. After you get the whole procedure typed into the Code window, as in the top of Figure 8-3, type test into the Immediate window and press Enter to run the code. With each pass through the loop, the code prints the name of the next form in the database; you see the results in the Immediate window. For example, Figure 8-3 shows the results of running the test procedure in one of our databases.

Figure 8-3:

Testing some code in the Immediate window.

Figure 8-3:

Testing some code in the Immediate window.

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 Formj.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 Formj.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.

Was this article helpful?

0 0

Post a comment