Loops – Do Until / Do While / Do

In this blog post we will be discussing the Do..Until/While loop.

The Do…While loop keeps executing the loop as long as a certain condition is met.

Sub doWhile1()
    
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
   
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0
    
    Do While (i < UBound(kitchenItems) + 1)
        'This line of code essentially says:
        ' As long as the value of i is less
        'than 6 execute the next line. Otherwise
        'exit the loop
        
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1
        'We need to increment i or we will be stuck
        'in a loop forever...
    Loop

End Sub
doWhile1
Item 0 is Cooker
Item 1 is Fridge
Item 2 is Cuttlery
Item 3 is Crockery
Item 4 is Dishwasher
Item 5 is Table and Chairs

doWhile2 below performs the same operation as doWhile1 above except it uses Exit Do to exit the loop.

Sub doWhile2()
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0

    Do While (True)
        'Because True evaluates to true (obviously) we have
        'created a never-ending loop. We will need to force
        'an exit if we want to leave
    
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1
        'We need to increment i or we will be stuck
        'in a loop forever...
        
        If i = UBound(kitchenItems) + 1 Then Exit Do
        'This line of code essentially says:
        ' If, at any point, the value of i becomes
        'greater than 6, exit the do loop
    Loop

End Sub

Do Until executes its code block until a certain condition is met.

Sub doUntil()
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0

    Do Until (False)
        'The Do until Loop fires until a condition is met
        'Because False can never evaluate to true (obviously)
        'we have created a never-ending loop. We will need
        'to force an exit if we want to leave
        
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1
        'We need to increment i or we will be stuck
        'in a loop forever...
        
        If i = UBound(kitchenItems) + 1 Then Exit Do
        'This line of code essentially says:
        'If, at any point, the value of i becomes
        'equal to 6, exit the do loop
    Loop

End Sub
doUntil
Item 0 is Cooker
Item 1 is Fridge
Item 2 is Cuttlery
Item 3 is Crockery
Item 4 is Dishwasher
Item 5 is Table and Chairs

Finally, the Do…Loop executes until you force it to stop.

Sub doLoop()
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0

    Do
    'The Do loop just does! There is no condition
    'to evaluate to so we will need to force an exit.
    
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1
        'We need to increment i or we will be stuck
        'in a loop forever...
        
        If i = UBound(kitchenItems) + 1 Then Exit Do
        'This line of code essentially says:
        'If, at any point, the value of i becomes
        'equal to 6, exit the do loop
    Loop

End Sub
doloop
Item 0 is Cooker
Item 1 is Fridge
Item 2 is Cutlery
Item 3 is Crockery
Item 4 is Dishwasher
Item 5 is Table and Chairs

Do…While/Until loops are necessary if you wish to learn to work with the recordset object in MS Access.

Related Posts

DoEvents
Looping Through a Recordset
Loops – For Each
Loops – For Next
Loops – While Wend
Nested Loops
What are Recordsets

One comment

  1. Sir I have a single table called Rank and there are 10 fields which are students in different names. For example Field 1 = John, Field 2 = Michale etc.and all data types are number. Now I want a report which will show the first 05 students as per their marks. Is is possible. Shall be grateful if any assistance is provided. Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube