How To Use A Timer To Run A Command In Microsoft Access 2013 (Automated Tasks)

hello everyone and welcome to access 2013 how to my name is Steve Bishop and in this video we're going to talk about how to use a timer to run a command now I'm gonna start off with a very basic example that a lot of you will find useful but then I'm gonna take it one step further and show you how you can set multiple times throughout the day to run commands so in order to get started let's hop into our Access database and take a look so the first thing I'm going to do is I'm going to create a form here and on my form I'm going to drop in a label and this is going to be a clock that shows the time up to the second and so we're gonna put in some dummy data here in this label and I'm gonna reef resize the font to 18 actually now let's make it bigger than that let's make it 28 and I'm gonna change the font color to black and we'll just double click to resize that and we have a pretty nice sizable system time setup here on our form now let me go and rename this label so I'm gonna go and change it to lvl clock and let's go ahead and save this form as it is right now I'm just gonna call it frm clock and now what I need to do is I need to update this label every second and the way I'm going to do that is I'm going to go to the form properties here and in the event tab down here or we can see timer interval and on timer and the timer interval is in milliseconds so I can specify after how many milliseconds I want to then trigger the on timer event since I'm gonna be calculating this every second I'm just gonna do 1000 milliseconds so that's gonna give me every second it's gonna then run this on timer event now I can go behind this on timer event and go into the code builder and here I can specify what code I want to run every second now so every time that the timer gets called so what I'm going to do is I'm going to update this label here called LBL clock with the current system time I'm gonna first dim the current time as a date parameter or excuse me a date variable and now we're going to set current time equal to and I would say now but the problem with now if we just take a look at this is that it gives us the full date and time so you can see this down here in my immediate window we get this the date and the time and really all that we're interested in is this part here and this is what's called the long time that's the hour minute and second so I need to actually format this now into a date time that is just the time and the way we can do that is using in access there's a function that does this for us so we're gonna use it it's called format date/time and I can pass along the expression of time which is now and then I can say what format I want it and that's gonna be VB long time okay so if I just gonna show you this I'm gonna copy this and paste this right here in my immediate window and you can see that it just gives us the current time not the whole day so that's what we're gonna set for our current time now I have my current time so I can go ahead and set it to the caption of my label so I'm gonna go me LBL o'clock caption equals current time let's just go and save this debug and compile yep everything looks good and let's just run this to make sure our clock works and there we go so every second now our clock label is going to be updated with the current time okay so now we want to have some sort of event triggered once a day so we need to compare the current time with a specific time during the day and in order to do that I'm gonna let the user actually specify the time that we want the event to trigger so I'm going to drop in this this text box here and this text box is where the user is going to enter in what the time is that they want to perform the action so I'm gonna go ahead and the alarm time this is my label says alarm time and I'm gonna resize this to be font 16 and let's just double click to resize that and expand this out a little bit so that it's wide enough now I'm gonna change the name of my text box to txt alarm time and on the data tab I'm gonna go to the input mask and the reason why is because I want to make sure that the user is only entering in a time I don't want them to be entering in a date I just want them to be entering at a time that each day this event is going to be triggered so on my input mask here I can click on the ellipsis and we can see that there's actually already a long time input mask so we should just go ahead and select it and click on finish and now when the user goes to enter in the time they're going to be forced to enter in a long time so now that the user can enter in a time in the long time format I'm going to go back to my event for my form to the on timer event and here now we can actually compare to see if the current time is equal to what the user entered into the textbox but before I even do that I first want to check to make sure that the textbox isn't null so we're gonna do if not is null me dot txt alarm time then now I'm gonna go ahead and do my comparison so we're gonna see if me dot txt alarm time equals current time then and now we can run whatever commands that we want okay so whatever function or command that we want to to perform here we can go and enter it so we'll do do command and I'm just going to do a system beep okay but this could obviously be a reference to anything in your application let's go ahead and save this and let me just debug it and make sure it compiles correctly looks good now let's go and let's test it out so I'm gonna do view on the forum and let's just set a time here to initiate our system beep 0 3 2 4 0 0 p.m. and let's just wait a few seconds here and and there's the system beep it's a little bling I don't know if you guys could hear that or not but it did activate so we are all set and there you go that's a very simple demonstration of how you can set up a timer to run at a very specific second of the day so what if we want to add more right what if we want not just one event to run one one thing to run but we want different things to operate per throughout the day well things are gonna get a little trickier so we're gonna step up our game a little bit on this code so since we're going to be keeping track of multiple alarms we want to keep track of not only the time but we also want to keep track of the function inside of a table because the table is gonna be a nice easy way for us to keep to store all of the alarm times that we want to keep so I'm gonna go to create a table design and I'm just gonna say ID for my primary key and this is gonna be an auto number and that's fine now we want to do an alarm time as a type of date/time and you'll notice that on the date/time data fields we can go once again to the format and we can specify that we want it to be a long time so that's the format that we want to store in our table then remember I said we want to also keep track of the function that's going to run at the specific time so we're gonna say function name and I'm gonna just change the field size to 50 you can obviously make the field size whatever you want but 50 is usually pretty good and let's go and save our table so I'm going to say table alarms and we're all set with our table now we need to modify our form so that we can actually add entries into our table so I'm going to go ahead and go back into the design we're going to add another text box this is gonna remember we need the function name so this is where the user is actually going to type in what the function is going to be and let's just resize this text box font to 16 double click to resize and I'm gonna make this a little whiter here move this and move this back over a little bit just a little Corrections here looks like I dropped it down one there we go okay so we have our alarm time we have our function name and I also want a button on here that will actually add the entry to our to our table so we'll say add alarm' and once again we'll change the font size to 16 double click to resize and let's go ahead and rename our text box and our buttons so this is going to be txt function name and BTN add alarm' okay so on our add alarm' button we need to go ahead and add a record to our table but before I even do that what I'd actually like to do is whenever this form opens up I want to open the table up as a record set behind the form and I want to do that because it's going to allow the code to run faster if the alarm information is already available in the data in the memory of the application if it has to go and check through the tables every single time that can actually slow down things so I'm gonna go back behind the code and I'm gonna go to the event onload and this is again on the onload of the forum we are going to actually set a record set and we're going to actually make the record set available to the entire forum so we're going to be private RS as record set and then when the forum loads we're going to set the value of this record set so we're going to say set RS equal to current DB open record set and it's just going to be the table so TBL 1 excuse me TBL alarms excuse me and DB open Dyna set DBC changes because we do want to be able to make changes to the record set as we need to so let's go to save this and now it's going to fill our record set with the current values of our alarms and now we can go and program now behind the ad alarm button the addition of an alarm to the record set so we're going to go to the on click event of the ad alarm button and we're just going to go to RS add new and I'm just going to do it as RS update just make sure I've got both commands in there and we're gonna set RS and alarm time equal to me dot txt alarm time we're going to set RS function name equal to me dot txt function name now I should probably make sure that there are values in these text boxes before I go and do all of this on the record set so before I do that I should probably do if may not actually if not is null me dot txt alarm time and not is null me dot txt function name so we're checking to make sure that those aren't blank and if they aren't blank then we go ahead and do the record set updates and if and let's just tab this over so that it's more legible and save that okay so now that adds the record to the record set but right now we don't really have any display of all of the alarms that are currently in the table so let's go ahead and add a list box that will show us all of the currently added alarms to our system so let's go ahead and add a list box this is going to be alarms and let's change the font size of this to 16 so that it matches the rest of the form let's make it nice and wide and move the label over a little bit so it matches the rest of our form okay so now this list box I'm gonna need to rename first so that I can reference it in my VBA code so we'll do LST alarms and the list box is going to contain a list of the alarms from our from our table so we can just simply go up to the data row source and I can just select the table alarms and I'm just gonna add these three fields and that's gonna be fine but I also on my form or on my list box I need to show I want to show the alarm time and the function name that's associated with it but I don't want to show the the ID value I need the ID value to be in the list box so that we can select it because we're gonna also need to be able to delete alarms from the system and I'll show you how to do that here in just a moment but I just want in my list box to see the alarm time and the function name that's being called with that alarm so in order to do that I need to adjust the format so the column count is actually three because there's ID alarm time and function name but the column widths are zero inches for the first column the other two are just going to be automatically space so we're just gonna save that and now let's go ahead and go back to our ad alarm event or our ad alarm button with the on click event and we want to say that once we've added an alarm to our record set which is our table of alarms let's go ahead and tell that list box to wreak wery and get the latest list of alarms so we're gonna do me dot LST alarms re-query okay so let's save that and let's give it a try let's make sure that it works so we've got an alarm time doesn't really matter even if it's in the past I'm just gonna do some function name that will come up with later add alarm and beautiful so we have the alarm time and the function name right there and of course you could get real fancy with this list box if you want it and add you know add titles to the column names if you want but as long as we are able to add different alarms to the list then we should be good and if we just double click we can see our alarms are being added to the table so we are all set with adding an alarm but now we need to be able to delete alarms the way I'm gonna do this and of course you could come up with your own interface if you'd like but I want the user to be able to just simply double click on an alarm and that will delete it from the list so let's go to the alarms list box and let's go to on double click we're going to go to the code builder and we are just going to do we're going to need to find the record set and we need to find the record in the record set that we just double clicked on so we're gonna do our S find first and luckily for us the list box when we double click on it it's going to change the value of the list box to the ID of the item we've selected okay so that's gonna be first name or find first ID is equal to the value that we get back from me dot LST alarm and now that will move the cursor in our record set to that specific alarm now we can go ahead and do our s delete and that's all we need to do in order to delete a record but I am going to require e the list box one more time so that it refreshes with the latest data so we're going to do mean LST alarms re query and let's save that and compile just make sure I didn't fat thing or anything again and let's run the code and I should just be able to double-click and that removes it okay so we are all set there and I can delete each one of those double-check our alarms table and sure enough there are no alarms anymore so we have a form here that adds alarms to the table and stores you know stores those alarms in the table and then displays those arms in our list box for us so now we need to do some coding that is going to compare the current time to all of the alarms that we have in our table and if any of those alarms should be triggered then go ahead and perform the function or the function name that we've that we detailed so I'm gonna go back into my timer code and in here we're going to need to loop through all of the records in our records set so since we're looping through the records in our record set that's why I wanted to make go faster because if we had to go and do a query each time it would take a little bit longer and I don't want to do that I want to just have all my records open and I could just loop through them each one at a time so first let's do it if not our SEO F then our s move first and that way we move the record cursor to the first record in our system or in our record set I should say and then we want to do until our SEO F pretty typical looping code that we've done before for record sets so our s move next okay and what we're gonna do is we're gonna compare the current time that we set here or actually it's up here that we set current time and we're gonna compare that to the alarm time within each record so we're gonna say if RS alarm time equals current time then and inside of here we're going to actually execute the command that we saved and we can do that just by using the eval function and the eval function will actually interpret a string as an expression okay so we can pass in that function in R that we put into the table since that that string that or that function name is a string we can use the eval method to actually evaluate that string as an actual VBA function to execute so we're just gonna do eval RS and function name now there is one thing that we do and it I actually kind of tripped up on this a few times when I was trying to do this since we moved the record set to the end of the file then what tends to happen is that it actually keeps the record there so we have to do another but to move the record set back up to the top there because right here we're saying if it's not at the end of the file then move to the first but since we looped through the record set until we got to the end then when it hits this code the second time through on the timer it's not going to move the cursor back to the first one so we need to actually force that to happen but we also need to account for the times that there isn't a record at all in our record set so what we actually have to do is if not our SEO F and our s record count is greater than zero then we're going to go ahead and our s move first so we're just double-checking we're making sure that excuse me actually if we are at the end of the record set sorry so if we are at the end of our record set so after we've looped through if we are at the end but we have records then we need to move to the first record that way the next time that the timer is run it will hit this part of our code here and see that oh it is moved actually to the first record and it's just gonna remove it to the first record again that's fine and then it's gonna loop through our code okay so just something that I did get initially tripped up on and I was like ah what the heck is going on here and I realized that it was just looping through one time it wasn't doing it again okay so let's go and save our code and debug compile okay good everything looks good it likes the syntax that we've put in there so far and let me just check and see I don't think oh I do have one alarm in here I'm just gonna delete this manually for now I could have obviously done that in the forum but whatever okay so I have my timer running there I have my clock going let's say I want to do the function name let's see I'm gonna do do command beep and the alarm time is gonna be 3 I want this set this time soon enough so 4 1 3 0 p.m. and add the alarm and now only just sit there and wait and see if it runs and uh-oh Microsoft Access cannot find the name do command you entered in the expression now I wanted to show you this this is an issue that I ran into when I was initially trying to set this all up and I thought gosh you know that's a little different what is this error that I'm getting here and I racked my brain about why was this not working and eventually I came a call upon a solution here and and really the way that this should probably work is that this function name that you call should not be a system command it should be a actual public function that you create in it your methods okay or excuse me in a module so I'm gonna actually go ahead and create a module here I'm gonna go to my Microsoft Access classes folder here and I'm going to insert a new module and I'm gonna create a public function that I'm going to then be able to call so we're gonna do public function and this could be a subroutine but function just works fine so public function command execute and to make this nice and variable to make this kind of a something that I can pick and choose which commands I want to execute I'm actually going to set up a parameter here so command number as integer I'm going to pass that along to my function so when I call this command execute I need to also specify what the command number is and now I could do a select case on that command number and I can specify if case is equal to say 1 then I'm going to go ahead and do a do command beep okay so if I pass along the command number 1 it's going to do a system beep and let's just do I'm going to do case is equal to 2 then we'll do a message box you executed a command ok so those are going to be our two different commands that we can issue I'll save this module it doesn't really matter what you name it so we'll do mod commands and debug compile just make sure that's all good so remember we want to use the command execute and we need to pass in an integer so I'm gonna go ahead and delete that one and it looks like did I goof on something I might have goofed here let me just delete this okay and layout let's close this out all together and reopen our form okay so the function name is going to be command execute we're do one and the time that I want to execute it is zero three four five let's do fifteen pm and let's go ahead and add the alarm and so again we can see that the alarm is going to go at three four three forty five and fifteen seconds command X cubes 1 and buttering there we go so I don't know if you guys heard that but it certainly went off now we can go ahead and add another one I'm going to add command execute two this is going to go off at 35 seconds and now we have a second command that's going to execute and you executed a command perfect so we're actually able to call a function from right here within you know we have a timer now that's actually executing these commands we specify right here in the function name now we could of course get much more specific here we could turn this into a drop-down of all of the available functions that the user could pick from that's probably a better way than just typing it in here but I just kind of wanted to show you that you could do this as long as you have a command that is properly formatted to a public function or a public subroutine then you can just use that eval command function in order to execute a string as an actual command let me just delete these and there we go so I hope you guys enjoyed this video I hope you guys learned a lot I I'm I'm glad that you know I really like stuff like this especially that allows you to automate some of the tasks that you may have around your office maybe you export CSV files maybe you have you know a specific task and you're just tired of having to go in and manually run it yourself you can just execute it right here from this window instead so if you guys did enjoy this video please don't forget to Like favorite and subscribe and tell all your friends about this wonderful channel because you know the more subscribers I get I love it because I know that I'm reaching more people and hopefully I get to see you guys again in the next video thank you so much

Channel: Programming Made EZ

Views: 43,396

Rating: 4.9921103 out of 5

Keywords: microsoft, access, 2013, vba, code, how to, timer, command, function, subroutine, expression, lesson, tutorial, tip, trick, efficient, office, execute, run, form, multiple, alarm, trigger, task, automation, automated

Id: dIJXFmxCMts

Length: 29min 13sec (1753 seconds)

Published: Mon Jul 11 2016