How to Use a Do While Loop in MS Access VBA

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we're going back to our microsoft access playlist and we're going to look at how to use a do while loop and we're going to do a couple of different ways that you can use a do while loop and we're going to use it on a record set so that it's more like a real world example which is uh most of the time where you're you're looping through and doing doing all kinds of operations so without further ado let's get to our do while loop okay so for today's example i'm using same database that i've used uh in other other examples and demonstrations and i created this table that has 34 000 dates in it that we just randomly created in another episode and what i want to do is i'm going to use the same record set for our do while example and what first what we're going to do is we'll we'll go to the create tab and then we're going to create a new module and i'll save that module i guess i'll call it uh do while module or something just uh just so that you've got your code saved so i like to save it when i first create it and then i can sort of just hit control s each time so we'll we'll create our module and you could also use the the code that i'm going to show you today you can use it in your code behind for your forms so your your your form code you know with all the events on it and everything you can also use this uh these same techniques so i'll create a new sub subroutine and we'll call it demo do while and what we're going to do is we're going to demo the the do while loop and we're going to open that record set and we're going to show how how the do wow works and different ways that you can exit the loop depending on conditions that you know you might have in your in your code and that makes it very flexible for doing all kinds of stuff and so what we'll do is we're going to create some variables for our database our record set and i'm going to make a counter as a long integer there and i'm just going to set these up very quickly you know just set a reference to our current database here and uh and then the record set i'll just open the table directly without using you know a select statement i'll just say date demo and then at the end of our procedure we're gonna we're going to close that record set we're going to set set it to to nothing and the database uh equal to nothing so that releases all all of the references there and then we can go ahead and we can set our counter equal to zero and then that makes it so we're ready to go here and from there we can go ahead and we'll make our our loop so we'll say do while our counter is less than 50 say so that means that the loop is going to continue to run while the counter is less than 50. so if it ever goes over 50 or equal to 50 then it's going to exit that loop and what we'll do inside our loop is we're going to print the record set i forgot the name of the field okay that's called date rep okay so i'll go back to my code here and um the name of the field is date rep so we're going to say debug.print rst and then exclamation date wrap and uh and then we're on the next line we're going to move to the next record so that means uh this is going to increment our our pardon me it's going to move all the way through the record set um and it'll actually go all the way unless we we don't you know increment our counter as well because the while loop is looking at our counter so we also want to say hey you know counter is equal to counter plus 1 and that's going to increment the counter but each time the loop goes through it's also going to you know move to the next record and it's going to print out that date there so it's going to put it into that immediate window you see below and there's our move next that's going to that's going to move the record set move next each time so it's going to basically walk through and if i hit go that run on the toolbar there then you can see it's done that and it exited after 50 times through even though there's 34 000 rows and you know plus rows in our in our table it's going to exit at 50. so i could make that a little more descriptive in the output i could say you know print our counter and a colon and then i'll concatenate the date on the end of it there and then if i hit if i hit go on the toolbar you can see there's 50 rows and if i go to the top there's 1 to 50. and the first row entry actually has has a time on that date as well because it is a date time field but that was the only one that had a time on it so that's very handy and that's how we can you know exit or have our loop go while some other variable is equal to something but there's other ways we can use it as well so say for example maybe i don't want to look at my counter maybe i want to exit the loop when the value in the record set on the row that we're in is still say greater greater than um [Music] greater than say a particular date and we can definitely do that so i could say punch in a literal date that's what the hashtag is there so in vba we can we can make a literal date and that's going to interpret that as usdate format and i could say do our loop while we're greater than the first because the the dates are in descending order and there you can see now it stopped at 34 which was the number of dates that were less than or pardon me greater than you know july 1st 2020 and so it got to 34 rows and it still incremented the counter but the counter was really just just being incremented until you know the loop exited and so we did rely on our move next because that's going to change the value of rst date rep each time so that it can be evaluated in the do while loop to see if it's still greater than you know the 1st of july 2020 and you can also add other conditions if you want you know so you could you know loop while you're evaluating the date but you could have another condition inside of your loop and you could say if the counter is equal to 20 then then exit do and exit do it basically says exit the do loop and so that gives you a nice exit that you can use for alternative circumstances that might you know be more complex where you're still you're evaluating dates or some condition but then if some special condition comes up you can still get out at that time and you can see it grabbed 20 rows you can see the 20 in in the in the immediate window and there's our condition for you know if the counter is equal to 20 then exit due and if i remove that i just comment out that line and i rerun it we go back to having there's 30 34 rows where the date is greater than um than the date that we put in in our condition and so also as i show in other episodes you can also run this your you know your subroutine by placing the name of the subroutine in the immediate window and you can hit enter and it will execute it and this is very very handy for if you need to you know have an input argument as well so you can't just you know click run on the toolbar when you have an argument so you need to put it into the immediate window so say i create you know a stop at variable to say stop if you hit this many um then at that point we could use that input variable to stop at let's say it's a variable number depends on some conditions in your program then you could say if the counter you know is equal to the stop at value then exit and so at that point we would put in our the name of our subroutine but then we would also you can see it asks for the stop at and in vba we don't use brackets so i'll just say demo do while 19 and then if i hit enter you'll see it got 19 records because there were 34 that were less than the date rep field [Music] and if i did 10 you'll see it grabs 10 because there's you know it hit the stop at before it ran out of records uh in the loop so the loop didn't complete all the way to its final condition of rst date rep is greater than 71 2020 but if i remove if i set it to higher than that you can see higher than 34 then you can see it stops at 34 where it does hit that condition and so that's sort of how you can have multiple sort of exit conditions on the on you know do while and that makes it it's quite flexible for different for different purposes so we could go ahead and change our condition to look at the record set itself and we could say do while not rst.eof which eof i covered in a previous episode so you can check out the end of file so you can see here if i you know it says go until you hit the end of the record set but we put our stop at at ten thousand and so it stopped at ten thousand and uh and so you can see that you know the eof that's our end of file and it's going to when it hits that point it should stop the loop has to stop and so now our condition is to move all the way to the end of the record set and so if i put in some value say that's even higher then then the amount of records in the record set then then it's going to so if our stop ad is actually higher than the number of records then it's going to go all the way through the record set and it our while condition is going to have the meaning of stop at the end so i put 1 million in there and i hit and if i hit enter then it's going to cycle through the entire record set and it's going to lock up at a certain point and check out my my other video about using using do events and that video will show you how to stop this sort of not responding message that you see there by passing you know some control back to windows but there we go so there's our 34 100 104 records so we use do while in this case to go all the way to the end of the record set and stop after you hit the end of the record set and so if i open the table directly here and i just look at you can see there's our oh there's our 34 104 records and we were able to traverse the entire record set and stop at the end and that is how you can use a do while loop in access vba hope you enjoyed today's discussion on how to use the do while loop in microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel and uh click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day i'll talk to you next time

Channel: Sean MacKenzie Data Engineering

Views: 192

Rating: 5 out of 5

Keywords: How to Use a Do While Loop in MS Access VBA, access do loop, access do while, vba do while, vba loops, ms access loop, ms access loop until, vba loop until, sean mackenzie data analytics and data engineering

Id: coZcu8g1rY8

Length: 13min 29sec (809 seconds)

Published: Tue Sep 07 2021