Microsoft KB Archive/90815

= ACC: How to Create For Next Loops in a Macro =

Article ID: 90815

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q90815



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 &quot;For_Next_Loops&quot;.

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: =&quot;Loop&quot;

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: =&quot;Loop Count: &quot; & 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: =&quot;Loop Count: &quot; & 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

