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

ID: Q90814

The information in this article applies to:

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:

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.

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.

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.

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.

To perform a Do While or Do Until loop in a macro, use a macro similar to the following examples. To create the following examples, open a new macro and click Macro Names on the View menu. Add the following macro names and actions and save this macro group as Do_Loops.

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"

In this example, the Do_Loop1 macro calls the Loop1 macro while the Repeat Expression parameter of the RunMacro action is true.

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"

In this example, the Do_Loop1 macro calls the Loop1 macro once unconditionally, and then continues to call the Loop1 macro while the Repeat Expression parameter of the RunMacro action is True.

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

If the condition is based on the value returned from a Visual Basic or Access Basic function, it will have syntax similar to:


   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          : kbusage McrCond 
Version           : 1.0 1.10 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 11, 1999