Select the product you need help with
ACC: How to Create For Next Loops in a MacroArticle ID: 90815 - View products that this article applies to. This article was previously published under Q90815 On This PageSUMMARY
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 PropertiesArticle ID: 90815 - Last Review: May 9, 2003 - Revision: 2.0 APPLIES TO
|
Back to the top
