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

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.


In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!

1 Comment

  • SATYABAN UKIL says:

    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 *