Employee Training 8
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 8 In this Microsoft Access tutorial we will learn how to manage employee training and certifications by setting completion and expiration dates, handling status changes, and creating efficient subroutines. You'll discover how to update records dynamically and ensure accurate tracking. This is part 8. MembersThere 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, employee training tracking, certification tracking, expiration date update, status combo in progress, status combo failed, manual completion date update, VBA subroutines, automate expiration, missing training query, unfinished training query, expiring soon query, report building
Intro In this video, we continue our employee training tracking series in Microsoft Access, focusing on updating training completion and expiration dates, handling changes to training status, and properly managing fields when statuses change to in progress, failed, or expired. You'll see how to use VBA to create reusable subroutines for updating expiration logic and make your code modular and efficient. We also cover handling manual updates, fixing common date calculation errors, and strategies for maintaining accurate records as employees complete or update their training history. This is part 8.Transcript 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.Quiz Q1. What action is being taken when an employee's training status is changed to 'completed'?
A. It updates both the completed date and the expiration date.
B. It updates only the expiration date.
C. It deletes the training record.
D. It sends a notification to the manager.
Q2. In the scenario where a training status is set to 'in progress' or 'failed', what happens to the completed and expiration dates?
A. They are updated to reflect the current date.
B. They are left unchanged.
C. They are set to null.
D. They are set to the default system date.
Q3. When a training status is manually marked as 'expired', under what condition will the expiration date be updated to today's date?
A. If the expiration date is in the past.
B. If the expiration date is today's date or earlier.
C. If the expiration date is in the future.
D. If the expiration date is exactly one year from now.
Q4. Why is it recommended to modularize code by creating functions or subroutines according to the video?
A. To reduce the number of lines of code.
B. To avoid repeated code and allow reuse in multiple places.
C. To make the code look more professional.
D. To make it easier for beginners to understand.
Q5. How does the speaker propose to handle manual updates to the completed date in order to ensure the expiration date reflects the correct number of months?
A. By manually recalculating and entering the expiration date each time.
B. By creating an update query to adjust all dates accordingly.
C. By calling a subroutine that recalculates the expiration date based on the new completed date.
D. By leaving the expiration date unchanged and only updating it on the status change.
Q6. What is the main problem identified towards the end of the video regarding the expiration date calculation?
A. The application crashes during date conversion.
B. The expiration date is incorrectly using today's date instead of the completed date for calculations.
C. The expiration date always defaults to the first of the next month.
D. The expiration date never updates when changes are made.
Q7. What concept is highlighted as 'beginner stuff' that the host decides not to cover extensively in the video?
A. Creating complex queries from multiple tables.
B. Adjusting form formatting and navigation features.
C. Understanding advanced database indexing techniques.
D. Designing macro operations for database tasks.
Q8. What future topics are hinted at for part nine of the series?
A. Advanced report designing and cloud data integration.
B. Query creation for tracking missing, unfinished, and expiring training.
C. Basics of database design and data entry.
D. Employee motivation techniques and productivity tracking.
Answers: 1-A; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-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 continues our employee training series with part eight. We're focusing on how to track employee training completions and handle expiration dates for certifications. If you haven't followed the earlier parts of the series, I recommend starting from part one to get the full context.
In our previous session, we automated the setting of a completed date when a training status changes to 'completed.' If a course requires recertification after some years, we calculate and set an expiration date accordingly. For example, HR 101 doesn't require recertification, while HR 202 does after one year. When a training is marked as completed for HR 202, an expiration date is generated automatically.
However, if the user's status reverts back to 'in progress,' indicating the training wasn't completed, we should clear the completed and expiration dates. In the design view, modifying the code for handling this involves setting these fields to null if the status is 'in progress' or 'failed.'
For a manually marked 'expired' status, we should maintain the completed date as is. The expiration date should be set to the current date only if it's still in the future. This logic ensures our data reflects the most accurate status of the training. We consider employing update queries in the future to systematically standardize expired statuses across the database.
The strategy involves refining how we handle different status changes using conditional logic, namely through else-if statements in VBA. This helps ensure smooth transitions between different training states.
We also address situations where past completion dates are entered manually. I proposed extracting related code into a single subroutine, 'update expiration date.' This allows us to call the update functionality from anywhere needed in the application with ease, reducing redundancy and maintaining cleaner code.
This involves placing relevant logic into a private subroutine so both updates to the status and manual changes to the completed date consistently trigger an expiration update. Through proper code organization, by putting key logic into dedicated subroutines, we ensure our application remains flexible and maintainable.
When a user inputs an old completion date, the expiration calculation adjusts based on the historical completion date, not today's date. Debugging highlighted the misuse of today's date in the date calculation, which I demonstrated how to correct by incorporating the completed date instead.
As we move into the next phase, part nine will cover the creation of queries for identifying missing or overdue training. We'll explore how to query for situations such as missing enrollments, unfinished or expired training, and training soon to expire. This will set the foundation for potentially generating reports. Additionally, automating status changes of expired training upon database loading is on our agenda.
I always welcome your input and ideas for new topics or features you'd like to see covered in upcoming sessions. Feel free to leave comments with your suggestions.
That's it for today. You can find a complete video tutorial with detailed, step-by-step instructions on my website at the link below. Live long and prosper, my friends.Topic List - 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 |
