ACC: How to Create Do While and Do Until Loops in a Macro

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

To perform a Do While or Do Until loop in a Microsoft Access macro, you need to use a combination of correct macro structure and the RunMacro action with appropriate information supplied in the Repeat Expression parameter. These requirements are described in more detail later in this article.

MORE INFORMATION

There are several types of Do loops. Each type handles the looping procedure and conditions differently. The different types of Do loops are as follows:

1. Do While <condition>... Loop

   This loop will execute while the condition is true. If the loop is encountered and the condition is already false, the loop will not be performed.

2. Do Until <condition>... Loop

   This loop will execute until the condition is true. If the loop is encountered and the condition is already true, the loop will not be performed.

3. Do... Loop While <condition>

   This loop will execute the first time unconditionally, and then loop while the condition is true. If the loop is encountered and the condition is already false, the loop will be performed once.

4. Do... Loop Until <condition>

   This loop will execute the first time unconditionally, and then loop until the condition is true. If the loop is encountered and the condition is already true, the loop will be performed once.

Do While <Condition>... Loop and Do Until <Condition>... Loop

   Macro Name       Action
   -------------------------
   Do_Loop1         RunMacro
   Loop1            MsgBox

   Do_Loop1 Actions
   --------------------------------------
   RunMacro
      Macro Name: Do_Loops.Loop1
      Repeat Expression: <your_condition>

   Loop1 Actions
   -------------------
   MsgBox
      Message: ="Loop"

Do... Loop While <Condition> and Do... Loop Until <Condition>

   Macro Name       Action
   -------------------------
   Do_Loop1         RunMacro
                    RunMacro
   Loop1            MsgBox

   Do_Loop1 Actions
   --------------------------------------
   RunMacro
      Macro Name: Do_Loops.Loop1
   RunMacro
      Macro Name: Do_Loops.Loop1
      Repeat Expression: <your_condition>

   Loop1 Actions
   -------------------
   MsgBox
      Message: ="Loop"

Conditions

The condition used in the loop, which is supplied in the Repeat Expression parameter of the RunMacro action, can be based on a value in a field on a form, a property of a control on a form, or the value returned from a Visual Basic or Access Basic function. If the condition is based on a field in a form or a property of a control on a form, it will have syntax similar to:

   Forms![CounterForm]![Counter]<=10

   -or-

   Forms![EntryForm]![InvoiceNo].Visible=True

   Time()>=TimeEntry()

Do While Loops vs. Do Until Loops

Whether a loop is a Do While loop or a Do Until loop depends on the condition. The RunMacro action runs the macro until the condition in the Repeat Expression parameter is false. This behavior is exactly what is needed for a Do While loop. It is, however, the opposite of what is needed for a Do Until loop. Therefore, to make a condition for a Do Until loop work correctly, precede the condition in the RunMacro action with the NOT operator.

Keywords          : McrCond kbusage
Version           : 1.0 1.10 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto