ACC: How to Create For Next Loops in a Macro

• Microsoft Access versions 1.0, 1.1, and 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"

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

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)

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