totn Access Functions

MS Access: WHILE...WEND Statement

This MSAccess tutorial explains how to use the Access WHILE...WEND statement to create a WHILE loop in VBA with syntax and examples.

Description

The Microsoft Access WHILE...WEND statement is used to create a WHILE loop in VBA. You use a WHILE loop when you are not sure how many times you want to execute the VBA code within the loop body. With a WHILE loop, the loop body may not execute even once.

Syntax

The syntax to create a WHILE loop using the WHILE...WEND statement in Microsoft Access is:

While condition
   {...statements...}
Wend

Parameters or Arguments

condition
The condition is tested each pass through the loop. If condition evaluates to TRUE, the loop body is executed. If condition evaluates to FALSE, the loop is terminated.
statements
The statements of code to execute each pass through the loop.

Returns

The WHILE...WEND statement creates a WHILE loop in VBA.

Applies To

The WHILE...WEND statement can be used in the following versions of Microsoft Access:

  • Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

Example in VBA Code

The WHILE...WEND statement can only be used in VBA code in Microsoft Access.

Let's look at how to create a WHILE loop in Microsoft Access.

WHILE Loop

For example:

Sub While_Loop_Example

   Dim LCounter As Integer

   LCounter = 1

   While LCounter < 10
      MsgBox (LCounter)
      LCounter = LCounter + 1
   Wend

End Sub

In this example, the WHILE loop is controlled by the condition While LCounter < 10. This condition is tested each pass through the loop. If the condition is TRUE, then the VBA code would display a message box with the value of the LCounter variable. If the condition is FALSE, then the loop is terminated. This code would display 9 message boxes with the following values: 1, 2, 3, 4, 5, 6, 7, 8, and 9. Once LCounter reaches 10, it will terminate the loop.

Double WHILE Loop

You can nest WHILE loops in VBA. This allows you to have a double loop with 2 different conditions that will be evaluated.

For example:

Sub Double_While_Loop_Example

   Dim LCounter1 As Integer
   Dim LCounter2 As Integer

   LCounter1 = 1
   LCounter2 = 6

   While LCounter1 < 5
      While LCounter2 < 9
         MsgBox (LCounter1 & "-" & LCounter2)
         LCounter2 = LCounter2 + 1
      Wend
      LCounter2 = 6
      LCounter1 = LCounter1 + 1
   Wend

End Sub

Here we have 2 WHILE loops. The outer WHILE loop is controlled by the LCounter1 variable. The inner WHILE loop is controlled by the LCounter2 variable.

In this example, the outer WHILE loop would loop 4 times (starting at 1 and ending at 4) and the inner WHILE loop would loop 3 times (starting at 6 and ending at 8). Within the inner loop, the code would display a message box each time with the value of the LCounter1-LCounter2. So in this example, 12 message boxes would be displayed with the following values: 1-6, 1-7, 1-8, 2-6, 2-7, 2-8, 3-6, 3-7, 3-8, 4-6, 4-7, and 4-8.

You will notice that as we exit the inner WHILE loop, we reset the counter for the inner WHILE loop back to 6 with the statement LCounter2 = 6. This is to ensure that the inner WHILE loop will execute again, as LCounter2 would equal 9 after exiting the inner WHILE loop the first time. If we left LCounter2 at a value of 9, he condition for the inner WHILE loop code would evaluate to FALSE and terminate the loop every subsequent time.