While Wend Loop
By Richard Rost
3 years ago
While Wend Loops in Microsoft Access VBA
In this Microsoft Access tutorial, I'm going to teach you how to use a While Wend Loop in VBA. We'll discuss the pros and cons, and I'll show you how to simulate an Exit While even though this type of loop doesn't support that feature. Then I'll show you how to use a While loop to cycle through all of the records in the Customer table and display everyone's names using a recordset.
Prerequisites
Links
VBA Loops Series
Recommended Courses
Syntax
While Condition
' Your Code Here
Wend
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, While-Wend Loop, while wend loop, while/wend loop, while loop, VBA, exit while, Loop Counter, Conditional Exit, Loop Termination, recordset, loop through customers, exit out of while loop
Intro In this video, we will explore the While-Wend loop in Microsoft Access VBA. I'll talk about the pros and cons of using While-Wend, how to handle loop exits since the classic Exit While is not available, and show you how to use a While loop to cycle through all records in a table using a recordset. I'll cover common pitfalls like endless loops and demonstrate practical examples including how to increment counters and loop through customer records to display their names. This is a developer-level tutorial, so some basic knowledge of VBA is recommended.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, we're going to learn about the While-Wend loop in Access VBA. We'll discuss the pros and cons of the While-Wend loop. I'll show you how to simulate an exit, even though that type of feature doesn't exist with While loops. I'll show you how to use a While loop to cycle through all of the records in the customer table, displaying everyone's names using something called a recordset. All that's covered in today's class.
We are continuing our series on loops in VBA. Yesterday we did the For Next loop. Today, we're going to talk about the While-Wend loop, which is a distant cousin of the Do While and Do Until. We'll talk about those in the next couple of classes.
This is a developer-level class, which means you need to know a little bit of VBA. If you've never done any programming 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 to go watch my variables video, so you know what variables are and how they work. Also, go watch my status box video. This is how I choose to display text for the user in a box on a form called a status box. These are all free videos. They're on my website and my YouTube channel. Go watch them and come on back.
All right, so the While-Wend, which stands for While-End (get it?) - the While-Wend loop, which I'm just going to refer to as a While loop from now on, is real simple. It's while some condition is true, do some stuff until you hit the end of the loop, and it will just keep repeating as long as that condition is true.
For example, here's a simple While loop: we're going to make a variable x starting at zero. While x is less than 10, add one to it and keep looping. So it's going to start 0, 1, 2, 3, and so on until it hits 9. That's the last time it's going to run because it's going to come back and be 10 and then exit out of the loop.
So, that's one of the downsides of a While loop: if you want a counter, you have to deal with it yourself, unlike a For Next loop, where it automatically increments the counter for you.
While loops are also typically used for things that don't require a counter, for example, a recordset. A recordset is where you loop through records in a table.
Here, for example, you'll open a recordset, which is the customerT. It will start at the first record, and while you're not at the end of the file (EOF) or end of the record, just keep looping. As long as you have records, it will keep looping, and it says rs.MoveNext, which is move to the next record, move to the next record, move to the next record. As soon as you hit the end (the EOF), it drops out of the loop.
So that's another use of While loops. I use these all the time for recordsets. If you want to learn more about recordsets, I've got a video on that too. I'll put a link down below in the links section.
Now, While loops pros and cons. Well, pros: it's very simple syntax. As you saw, they're extremely easy to write. They're extremely easy to read. Downside is there's no Exit While option in a While-Wend loop. If you're in the middle of a For Next loop like we looked at yesterday and you want to exit out of that, there's an Exit For. The next two loops we're going to look at, Do While and Do Until, have an Exit While option. But for While-Wend, there's no exit option. Why? Because it's old.
While-Wend goes way back, and in fact, they probably only keep it around for backward compatibility because tons and tons of old Visual Basic was written with While-Wend loops, including mine. So I still use them to this day. But if you want to exit prematurely out of that, there's no exit option. But I'm going to show you how to do it a little bit later on today.
All right, as we saw there's no built-in counters, so if you want a counter variable you have to track and increment it yourself.
Endless loops are very possible with While loops. Be careful because if you get stuck in an endless loop, your Access is going to seem like it froze and it's just sitting there looping and looping and looping.
Here, for example, if you don't have that x = x + 1 in there, or you forget the MoveNext (I do this all the time) - that's why whenever I write this, I always put the MoveNext before I even start my "do stuff," because I always forget the MoveNext, and then it just keeps looping and looping and looping, and Access looks like it's stuck. That's called an endless loop.
Yeah, it's considered obsolete by a lot of programmers, but I don't listen to them. I still use While loops. It's what I learned, it's how my brain works, and they're perfectly fine. I use them in almost all my projects. I teach them in all my classes. The other two loops that we're going to learn tomorrow and the next day are good too, but While-Wend is just fine. It's my preferred While loop.
All right, so here we are in our TechHelp free template. I'm working with the copy that I built yesterday in the For Next loop. So what we're going to do is we're going to change this Run Loop. We're going to change this to For Next loop, and I'm going to change the button over here. Instead of "Hello World" button, this is going to be the For Next button. Now, when I do this, I break the link from the button to the code.
Right click, Build Event. Now, it will bring up your code editor and look what happened: "Hello World" button is still there. When I went into the code I got a blank sub. So what we have to do in this case is just take all this code that's in our For Next button and slide it down there. That's it. That's all you have to do, and we can delete Hello World button because it doesn't exist anymore. Just remember that if you rename any objects, it doesn't rename the Visual Basic code that's associated with it.
That's something - put that on the list. If anyone's keeping track, send that to the Microsoft team. Let them know, hey - they'll rename form objects, they'll rename table fields. If you go in the customer table and change FirstName to FName, and you've got any queries or forms or reports based on that field, those all get renamed, but they don't rename the Visual Basic. Why not? Come on, give us that.
All right, I'm just playing. Anyway, let's go back here. There's our For Next loop and we're going to take this and just slide the ones we're done with over here. Put them over to the right when we're done with you for today.
All right, but we do need another button, so copy, paste, slide you up here. This today is going to be the While-Wend loop, and I made the button too small, didn't I? Okay, so While Wend Loop button. Right click, Build Event.
Now, let's do the same thing we did yesterday with the For loop, except we're going to do it as a While loop. We're basically adding up the digits from one to ten.
So again, Dim x as Long and a total as Long. That's the same. StatusBox equals blank, length the box, all right. Total equals zero, that's the same. Now, instead of For x = 1 to 10, we have to start x off on our own, so x = 1. That's where you want to start it.
While x is less than or equal to 10. Do some stuff and then Wend. The first thing that I do, like I said before, the very first thing I do is put my incrementer in so I don't forget. I don't want to end up with an endless loop.
Right here, x = x + 1. Now in the middle we can do our stuff. The stuff is exactly the same as the stuff we typed in up here. Copy that, drop it down there, and that's it. Save it. Come back out here. Let's close it, open it, click the button, and there we go. There's our total of one to ten, total of 55.
Now, if you look at the code from yesterday, we had this thing in here with the Exit For: if total Mod 5 equals zero then Exit For. Remember, we said if the total is evenly divisible by 5 then exit out, and when we run this one, it hits ten and exits out, okay. But there is no Exit While option, and in fact, let's just test it just to see. Just to prove that I know what I'm talking about. Come right here.
Is there an Exit While? Maybe? No, it goes red. How about Exit Loop? No, that doesn't do it. All right, so we can't do it.
Now, what you can do is use a variable that you'll set to true if you want to exit out of that loop. So what I like to do is I'll make it called Abort as a Boolean. Up top here, where we're initializing stuff, say Abort = False.
Then our While loop is going to be: While x is less than or equal to ten and not Abort. That means we're not aborting, okay, and that's going to start off false. That's going to continue to be false unless, in here somewhere, you say Abort = True.
What I did there: if its mod 5, Abort = True. Save it. Back out here, hit the button, boom, and we get the same stuff. It works the same way. So, while there is no exit option in a While loop, you can very easily add one, and that's what I've been doing since I was a kid. Little Abort option.
All right, you ready for a little extra bonus material? This is the kind of stuff I normally save for an extended cut, but I'm putting it in this video to show you. The extended cuts usually cover extra stuff like this, trying to entice you into signing up to be a member.
I mentioned a recordset earlier. I got a separate video on recordsets, but let's do a recordset loop real quick.
I'm going to slide the While-Wend loop over, put you right over here. Which means I need to make the For Next loop button a little bit bigger.
Let's make another button: copy, paste, slide it up here. Let's call this Recordset While. Recordset Button.
All right, what are we going to use a recordset for? We're going to look at all of the customers in the customer table. We're going to go down each one and list off their first and last names. Put them in the status box.
Ready? This is how it works. We're going to say: Dim rs as Recordset. Set rs = CurrentDb.OpenRecordset("customerT"). You can put an SQL statement in there if you want to open it and sort it a particular way, or limit records, or filter it, or whatever you want to do. Or, you can also base it on a query if you want to build a query out here and then use that for your recordset.
While Not rs.EOF
Do some stuff
rs.MoveNext
Wend
Very important to have that MoveNext there so you avoid the endless loop. When you're all done with the recordset, we say rs.Close to close the recordset. Everything you open, you want to close. Every variable that's an object, like a recordset, is an object. If you set a variable, you want to then set it to Nothing when you're done with it. That's what I like: the setup, that framework, first.
Then we can do our stuff. What's the stuff we're going to do? We're just going to StatusBox = rs!FirstName & " " & rs!LastName. Give me each customer's first and last name. That's it. That's how you loop through all the customers.
You can do stuff in here if you want. You can add up values, you can send them an email, whatever you want to do. That's the beauty of recordsets.
Save it. Always good to throw in a Debug Compile from time to time.
Let's come back over. Let me close this. Open it back up again. Hit the button. Boom. There's all your people. Lots of people.
All right, you like learning this stuff? Is this fun and enjoyable? Well, check out my developer lessons. I got lots and lots on my website.
I cover While loops in detail in my Access Developer Level 12 class, and I cover those recordsets in detail in Access Developer 16. That's a really good lesson. This is a foundational lesson for a lot of stuff that comes after it.
That's it. That's the famous While-Wend loop. That's 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 purpose of the While-Wend loop in VBA?
A. To repeat a set of actions as long as a condition is true
B. To repeat a set of actions a specific number of times
C. To optimize code performance automatically
D. To organize code into modular sections
Q2. Which of the following is a disadvantage of the While-Wend loop compared to the For Next loop?
A. It cannot be used with variables
B. It requires manual control of counters
C. It runs only one iteration by default
D. It automatically increments counters
Q3. What happens if you forget to increment the counter or call MoveNext in a While loop that processes records?
A. The loop will end with an error message
B. The loop will skip every other record
C. The loop will create an endless loop
D. The loop will process no records at all
Q4. Why is the While-Wend loop still kept in VBA, according to the video?
A. Because it is significantly faster than other loops
B. For backward compatibility with older Visual Basic code
C. Because it is the only loop available in VBA
D. Due to its advanced exit options
Q5. How do you prematurely exit from a While-Wend loop, given there is no Exit While statement?
A. Use a GoTo statement to jump outside the loop
B. Use a Boolean variable to control the loop condition
C. Use an Exit For statement
D. Insert a Stop statement in the loop
Q6. When using While loops to loop through records in a recordset, what is a necessary command to include in each iteration to avoid endless loops?
A. rs.Close
B. Set rs = Nothing
C. rs.MoveNext
D. Exit Loop
Q7. Which VBA construct is typically easier to read and write for loops that require only a simple conditional check?
A. For Next loop
B. Select Case
C. While-Wend loop
D. Do Until loop
Q8. In the context of looping through a recordset, what does rs.EOF signify?
A. The recordset is empty
B. The recordset has reached its last record or has no more records to process
C. The first record in the recordset has been processed
D. The data is being filtered
Q9. According to the video, what is the recommended practice after processing a recordset?
A. Leave the recordset open for future use
B. Always set the recordset variable to Nothing without closing
C. Close the recordset and set the variable to Nothing
D. Delete the recordset from the database
Q10. Which one of the following statements about renaming objects in Access is accurate, based on the video?
A. Renaming form objects automatically updates related VBA code
B. Renaming table field names always updates queries and reports, but not VBA code
C. Renaming VBA code modules changes corresponding form names
D. Renaming a button will always update its linked Visual Basic code
Q11. If you want to sum the numbers 1 through 10 using a While loop, what is ONE key thing you need to do in your code?
A. Set the counter outside the loop only
B. Increment the counter within the loop
C. Use Recursion instead of iteration
D. Only define a total variable
Q12. In the While-Wend loop, what does Wend signify?
A. The start of the loop
B. The end of the loop's actions and checks condition again
C. The initialization of the loop variable
D. The increment of a loop counter
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B; 11-B; 12-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 the While-Wend loop in Microsoft Access VBA. This lesson is part of our ongoing series on looping structures in VBA. Previously, we looked at the For Next loop, and soon, we will examine Do While and Do Until loops. Today, though, our attention is on the While-Wend loop, its proper usage, advantages, limitations, and some practical examples, including looping through records in a table using a recordset.
The While-Wend loop, often just called a While loop, works by repeatedly executing a block of code as long as a specified condition remains true. For example, if you set up a variable x starting at zero and run a loop while x is less than ten, you need to increment x yourself during each loop cycle. This is in contrast to the For Next loop, which automatically manages the counter for you. The While loop requires manual control over when and how the loop ends.
While loops are especially useful when you do not rely on a numeric counter. One of the most common situations is iterating through a recordset. With a recordset, you open the table, usually starting at the first record, and continue processing each record as long as you have not reached the end of the file. The loop will continue until all records have been examined, with each loop cycle moving to the next record.
Let me address some important pros and cons of the While loop. Its straightforward syntax makes it very easy to read and write, which is helpful for beginners and experienced developers alike. However, an important drawback is the absence of an Exit While feature. Unlike For Next loops, which have Exit For, and unlike Do While or Do Until loops, which provide Exit Do for premature exits, the While-Wend loop lacks any built-in way to break out of the loop early.
This limitation is due to the age of the While-Wend loop structure, which dates back to early versions of Visual Basic. Microsoft maintains it primarily for backward compatibility, since a great deal of legacy code relies on While-Wend. For those of us who have used the loop for years, it's still a very effective tool despite being considered outdated by modern standards.
If you want an exit condition in a While loop, you can use a Boolean variable, such as Abort. Set Abort to False when initializing, and include it in the loop's condition. When you want to force an early exit, simply set Abort to True inside the loop, and it will break out on the next condition check. I have used this approach since I first began programming.
You also need to remember there is no built-in counter in the While loop, so you must increment any counting variable yourself. Forgetting to do this, or neglecting to move to the next record in a recordset, can easily result in an endless loop, which will make your Access interface appear to freeze. Always be cautious and ensure that the loop will eventually end.
Moving on to practical examples, let's say we want to sum the numbers from one to ten. We would declare two variables for the counter and the total, set the counter's starting value, and structure our While loop to run as long as the counter is less than or equal to ten. Be sure to increment the counter each cycle to avoid infinite looping. Within the loop, we perform our calculation and output the result.
Suppose in the For Next loop example, we included a condition to exit the loop early if the total was divisible by five. There is no direct equivalent in the While loop, but as mentioned, we can use a Boolean variable to achieve the same result.
Let me offer a little extra demonstration that I usually reserve for more advanced lessons. Suppose we want to cycle through all customers in our customer table and display their names. We would create a recordset object for the table, structure our While loop to check if we have not reached the end of the file, perform our actions (such as concatenating and displaying the customer's first and last names), and always remember to move to the next record at the end of the loop. When finished, it's important to close the recordset and clear your object variable, following best practices with object variables in VBA.
This type of recordset loop is limitless in its usage. You can gather information, send emails, build reports, or process anything you need, one record at a time, within the loop.
To summarize, the While-Wend loop is direct, effective, and has its place in VBA programming. While some may consider it obsolete, I find it a valuable tool and use it often. For more detailed training on While loops, look for my Access Developer Level 12 class, and for more about recordsets, check out my Access Developer Level 16 class. Both lessons build the foundation for more advanced projects in Access.
If you are new to programming or unfamiliar with variables and status boxes, I strongly recommend viewing my introductory tutorials, all available for free on my website and YouTube channel.
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 Introduction to While-Wend loops in Access VBA
Pros and cons of While-Wend loops
Simulating an Exit feature in a While-Wend loop
Using a counter variable in While-Wend loops
Avoiding endless loops in While-Wend
Looping through records using recordsets
Displaying names from the customer table with While-Wend
Setting up and closing a recordset in VBA
Concatenating fields in a status box using a loop
Using a Boolean variable to abort a While loop |
