For Each Loops

The For Each loop is very similar to a For..Next loop, but it is specifically for use on collections or arrays. For Each allows you to step through each item within the collection or array. You do not use an index (such as n in the previous example) because it automatically moves through each item within the collection. This is very useful if you need to search through a collection for a certain object and then delete it because the position in the collection after deletion is maintained in your loop. If you use a For..Next loop with an index and delete the object, the index will be moved up one and your routine will go through one loop too many, causing an error message.

The following example iterates through all the query names in the current database using a For Each loop:

Sub ShowName()
	Dim oQry As QueryDef
	For Each oQry In CurrentDb.QueryDefs
		MsgBox oQry.Name
	Next oQry
End Sub

Do Until Loops

The Do Until loop keeps looping until a specified condition is met. Often this means waiting for a variable to contain a particular value. When the condition is met, the loop stops, and the program continues executing on the next instruction after the loop. You can also use a While statement so that while a certain condition is met, the code will carry on looping. Here is a simple example:

Sub test_do()
x = 0
Do Until x = 100
    x = x + 1
Loop
MsgBox x
End Sub

First a variable x is set to the value 0. The condition of x = 100 is then supplied as the criterion for when the Do loop should stop. The variable (x) is then incremented by 1 each time through the loop, so it loops 100 times until x = 100. At this point, it displays a message box giving the value of x that is 100.