Using Loops with Collections

Alternatively, the For…Each loop can be used with a collection.

Public Sub forEachCollection1()
Dim element As Variant
Dim animals As Collection
Set animals = New Collection
'Collections are literally collections of objects
'and are a useful feature of MS Access
'They have 4 methods - add, count, item, remove
animals.Add "Dog"
animals.Add "Cat"
animals.Add "Bird"
animals.Add "Buffalo"
animals.Add "Snake"
animals.Add "Duck-billed Platypus"
'We utilise the add method to add the various
'animals to the collection
For Each element In animals
Debug.Print element
Next
'We print out all the elements in the
'animals collection
animals.Remove 3
'We remove an element from the animals collection
'we are removing the 3rd item in the collection (bird)
Debug.Print ""
'prints a blank line
For Each element In animals
Debug.Print element
'printing to the immediate window
Next
'Here we are printing out all the elements in the
'animals collection minus the bird
End Sub

Access contains some collections of its own! Knowing how to utilise these collections, can make life much simpler when coding.

Sub forEachCollection2()
Dim i As Integer
For i = 0 To CurrentProject.AllForms.Count - 1
'CurrentProject.AllForms is a collection and
'therefore has the add, count, item and remove
'methods available
Debug.Print CurrentProject.AllForms(i).Name
'Here we print the names of the forms to the
'immediate window
Next
End Sub

In our database, we have 3 forms (Form1, Form2, Form3) and so the output to the immediate window will be:

Form1
Form2
Form3

Let’s take a look at how to exit a for each loop.

Exit For

To leave the For Each loop before its natural end,we can use the Exit For statement.

Sub forEachExit()
Dim element As Variant
Dim animals(0 To 5) As String
'We have created an array that can hold 6 elements
animals(0) = "Dog"
animals(1) = "Cat"
animals(2) = "Bird"
animals(3) = "Buffalo"
animals(4) = "Snake"
animals(5) = "Duck-billed Platypus"
'Here we fill each element of the array
For Each element In animals
'iterates over the animals collection
Debug.Print element
'print each element to the immediate window
If element = "Buffalo" Then Exit For
'if, at any point, the element becomes equal
Next
End Sub

The output to the immediate window will be (we exited the loop before all items could be printed):

Dog
Cat
Bird
Buffalo

The For…Each is worth learning if you want to use arrays correctly. Its ability to search over all the elements of an array sets it apart from a For…Next Loop.