Abort Loop
By Richard Rost
18 months ago
Add Abort Checkbox to Exit Loop in MS Access VBA
In this Microsoft Access tutorial, I will show you how to add an abort checkbox to a loop in VBA, allowing you to gracefully exit without shutting down Access. We'll cover setting up the checkbox, checking its status inside different types of loops, and making sure the interface responds properly during the process.
David from Germantown, Tennessee (a Platinum Member) asks: I've got a loop that [does stuff], and it takes a good 20 to 30 minutes to run. Sometimes I decide in the middle of that loop that I want to abort it. How can I do this without shutting down Access
Members
There is no extended cut, but here is the database download:
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
Links
Recommended Courses
Keywords
TechHelp Access, abort loop, graceful exit, VBA loop abort, Access VBA checkbox, abort button Access, stop long running loop, VBA abort process, exit loop gracefully, stop button in Access, Access VBA error handling, terminate loop safely, VBA do events, Access VBA exit for, interrupt VBA loop
Subscribe to Abort Loop
Get notifications when this page is updated
Transcript
Today, I'm going to show you how to abort out of a loop gracefully instead of just killing the process or shutting down Access when you're working with your AccessVBA loops. Today's question is very similar to yesterday's question. I like to group these things together when I get a bunch of questions in my queue list. I try to find the ones that work well together.
David from Germantown, Tennessee, one of my platinum members, says, "I've got a loop that does stuff and it takes a good 20 to 30 minutes to run. Sometimes I decide in the middle of that loop that I want to abort it. How can I do this without shutting down Access?" Well, David, let's talk about it. But for everybody else, I want you to go watch yesterday's video, which is about speeding up loops, because we're going to use the same database, the same loop that we built in that database for today. Watch all the prerequisites for this one so you understand, you know, VBA, my status box, what else did we do, a for next loop? Go watch this video and its prerequisites first and come on back.
Alright, here's the database we built yesterday. We got this nifty hello world thing running, right? And let's say this takes a bunch of time. Again, let's say you're using the database to send emails, and you realize you've got, oops, right in the middle of it, 50 of them went out, and you've got 5,000 to go, but you've got to make a quick change. So you want to put a little button here or a box you can click on to say, abort, stop, you know, danger Will Robinson, cut it out. So how do you do that?
Well, if it's you, if you didn't program in an abort and it's not your end user, you could open up the VBA window, right, and hit the stop button right here, the reset button. That will stop whatever process is running, including your loop. Alright, so if you happen to be in here, design view, alright, let's intentionally slow this thing back down again. Let me put this to 10. Okay, now, you've got to have this thing accessible. It's hard to sometimes get to this if something else is running. But if the window is open, alright, and you're running it, let's go, alright. I mean you could try control break, you could try a couple of other things, but nothing really is going to work unless you come over here to the VBA window and hit stop. That should stop the process from running. See, it just stopped it. Alright, but I don't like doing that. That's not good to do. Your end user can't do that. So we want to give them something nice and easy that they can do.
So let's go to Design View here. And let's change this to our Start button. Not the Start button, our Start button. Alright, let's drop a checkbox right here. Where is it? Checkbox looks like that. Drop it right there. I'm going to change the label to say abort and let's make it so we can actually read it. White and let's change the name of the box itself to abort. Alright, and you can set its default value to no if you want. Alright, save it close it open it back up again. Alright, we got our abort box right there. Okay, so let's go back into our button code and here's where our hello world runs right down here. First thing we're going to do is say abort equals false when we start the loop. That's because, you know, if you had canceled it on a previous run and you click the button you don't want it to immediately abort. So we're going to set this to false. Now somewhere inside your loop you're going to check to see if the user clicked that box.
Okay with a for loop you can say it like this if abort then exit for like that that's how you exit a for loop. If you're using a while loop or a different kind of loop, you just build it into the thing here. Like, for example, here I'll just show you. If you say like x equals 0, and then while x is less than or equal to 100,000, and not abort, you do it like that. And then your while loop will exit out if abort is true, okay? It all depends on the kind of loop that you're using. This works for a for loop. Now, the important thing here is you have to make sure you include a do events inside your loop somewhere. Now, do events is one of those things like writing to the screen that takes a lot of processing cycles, alright? It basically tells Access, hey, you can run background processes now. You can accept user input now. Normally it doesn't unless you do events. The reason we didn't need it before was because my status sub, my status function, whatever you want to call it, has a do events in it. So you can see the screen being updated. OK?
So I did not need to use do events in yesterday's video, but you normally would need it. And I'm going to keep it in here so that it's not waiting for a do events every, you know, every iteration. It's going to only check it when it writes to the screen. Okay, which is usually good enough for your abort loop. Okay, now after the loop is exited, I'm going to check to see if the user aborted or not, because it's not done. So if abort, then status aborted. Right? You could do other things in here, like change the color, or whatever you want to do. Otherwise, right, it finished successfully. And then you can do all of this stuff. Right? Get rid of that blank line, and then we'll put the beep at the very end. So regardless of whether they aborted or finished, it's still going to beep. Okay.
Let's give it a shot. Debug, compile, come out here, hit the button. It's running nice and slow. And oh, I got a problem. I got to abort. Done. Okay, start it up again. Alright, abort. Bing. That's how you do a little abort box. There's a lot of embellishments you can add to this, folks. You want to hide the abort box if it's not in the middle of the loop? Well, just set its visible property to no by default, okay? Then when this starts, abort.visible equals true. If it's got a bound label, the label is stuck to the box, it'll also control the label too. And then when you're all done, we're gonna say abort.visible equals false. Show it during the loop and then when it's done, hide it again. Save it, debug, compile, come back out, meow, and let's give it a go. Notice how I didn't see it. Now I'll click on it oh oh this is important that's why I'm leaving it in the video it's important you can't hide a control that is focus when you click on the abort box it's got the focus so you can't hide it so you gotta shift the focus somewhere else all right where do you want to put it all right I'll end that let's just put it back on our hello world button right this guy right there that's the name of the control hello world button so before Hello World button. So before we hide it, we'll say Hello World button dot set focus. Just like that. Alright, save it, debug, compile. So that's a runtime error. That's an error that the compiler won't catch if you debug, compile. You won't discover that until you run the code. Ready, go. And go. See? And the focus is sitting back here. That's a big one that comes up a lot. Want to make sure that the user is sure they want to abort? Throw a message box in here. Right? If abort then do some stuff right? And if I want to say right here if message box are you sure you want to abort? Right? Comma VBS no No, cancel, plus VB default button two, so the default is no, slide over so you can see this whole thing, right, comma, title is going to be abort, and if this is anything other than VBS, or I'm sorry, if this equals VBS, if their answer is VBS, then we're going to exit for, and if, otherwise it will continue to loop. If they say nowhere canceled, it will continue to loop. If you want to learn more about the message box, go watch my message box video and I got a video on those different message box options too. Go watch those. I have videos on everything. And if you find a topic about Microsoft Access that I don't have a video for, I want to know about it. If you do a Google search and one of my videos doesn't show up, I want to know, all right, exactly what that search was. I'll make a video about it. That's my goal is to show up on every search, anything access-related. But this can be important, especially if you're doing something like sending out an email batch or whatever and you want to make sure that the user is sure that they abort it so you don't have to start it over again. Start, abort. Are you sure you want to abort? No, I'm not sure. Keep going. Oh, look at that. Oh, that's a bug. That's a bug and I'm going to leave this in the video. What's happening? It's checking every loop now because we didn't reset abort. That's important. Now how do I get out of this? I'm seemingly stuck. Alright, yeah, you might have to... Let's see if I can get out of here by hitting escape. I'm running into a situation now where I can't gracefully exit this. I put myself in a locked loop. In this particular case, now normally you could kill access but if I just say yes it's going to exit out of that loop. Right? But make sure you put in here, right, are you sure you want to exit? If they say yes, exit for otherwise abort equals false. Uncheck that box. All right, now let's give it a try. Ready, go, and abort. Are you sure? No. And it should resume. Abort. Oh, cancel. All right. Abort. Are you sure? And yes. Alright. The reason I leave these mistakes in the video, folks, is because you're going to make the same mistakes I did. Now the tricky part, if you're doing something like an email batch, the tricky part is if you abort it because you want to make a change. Let's say you're sending out 10,000 emails and you discovered after the first couple hundred that there's a problem. Well you already sent it out to a couple hundred people so you don't want to resend it to them again. So the tricky part is resuming the loop later. Right after you've made your change now you want to restart the loop but you want to pick it up at whatever record it canceled it before. If you want to see how to do that post a comment down below. Say yes I want to see how to resume an aborted loop. I know what you're talking about. I post a comment if you want to see that I'll do it in a future video.
One last thing that I'll let you go let's let's change the colors right if it's when it starts we'll make it yellow if it finishes successfully we'll make it green and if it's a border we'll make it red. So how about up here we'll say we'll say status box dot back color equals DB yellow that's one way to do it. There's db yellow, db green, db red, blah, blah, blah. Or you can use the RGB, which is for more subtle colors. So let's say if it's aborted, we'll say status box.backcolor equals RGB. Let's go 255, 200, 200. That should be a light red. I always put after that light red. RGB. Higher this number from 0 to 255. And if this was 00, it would be a really deep red. And if it finished successfully, we'll make it a light green. So we'll put that here. I want you to say RG is at 255 this time. And then 200. And then light green. OK. Alright. Let's save it. And we'll come back out now and run it. It's yellow because it's running. Let me abort it. Are you sure you want to abort? Yup, okay, it's red. You can make it a deeper red if you want. I just didn't make those other values lower. And if it's successful, I don't want to send you through the whole thing, so let's do this again. Let's crank up that. Let's pump up the volume. Ready, go and green, done. See? All kinds of options, all kinds of stuff you can do like this folks. And I'm just just scratching the surface, trust me. If you like this stuff, if you like my videos, if you like learning with me, I got tons and tons of developer lessons on my website. I think I'm up to developer 45. There's dozens and dozens of hours of stuff where I teach you cool tips and tricks like this and how to build databases the right way.
So check those out or consider becoming a member of my channel and you can get all my extended cut videos and learn all kinds of new fancy stuff that way too. But that's going to do it. That's your TechHelp video for today. I hope you learned something folks. Live long and prosper. I'll see you next time.
TOPICS:
Creating a database to abort loops
Adding an abort button to a form
Changing the textbox label and value
Conditionally stopping a loop
Implementing a checkbox to control loops
Using 'Do Events' in VBA
Checking loop status with a status box
Ensuring user confirmation before aborting
Color-coding status indicators
Fixing UI focus issues during abort
Handling runtime errors
Resuming aborted loops
RGB color customization for status boxes
COMMERCIAL:
In today's video from Access Learning Zone, I will show you how to gracefully abort out of a loop in Access VBA without shutting down Access. We start by using the database and loop we built in the previous video. Discover how to add an "abort" checkbox, integrate it into your loop, and ensure your VBA code can handle user input to stop the process smoothly. Learn practical steps like adding DoEvents, setting focus, and managing user confirmation with message boxes. This tutorial is perfect for those lengthy loops that might need a quick stop, like sending batches of emails. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main topic of today's video tutorial?
A. Speeding up loops in AccessVBA.
B. Aborting out of a loop gracefully in AccessVBA.
C. Building a Hello World program in VBA.
D. Sending emails using AccessVBA.
Q2. What does the 'DoEvents' command do in VBA?
A. Speeds up the execution of a loop.
B. Halts the execution of a loop until user input.
C. Allows Access to run background processes and accept user input.
D. Logs the status of the loop to a file.
Q3. Why should you initially set the 'abort' variable to false at the start of the loop?
A. To ensure that the loop runs faster.
B. To prevent the loop from aborting immediately if 'abort' was previously set to true.
C. To display a message box at the start of the loop.
D. To hide the abort checkbox.
Q4. What should you include inside your loop to check if a user has clicked the abort checkbox?
A. A timeout function.
B. A message box prompt.
C. An 'If exit' statement.
D. An 'If abort then exit for' statement.
Q5. How do you handle the scenario where the abort checkbox has the input focus when trying to hide it?
A. Change the checkbox label to hidden.
B. Set the focus to another control before hiding the checkbox.
C. Disable the checkbox control.
D. Reset the form.
Q6. What should you add to your code to confirm with the user before aborting the loop?
A. A form reload command.
B. A status update message.
C. A message box asking for confirmation.
D. An error handler.
Q7. What should be done if the user decides not to abort and continue with the loop?
A. The loop should be restarted from the beginning.
B. The abort checkbox should be reset to false.
C. The VBA editor should be opened.
D. The database should be closed and reopened.
Q8. How can the user be visually informed about the status of the loop, such as when it is running, aborted, or finished successfully?
A. By printing messages to the console.
B. By showing pop-up messages every iteration.
C. By changing the background color of a status box.
D. By logging messages to a file.
Q9. What is recommended to avoid when killing a process or shutting down Access mid-loop?
A. Using the Save As feature.
B. Stopping any background activities.
C. Using the VBA window to reset the process.
D. Editing the database design.
Q10. Why might you want to resume a loop at a specific record after aborting it?
A. To start the process from scratch without errors.
B. To avoid resending data to those who have already received it.
C. To minimize the number of total iterations.
D. To ensure all data is deleted before reprocessing.
Answers: 1-B; 2-C; 3-B; 4-D; 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.
|
