Microsoft Access VBA Macro Programming

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

TheDo Untilloop 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 variablexis set to the value 0. The condition of x = 100 is then supplied as the

criterion for when theDoloop should stop. The variable (x) is then incremented by 1 each time

throughthe loop, so it loops 100 times until x = 100. At this point, it displays a message box

giving the value ofxthat is 100.

While..Wend Loops

Finally, there is the While..Wend loop. This continues to loop while a specified condition is

true. It stops as soon as the condition is false. The following is a simple example that is very

similar to the previous Do Until loop:

Sub test_do()

x = 0

While x < 50

x = x + 1

Wend

MsgBox x

End Sub

42 Microsoft Access 2010 VBA Macro Programming