HomeAccess Blog › While…Wend

While…Wend

In this blog post we take a look at the While…Wend Loop.

A While loop executes its code blocks over and over until its expression is not True.  In the example below the user is asked to input some text every iteration of the while loop. If the box is dismissed without entering any text, the loop ends.

Sub whileLoop2()
  Dim str As String
  str = InputBox("Enter some text")
  While (str <> "")
    Debug.Print "You wrote: " & str
    str = InputBox("Enter some text")
  Wend
End Sub
whileLoop2
You wrote: test 1
You wrote: test 2!
You wrote: test 3?

While (false)

The While statement only executes its code block if the expression in parenthesis is equal to True.

Sub whileLoop3()
  While (False)
    MsgBox "I was not called!"
  Wend
End Sub
whileLoop3
‘ nothing is executed!

While is often used to cycle through Recordsets and Files.  We can use each object’s EOF (End-Of-File) property as the expression to the While statement.

Sub whileLoop4()
  Dim rs As DAO.Recordset
  rs = getRecordSet() ' this method is for illustrative purposes only

  While (Not rs.EOF)
    Debug.Print rs!FieldName
    rs.MoveNext ' object rs told to move to the next row 
  Wend

End Sub
whileLoop4 ‘getRecordSet() is for illustrative purposes only
{FieldName of each row in Recordset}

EOF is set to True when the Recordset object reaches the last element and attempts move forward once more, so this loop cycles over the Recordset object and prints the value of field FileName.

Note

The above code is for demonstration purposes and will not work unless a recordset is defined correctly.

Exit While

To exit a while loop isn’t as trivial a task as with other loop structures.  To exit a While one must force the While expression to be false.

Sub whileLoop5()
  Dim rs As DAO.Recordset, exitMe As Integer
  Set rs = CurrentDb.OpenRecordset("SELECT * from tblStudents")

  While (Not rs.EOF And exitMe <> 5)
    Debug.Print exitMe; rs!LastName
    rs.MoveNext ' object rs told to move to the next row
    exitMe = exitMe + 1
  Wend

End Sub
whileloop5
0 Bedecs
1 Gratacos Solsona
2 Axen
3 Lee
4 O’Donnell

The variable exitMe is incremented by 1 over each loop and forces the expression in the While to be false after the 5th iteration.

 

While…Wend Loops are very useful when used in conjunction with recordsets.

Leave a Comment