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.
Advertisements