ACC: How to Create For Next Loops in a Macro

ID: Q90815

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

To perform a For Next 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 either the Repeat Count or Repeat Expression parameters.

To enhance your macro, you can use a different macro structure and include a call to an Access Basic function that maintains a counter, or use the SetValue macro action to maintain and update a counter value in a text box on a hidden form. The benefit of maintaining a counter is that you can use the value of the counter in your macro processing just as in a Access Basic For Next loop.

MORE INFORMATION

To perform a For Next loop in a macro, you would use a macro similar to the following examples. To create the following sample macros, open a new macro and choose Macro Names from the View menu. Add the following macro names and actions, then save this macro group as "For_Next_Loops".

For Next Loop Using Repeat Count


   Macro Name       Action
   -------------------------
   For_Next_Loop1   RunMacro
   Loop1            MsgBox


   For_Next_Loop1 Actions
   ----------------------------------
   RunMacro

      MacroName: For_Next_Loops.Loop1
      Repeat Count: 10


   Loop1 Actions
   -------------------
   MsgBox

      Message: ="Loop"

In this example, the For_Next_Loop1 macro calls the Loop1 macro 10 times. The number of times the macro is called is specified in the Repeat Count parameter of the RunMacro action.

The limitation of this example is that a counter value is not available to indicate which instance of the loop is being executed. As a result, there is no way to tell if this is the first, fifth, or tenth time the loop is being executed.

For Next Loop That Maintains a Counter in a Text Box on a Form


   Macro Name       Action
   -------------------------
   For_Next_Loop2   SetValue
                    RunMacro
   Loop3            MsgBox
                    SetValue

   For_Next_Loop2 Actions
   ----------------------
   SetValue
      Item: Forms![CounterForm]![Counter]
      Expression: 1
   RunMacro
      MacroName: For_Next_Loops.Loop2
      Repeat Expression: Forms![CounterForm]![Counter]<=10

   Loop2 Actions
   -------------------
   MsgBox
      Message: ="Loop Count: " & Forms![CounterForm]![Counter]
   SetValue
      Item: Forms![CounterForm]![Counter]
      Expression: Forms![CounterForm]![Counter]+1

In this example, the For_Next_Loop2 macro uses the SetValue macro action to initialize (set to a value of 1) a counter in a text box called Counter on a form called CounterForm. It then runs the Loop2 macro until the Repeat Expression parameter evaluates to false. In this example, the loop executes 10 times. The Loop2 macro displays a message box that indicates which instance of the loop is being executed by referring to the counter value in the Counter text box. The Loop2 macro then increments the counter (adds 1 to the current counter value) by using the SetValue macro action.

The benefit of this example is that a counter value is available to indicate which instance of the loop is being executed. The drawback to this example is that it depends on a form that must be created and opened prior to the execution of the loop. The form can be hidden or visible, but it must be open for this example to work.

For Next Loop Using Access Basic Function to Maintain a Counter


   Macro Name       Action
   -------------------------
   For_Next_Loop3   RunCode
                    RunMacro
   Loop3            MsgBox
                    RunCode

   For_Next_Loop3 Actions
   --------------------------------------
   RunCode
      Function Name: =LoopCount(1)
   RunMacro
      MacroName: For_Next_Loops.Loop3
      Repeat Expression: LoopCount(3)<=10

   Loop3 Actions
   -----------------------------------------
   MsgBox
      Message: ="Loop Count: " & LoopCount(3)
   RunCode
      Function Name: =LoopCount(2)

In this example, the For_Next_Loop3 macro runs the LoopCount(1) function by using the RunCode macro action. This causes the function to initialize the counter to a value of 1. It then runs the Loop2 macro using the RunMacro action until the Repeat Expression parameter is false. LoopCount(3), which is used in the conditional expression, returns the current value of the counter. Therefore, when the counter reaches 11, the loop has executed 10 times, and when the RunMacro action checks the condition, it evaluates to false and will not call the Loop3 macro again. The Loop3 macro displays a message box that indicates which instance of the loop is being executed by referring to the counter value returned by the LoopCount(3) function call. The Loop3 macro then increments the counter using the RunCode macro action, which calls the function LoopCount(2).

The benefits of this example are that a counter is available that indicates which instance of the loop is being executed and you do not need to create and open a form with a control on it to contain the counter value. The drawback to this example is that you need to create the LoopCount() Access Basic function prior to using this example; however, after you create the function it is available anywhere in Microsoft Access.

The Access Basic function procedure LoopCount() returns the current value of a counter that the function declares and maintains. Depending on which parameter is passed (1, 2, or any other number), LoopCount() will initialize, increment, or return the counter's current value. Below is the Access Basic code for the LoopCount() function procedure.


   Function LoopCount (Action)
      Static LoopCounter             ' Static variable to hold counter
      If Action = 1 Then             ' Initialize counter
         LoopCounter = 0
      ElseIf Action = 2 Then         ' Increment counter
         LoopCounter = LoopCounter + 1
      End If
      LoopCount = LoopCounter        ' Return value
   End Function

REFERENCES

Microsoft Access "Introduction to Programming," version 1.0, chapter 3, pages 6-7 and 10-11

Microsoft Access "Language Reference," version 1.0, pages 246-248, 316-317, 412-414, and 437-438

Keywords : kbusage McrOthr
Version : 1.0 1.10 2.0
Platform : WINDOWS
Issue type : kbhowto