You are currently offline, waiting for your internet to reconnect

ACC: How to Create For Next Loops in a Macro

This article was previously published under Q90815
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
To perform a For Next loop in a Microsoft Access macro, you need touse a combination of correct macro structure and the RunMacro actionwith appropriate information supplied in either the Repeat Count orRepeat Expression parameters.

To enhance your macro, you can use a different macro structure andinclude a call to an Access Basic function that maintains acounter, or use the SetValue macro action to maintain and update acounter value in a text box on a hidden form. The benefit ofmaintaining a counter is that you can use the value of the counter inyour macro processing just as in a Access Basic For Next loop.
To perform a For Next loop in a macro, you would use a macro similarto the following examples. To create the following sample macros, opena new macro and choose Macro Names from the View menu. Add thefollowing 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 10times. The number of times the macro is called is specified in theRepeat Count parameter of the RunMacro action.

The limitation of this example is that a counter value is notavailable to indicate which instance of the loop is being executed. Asa result, there is no way to tell if this is the first, fifth, ortenth 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 macroaction to initialize (set to a value of 1) a counter in a text boxcalled Counter on a form called CounterForm. It then runs the Loop2macro until the Repeat Expression parameter evaluates to false. Inthis example, the loop executes 10 times. The Loop2 macro displays amessage box that indicates which instance of the loop is beingexecuted by referring to the counter value in the Counter text box.The Loop2 macro then increments the counter (adds 1 to the currentcounter value) by using the SetValue macro action.

The benefit of this example is that a counter value is available toindicate which instance of the loop is being executed. The drawback tothis example is that it depends on a form that must be created andopened prior to the execution of the loop. The form can be hidden orvisible, 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 functionto initialize the counter to a value of 1. It then runs the Loop2macro using the RunMacro action until the Repeat Expression parameteris false. LoopCount(3), which is used in the conditional expression,returns the current value of the counter. Therefore, when the counterreaches 11, the loop has executed 10 times, and when the RunMacroaction checks the condition, it evaluates to false and will not callthe Loop3 macro again. The Loop3 macro displays a message box thatindicates which instance of the loop is being executed by referring tothe counter value returned by the LoopCount(3) function call. TheLoop3 macro then increments the counter using the RunCode macroaction, which calls the function LoopCount(2).

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

The Access Basic function procedure LoopCount() returns thecurrent 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'scurrent 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				
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

Article ID: 90815 - Last Review: 05/09/2003 18:29:42 - Revision: 2.0

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • kbhowto kbusage KB90815