Do Until Loop
By Richard Rost
3 years ago
Do Until Loops in Microsoft Access VBA
In this Microsoft Access tutorial, I'm going to teach you how to use a Do Until Loop in VBA. We'll discuss the pros and cons. We'll see how you can test for a condition at either the top or the bottom of the loop. We'll learn about the Exit Do command. We'll discuss how Do Until is very similar to yet different from Do While.
Members
Members will learn how to perform goal seeking for a savings account balance using a Do Until loop. They will specify a starting balance and an end goal balance, as well as the interest rate. Then we'll use a loop to calculate the compounding monthly interest until our goal balance is met.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
VBA Loops Series
Recommended Courses
Syntax
Do Until Condition
' Your Code Here
[If OtherCondition Then Exit Do]
Loop
Do
' Your Code Here
[If OtherCondition Then Exit Do]
Loop Until Condition
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Do-until Loop, do until loop, do/until loop, do until loop, VBA, exit do, Loop Counter, Conditional Exit, Loop Termination, exit out of do until loop, goal seek, monthly compounded interest, compounding interest
Subscribe to Do Until Loop
Get notifications when this page is updated
Intro In this video, we'll talk about how to use the Do Until loop in Microsoft Access with VBA. You'll see the differences between Do While and Do Until loops, learn about evaluating loop conditions at the top or bottom of your code, how to use the Exit Do command, and tips for handling user input to avoid errors. I'll demonstrate updating a database example and give you a clear understanding of how and why you might choose Do Until over other loop types in your Access projects.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we are continuing our loop series. We're going to look at the do-until loops in Microsoft Access.
So far in our series we've looked at for-next, while-wend, and the do-while loops. Today we're going to talk about do-until.
Of course, this is a developer series, which means we're going to be needing some VBA. If you've never programmed in VBA before, go watch my Intro to VBA video. It's about 20 minutes long and teaches you everything you need to know to get started.
Also, make sure you understand how variables work. I have a video for that. Go watch my Status Box video as well. This is how I display information for the user using a status box, which is a text box on a form.
Go watch the previous three videos in the loops series, because I'm going to be continuing on with those examples. You'll find links to these down below in the description under the video window.
These are all free videos. They're on my website, they're on my YouTube channel. Go watch those and then come on back. I'll wait for you.
All right, so yesterday we looked at the do-while loop. A do-while loop basically reads: do this while some condition exists. Do-until is basically the opposite of that: it says do this until some condition exists.
Here, the condition already exists and you're going to keep running the loop as long as that condition still is in effect. With do-until, the condition doesn't exist and you're going to keep looping until it does.
It's a subtle difference, but depending on which one you use it really changes the readability of your code. Let me show you some examples.
They're set up the same: do while this condition exists, loop. This one is do-until some condition exists, and then you're going to loop.
With a while loop, the user enters in some number. Let's say five. While that number is less than 10, we're going to add one to it and eventually it's going to equal 10 and drop out.
It's the same exact loop just written differently. The user enters a five. Do until X is greater than or equal to 10. Make sure you get the greater than or equals over here if this is less than. OK, so it's going to loop until X is greater than or equal to 10, and when it hits 10, it's going to drop out. Same exact logic, just written differently.
Of course, you can evaluate a do-until at the bottom or the top of the loop just like with a do-while.
Here's another example: do loop while X is less than 10, or do loop until X is greater than or equal to 10. It's all a matter of how you want to write it.
Just like the do-while loop, the do-until also has the exit do command. If in the middle of the loop you decide you want to get out, just say exit do. Do until X is greater than or equal to 10, and then right in here, if it's divisible by five, exit out of the loop.
Pros and cons are pretty much exactly the same as do-while. It's the same loop, it just reads differently, that's all.
Let's see what this looks like in our VBA code.
Continuing on here with our loop database. By the way, not sure if I mentioned this in the previous video, but gold members, I am going to make this database available at the end of the series. It will be on the summary page.
Let's go in here and copy the do-while loop button we made in the last class. We'll slide that guy right up there. We'll change this to the do-until loop. Change the name of the button. What did we call you? doWhileButton? Let's make you the doUntilButton. doUntilBTN.
Let's save it. I'm going to right-click, Build Event, that's going to bring up my code editor.
All right, here's the do-until button click. I'm just going to copy the do-while button click, just copy and paste, and there you go.
In the last video we made the user type in a value into UserData, and if you look, this starts off blank. What I'm going to do is put a default value in here of zero so it starts at something. Otherwise, we're going to get an error message if we just run the code. More importantly, if they don't have a value in there, if they clear that value, let's give them an error message.
Let's come up here and we'll say:
If IsNull(UserData) Then
Status = "Missing user data": Exit Sub
That's a little trick you can do with this little colon if you want to put two commands on the same line. Usually I only use this with select case statements, but once in a while I'll do that. It prevents you from having to go: then Enter status = "Missing user data" Enter Exit Sub Enter End If. This way, you can put it all on the same line.
While we're at it, let's copy this down here to the while button as well so they both can use it.
Now this guy says do while X is less than or equal to 10. That means I want this to run as long as the value is less than or equal to 10. To flip this to an until, it's going to be: do until X is greater than 10, and it'll do the same thing. I'm going to remark out the mod line for now.
Let's save it, come back over here. I'm going to close my form, open it back up again, hit the button, and there we go. Same loop, just written differently. It's all about how you read it. Do something while I have something going on, or do something until something happens.
The logic is really more for the programmer than the computer, because you can phrase it either way with a while or an until. Want to put a value in here? Put a three in there, run it, same thing, just starts at three.
Want to flip this guy on its head? Do... cut, paste, loop until X is greater than 10, and you get the same results.
Remember, in the last class, in the do-while class, we talked about the differences. This way, this loop is guaranteed to run at least once, no matter what the user data is.
If I come in here and put in 15, it's going to run at least one time.
Whereas the other way, if I say this: do until X is greater than 10, now if I put a 15 in there and run it, nothing happens.
That's the difference between whether you check at the top or the bottom.
That's pretty much it. That's basically the do-until loop. Pretty straightforward, almost exactly the same as a do-while.
Want to learn some more and have some more fun? I'm going to throw an extended cut in today. We haven't done an extended cut with the loop series yet. What we're going to do is use a do-until loop to goal seek our savings account.
I know there are financial functions you can use, like in Excel, to do this, but we're going to do it with a loop in VBA. I'll teach you the old school method for calculating this. We'll have a starting balance, a goal amount, and an interest rate, and we'll loop by adding the interest every month and figure out how many months it's going to take to get to our goal amount.
That will be in the extended cut for the members. Remember, Silver members and up get access to all of my extended cut videos, not just this one, all of them. Gold members can download the TechHelp databases.
Come on back tomorrow. We'll talk about the for each-next loop. That's a really cool loop. You can loop through arrays, you can loop through collections of items, like all the controls on a form, that kind of stuff.
If you enjoy learning with me and you want to learn more, I have tons of developer lessons on my website. I have 44 of them as of right now, and I add a new one every month or two. I'll put a link down below. You can click on that and come to my website and check out all my classes.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main conceptual difference between a do-while loop and a do-until loop in VBA?
A. Do-while keeps looping while a condition does not exist; do-until loops while the condition exists.
B. Do-while loops run indefinitely without a break; do-until always executes once.
C. Do-while loops while a condition exists; do-until loops until a condition exists.
D. There is no difference; they function identically in all scenarios.
Q2. Which of the following is true regarding the placement of the loop condition in do-until and do-while loops in VBA?
A. The condition must always be evaluated at the top of the loop.
B. You can only check conditions at the bottom with do-while loops.
C. Both loop types allow checking the condition at either the top or bottom.
D. Only do-until loops support condition checking at both ends.
Q3. What VBA command is used to exit a loop early within a do-until or do-while loop?
A. Exit Sub
B. Goto End
C. Break Loop
D. Exit Do
Q4. Why might a programmer choose to use a do-until loop instead of a do-while loop for the same logic?
A. Do-until loops run faster.
B. It can improve code readability depending on how the condition is phrased.
C. Do-until loops consume less memory.
D. Do-until loops are required for all variable-based conditions.
Q5. In VBA, what happens if you place the loop's condition at the bottom (after 'Loop Until') and the initial condition is already true?
A. The loop is skipped entirely.
B. The loop runs once before checking the condition.
C. The loop runs twice before stopping.
D. The loop runs endlessly regardless of the condition.
Q6. What is the effect of initializing a default value (such as 0) in a user input textbox before running a do-until loop?
A. It forces the loop to run at least twice.
B. It guarantees the loop never exits.
C. It prevents errors due to missing or null input data.
D. It has no effect on how the loop operates.
Q7. According to the video, which situation can result in a more readable version of loop logic for the programmer?
A. When the loop is always guaranteed to run twice
B. By using do-until when waiting for a condition to become true
C. By using while-wend for iterative processes
D. By avoiding the use of variables entirely
Q8. What is a practical example of using a do-until loop given in the extended cut for members?
A. Searching for a value in a string
B. Looping through all controls on a form
C. Calculating how many months are needed to reach a savings goal with interest
D. Sorting an array alphabetically
Q9. If you use do until X is greater than 10 and the starting value of X is 15, what will the loop do when the condition is checked at the top?
A. Loop forever because the condition will never be met
B. Run at least once, regardless of X's initial value
C. Not run at all because the condition is already met
D. Decrement X until it reaches 10
Q10. What is the purpose of the Status Box as used in the examples in the video?
A. To display database table names
B. To show output or messages to the user during code execution
C. To store user credentials securely
D. To protect VBA code from being edited
Answers: 1-C; 2-C; 3-D; 4-B; 5-B; 6-C; 7-B; 8-C; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on understanding do-until loops in Microsoft Access using VBA. This lesson is a continuation of our series on loops, where we have previously explored for-next, while-wend, and do-while loops. Today, we shift our attention to the do-until loop, an important construct for controlling program flow in your Access applications.
If you are new to programming in VBA, I recommend watching my Intro to VBA tutorial first. It's about 20 minutes long and will give you a solid foundation to begin with. You should also be comfortable working with variables, so be sure to check out my video on that topic as well. Additionally, I have a Status Box video that shows how I present information to users by displaying it in a text box on a form. It helps make your applications more user-friendly. If you have not seen the previous videos in this loop series, I suggest going through those first, as I'll be building on the examples from earlier lessons.
Let's revisit some differences between do-while and do-until loops. The do-while loop will continue executing its statements as long as a given condition is true. In contrast, the do-until loop keeps executing statements until the specified condition becomes true. So, do-while keeps looping while the condition holds, and do-until keeps looping until the condition is met.
This may seem like a minor difference, but it can affect how readable your code is to other programmers or even to yourself in the future. For example, with a while loop, if a user inputs a value such as five, the loop might add one until the number reaches ten. With a do-until loop, you simply rephrase the condition so that the loop continues until your value is ten or higher. Both loops accomplish the same goal, but the way you word the condition can make your intent clearer.
Both the do-while and do-until loops give you the flexibility to place the condition check either at the top or bottom of the loop. This control allows you to determine whether the loop always executes at least once, or only if the condition is initially met.
Using the exit do command is also supported in do-until loops. If you want to break out of the loop in the middle, you can use exit do. For example, you might exit the loop early if a certain value is divisible by five. This is useful when you want to allow for exceptions within your loop logic.
Now, let's look at how this works in practice. In the sample database for this series, we have a button set up for the do-while loop from the last lesson. I recommend duplicating this button and renaming it as the do-until button. Make sure you update its event procedure to reflect the new do-until logic.
One thing I recommend is to assign a default value to the user input field, such as zero. This prevents errors from occurring if the field is left blank. If the field is empty, you should also show a clear error message letting the user know that input is required. You can handle this by checking for null values in the input field. By combining these small improvements, your code becomes more robust and user-friendly.
With the do-until loop implemented, you may see code written as "do until X is greater than 10." This will perform identically to the while loop, just stated differently. Test it by entering values into your user data field and running both the while and until versions to see that they work the same way. You can even move the condition check to the bottom of the loop, which ensures the loop runs at least once irrespective of user input. This change is useful if you want certain code to execute no matter what, and only break when the condition is finally met.
In summary, the do-until loop offers a flexible and intuitive way to execute repetitive tasks in Access VBA until a certain criteria is met. It is almost functionally identical to a do-while loop, with the main difference being how you structure your condition, which can improve code readability.
Also, with today's lesson I am including an extended cut. In this extended cut, I will demonstrate how to use a do-until loop to perform a goal-seek operation on a savings account. Even though there are financial functions for this type of computation, using a loop in VBA is useful for learning the step-by-step logic. We will set a starting balance, a goal amount, and an interest rate. Each month, interest will be added, and we will calculate how many months it will take to reach the savings goal. This practical example is covered in detail for the members in the extended cut. Silver members and higher get access to all extended cut videos, and Gold members can download the supporting TechHelp databases.
Be sure to come back for the next lesson, where I will be covering the for each-next loop. This is a powerful way to loop through arrays or collections, such as every control on a form.
If you are enjoying these lessons and would like to explore further, I have many in-depth developer courses available on my website covering a wide range of topics in Access development.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Explaining the difference between do-while and do-until loops
Using do-until loops in VBA
Setting up a do-until loop with user input
Comparison of loop condition placement (top vs bottom)
Adding a default value to a text box for user input
Handling missing user data with error messages
Using the exit do command within a do-until loop
Copying and modifying a button to create a do-until loop event
Changing loop types by editing loop syntax
Ensuring a loop runs at least once using loop structure
Differences in loop execution based on condition position
Practical examples of do-until loops in Microsoft Access VBA |
