Today's part eight of my employee training series, where we're tracking employee training and certifications and all that jazz. If you haven't watched parts one through seven, go watch those first and come on back.

Last time we set it so that if our person changed this to completed, it would set the completed date. If there was an expiration date or an expiration number of years, it would set that. I don't think HR 101 has it. Let's double-check and make sure HR 101. Yep, it's a zero. All right, HR. Oh, there it is. It's a zero. HR 202 is a one. So if this was HR 202, let's just change this here. Once changes to completed. Boom. We get an expiration date and expiration date.

Now, what if the person goes back and changes this to in progress? Well, that means they didn't complete it or you know, expire it. So we want to maybe go back and change these to null. Let's go back to our design view and we'll go into the code editor. What we're going to do is bump this down. We'll say if status combo equals one or status combo equals three, that is, let me look at my little notepad here, that is in progress or failed. Then in progress or failed. We're just going to say completed date equals null, expired expiration date, expiration date equals null. Right? Then this becomes an else if that. Pretty straightforward. Save it.

Now, what if they set it equal to expired? Well, that's the case. If they come in here and they set this equal to expired, then leave the completion date alone. But set the expiration date equal to, I'd say today, unless the date's in the past. Later on, we're going to do something where we can say, okay, go through the database, maybe we'll make an update query or something to go through the database and mark all of the stuff that should be expired as expired. But if they do it manually. If I make it marked expired right now, and this is like next, next February, well, set it to expired. If not, if this is in the past, leave it at whatever date it's at.

So here's all, here's what I would say. I would say another else if in here status combo equals four. So then, and this is the mark expired manually, I'd say if expiration date is greater than today's date, then set the expiration date equal to today's date. If it's in the future, mark it expired now, if it's in the past, leave it. Makes sense.

Okay. Let's double-check it. Let's say this is, okay, let's go back to in progress. Let's put a date in here, 2025-11. And we've got to change that cycle too. Look, it went to a new record. We'll get to that in a second. Right? Let's mark it expired. Boom. And it puts today's date in there. But if it's a past date, if this is 1101, and we'll go from in progress, oh, see, our in progress kills it. And let's put today's date in there. We'll go, oh, one, let's make it November first. And we'll now change it to expired. And it leaves it as November first. That's what I want. Let's set that cycle.

See, there are all kinds of little things when you're building a database that you're going to constantly be making little tweaks and stuff like that here, like this. I want this to be the cycle being just the current record. From this form, you could even do things like turning off the record selectors, the navigation buttons. That's up to you. If you want them to be able to add stuff and move between records in here, that's okay. But the way we have it opening is it opens up just that particular field or that particular record. All right. And these off if you want to turn this off. Stuff to you. These are all beginner things that you should know how to do. So I'm not going to waste time covering them in this video. If you want to learn more about that kind of stuff, form formatting and that, go watch try beginner series. Okay.

Now, what did they come in here? All right. And let's say, let's go back to Jean Lou. Because I know he's got stuff on it. All right. So he's got these. Now, let's say that he completed this, but he completed it last month and you're just now entering it. Okay. You marked it completed today and it puts today's date and they're in a calculator the six months. But he actually completed this October first and you're just now entering it. So we need to also update the expiration date if the completed date is manually changed.

Here's the problem. I already got all this nice code here for updating the expiration date. I don't want to have to put this in two different places. This is why it's important to make functions and subroutines. Someone's beaming in. It's important to make stuff like this into its own standalone subroutine so you can call it from two different places or more and not have to repeat code. We don't want repeated code in our database.

So what we're going to do is we're going to snip out all the stuff that can be modularized. Let's create a private subroutine update expiration date like this. We're going to put all the stuff up in there that has to deal with updating the expiration date. So these variables that we need don't need them here, but we do need them up there. Okay. So those extra lines. All of this stuff right here is the stuff that has to do with updating the expiration date. I'm going to cut that out and I'm going to put it up here. Let me fix my tab like that.

Indenting is important people. Very important. You know, how many times I've helped someone with their code and it all was because you know a mistake could be something as easy as this and they didn't know that, you know, this end if matched up with that. It's just little things. Make sure your indenting is crisp and clean. No caffeine.

But we still got to call this code from down here. I'm going to take this right update expiration date and put it right there. So now status combo equals to update the complete date. Then run all that same code. And hops up here. Does all that jumps back down there? That's how that works. But more importantly, now I can call this some other places for example. I want to call it when the completed date is manually updated. So I'm going to go over to event after update dot dot dot that puts me in here. And I'm going to go. Poop. That's all you got to do. Now it'll call that same block of code to update the expiration date. Save it. Debug compile once in a while. Come back over here. Close it.

Let's go into AC 101. Let's say it's in progress. Okay. Finish it today. So he completed it. But oh, he turned in the paperwork today. He really completed it October 1st. So I'm going to come in here and change this to it. And one and it update update. Hang on. And one. Oh, I know what I know what the problem is. I know what the problem is. I ran through this earlier. I forgot I let this bug in here intentionally. I'm going to come back over here. I want you to see if you can figure out what the problem is. Does the video and see if you can troubleshoot this problem?

Do you see it? We're looking up the expiration years converting it to months. Expiration date equals date add expiration months to today's date. That's the problem. We don't want to use today's date there. We want to use the expiration or the completed date. Completed date. The expiration date is based on a number of months since the completed date, not today's date. Okay. That ought to fix it. Let's go back over here. I know there's a reason I left it in there for a couple of months. So let's change this to 10 to and there you go. 4 to and it works. Beautiful.

That's about it for today's video. Coming up Monday and part nine. We're going to start making some queries to show things like employees with missing training based on the roles if they have not even enrolled or set up the training that they need missing unfinished training. In other words, it's got an enrollment date and no completed date. Expired training or expiring soon. These are all things we want to see. I'm going to show you how to make queries for them and then maybe we'll make a report out of one of them but making the reports easy. Once you get the data and query that building a report out of it is beginner stuff.

Then also we need to a so automated method for marking training that is expired as expired. So if it, you know, when the database opens, for example, if we see the expiration date is in the past, change the status to expired, all those kinds of things. So that's lots, lots more coming up. Those are just some more ideas and things I have. If you want to see stuff, all right. If I haven't covered something, you want to see how to do it. Post your comments down below and if I like it, I'll add it to the list.

This series will continue on as long as we've got cool stuff to add to it within reason, of course. Or if it's little things that I don't think a lot of people will like, maybe we'll throw it into a, you know, another extended cut or a seminar or something. But if you got ideas for stuff that I haven't covered that you want to see how to do, post them down below in the notes in the comments.

That's going to do it for today. That's your TechHelp video. Hope you learned something. Live long and prosper my friends. I'll see you Monday for part 9.

TOPICS:
- Tracking employee training completion
- Managing training expiration dates
- Handling status changes: completion and in progress
- Coding with VBA: setting completion and expiration dates
- Using VBA to nullify fields based on status
- Manually marking status as expired
- Creating reusable subroutines in VBA
- Modularizing expiration date logic
- Event handling for manual date updates
- Debugging VBA code for date calculations
- Preparing for future sessions: queries and reports

COMMERCIAL:
In today's video, we're continuing with Part 8 of our employee training series. You'll learn how to manage employee training progress, update completion and expiration statuses, and handle manual adjustments in dates. We'll create private subroutines to avoid code repetition, ensuring efficient database management. Plus, you'll see how to troubleshoot potential bugs related to expiration date calculations. Finally, get a sneak peek at part 9, where we'll dive into creating queries for tracking missing, incomplete, and expiring training records. Don't miss out on these essential tips for managing training data! You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.