Employee Training 7
By Richard Rost
2 years ago
Employee Training Tracking in Microsoft Access Part 7 In this Microsoft Access tutorial, we will learn how to manage employee training records by automating the update of completion and expiration dates when training status changes. I'll show you how to lock controls, use the Dlookup function, and handle fractional years using the DateAdd function. This is part 7 of the series. 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, Employee Training Tracking, Certifications, Completed Training Date, Expiration Date Automation, VBA Code, Combo Box Locking, Design View, Course Table, SQL Statement, Update Query, dlookup Function, DateAdd Function, Status Table, VB Editor, Microsoft Access Multi-Part Series
Intro
In this video, we continue the Microsoft Access employee training series by focusing on how to update training records when a course is marked as completed. I will show you how to lock fields in your form to prevent unwanted edits, set completion and expiration dates using VBA, handle refreshes for "dirty" records, and use the AfterUpdate event to manage status changes. We will use DLookup and NZ to pull data from your tables, address issues with adding fractional years in Access by converting years to months, and ensure your data entry process is accurate and controlled. This is part 7.
Transcript
I promised you we'd come back to it. This is part seven of my employee training series. We're tracking the employee training for an organization. It could be for, you know, certifications or classes they have to take, all that good stuff. If you haven't watched parts one through six, go watch those first. You'll find links down below in the description. Then come on back when you're done.
Alrighty, we're back. This is one of those things. When I do these series, these multi-part series, it's nice to do a bunch of them in a row because then I don't have to stop and go back and figure out where I left off and all that stuff. When I did parts one through six, right after that, I took a break, and I went out of town for a week. Today, I had to spend some time reviewing what I did and going over my notes, figuring all that stuff out. When I promise I'm going to get back to something, I always get back to it eventually.
Today we're going to work on this thing down here. We're going to do it so that when the user actually completes an in-progress piece of training, we fill in the dates. We've got some dates in here that we have to fill in, right, the completed and the expiration date. We want to put some controls in here so that they just can't do anything willy-nilly. How much control you put in here is up to you. You can either lock it down a lot so that they have to follow the rules, or you can give your users the discretion to do what they want.
Now the first thing I'm going to do is lock this combo box because I don't want them just coming in here and doing that. There are some dates that I want to set, like when it was completed and when it expires if it has an expiration date. Yes, you can do that from here with some VB code. You can issue an SQL statement in the background, run an update query; you can do all kinds of things, but I want to basically force the user to have to open up this form to do that kind of stuff. It's easier if you can control these kinds of things from one place instead of having multiple places where they can make changes.
One thing I want you to notice first is this little guy here. That means this record is locked. You can't do anything with it. Why? Because I was in here, and I made some kind of edit, right? I changed this. Now this record is dirty.
The first thing we're going to do is before we open up this form, I want to do a refresh here. Design view. I'm going to go to this course ID combo box. See what I just did there? I've got to click on it twice. Once clicks on the subform, the second time clicks on that combo box. It's kind of tricky. Go into the on double-click event. That'll open up your VB editor. We're going to go there.
I resize things after I don't record video for a while because I work on my own database a lot too. So I move this window to a different screen. Right here, very simple, me.refresh. It's the same thing you have to do when you open a report. If you open a report and your form's dirty, the report is going to have bad information. I covered that in my invoicing video.
Alright, so that takes care of that problem. Next thing is I want to lock this guy. I mean, move this over. I want to lock this guy so the user can't change it here. That's just simply go to the data tab and go lock is yes. If you want to lock this one, that's on your own. That's up to you. You can lock this too. I'm keeping a list of little things that I'm going to put in an extended cut. I'm going to cover that in the extended cut.
Alright, so this will lock if this says it's completed. Okay. Save it. Close it.
So if you open this up again, you'll notice that you can't change this here. You can make this gray if you want to. But in order to change that, the user's got to come in here. In here is where we'll control what happens when these things change.
What is the change? Well, let's assume this goes. Let's see, let's get this started in progress. Here's the enrollment date, the deadline. What I want the first thing to do is when this changes, when the user changes this from in progress to completed, I want to set the completion date. Then I want to look up the expiration date. Now, how do we find the expiration date? Well, this course, if you go to the course table, every course has this guy.
Expiration numbers. If it's a zero, this doesn't expire once you get the training, like your new hire training. Once you're on board, you're good. It doesn't expire. But if it's got a number in here, that means you have to renew it on a regular basis. Let's make the accounting stuff here, AC 101, 2, and 3, let's make those so they expire.
Alright, where are we at? Let's do six months, one year, and two years. For AC 101, 2, and 3.
Okay. If change is yes. Let's go back over here. Notice over here, we got this. He needs to add that too. Let's add that. We'll put these all in progress. Oh, can't change it from here. See, that's good. Open it up in progress. Open it up in progress.
Okay. So he's got those three things in progress. We just finished AC 101. Let's open it up. Now I'm going to come in here and change this to completed.
Now, at this point, when this thing is changed, we want it to trigger an after update event that updates the completed and updates the expiration date. Design view, we're going to go to the status ID. We're going to look for your after update event right here.
Now the statuses are in the status table. In progress, completed, failed, and expired. Those are their IDs. Sometimes it's helpful to take this and put this in notepad so you can see it sitting right next to you as you're working. There we go. I dropped them in the notepad. I'm just going to slide them off the screen here. I'm going to keep my eye on them. But I do that a lot. Or you can use paint and take a little screen cap. Sometimes I do that with design view just so I can see the list of stuff that I'm working with.
Okay. If they set the status combo to two, which is completed. If status_combo equals two, then we'll mark that as completed. What are we going to do? Well, completed date equals the day's date. That's the default. You can change it. Like if they're, you know, if they reported this last Monday, you could put in last Monday as a complete date because that might make a difference for the expiration date.
How do I get the expiration date? Well, we're going to have to use dlookup and grab it from the table. It's in the course table. We've got the course ID, which is the course combo here. Then we're looking for what are we looking for? We're looking for the field expiration_num_years. So we can come in here. We need a variable to put it in. Going to dim expiration_num_years as a long. Actually, this is a long integer. This should be a double. Because it can be a 0.5, expiration_num_years as a double. Just so you used to make them long.
Expiration_num_years equals dlookup expiration_num_years from the course table where the course_id equals closure quotes and put course combo in there. There is the possibility that someone didn't fill that value in. Might want to enclose this inside of NZ null to zero and make that a zero. Now I know the expiration_num_years. I can say if expiration_num_years is zero, then it doesn't expire. So expiration_date equals null. Otherwise, expiration_date equals I want to add that number of years onto the completed date. It's going to be dateadd. Use the dateadd function. Years is not y; it's yyy. That's a common mistake a lot of people make. Single y is day of the year. It's a number from one to 365 or 366 in a leap year. Yyy is whole years. What is the number of years we're adding? Well, that's the expiration_num_years that we just looked up. Completed date is the piece we're adding to.
And if. So that's what happens if they change it to completed. Let's save it and test it. Save it. Close it. Open it. Let's set it to completed. And there's not working. What happened? We got this date right. We got that date right. What's I don't know why that wouldn't work. Let's see if we could figure this out.
Let's try a different class. Let's try AC 102. Go back to progress and then completed. It worked. That did the year. Let's try AC 103. In progress. Back to completed. I got my two years. Let's see. Why is AC 101 not working? Again. There you go. Yeah. Then go back. Nope. It didn't work. Why is that? Well, welcome to another one of my problems with Access. Sammy, this is a good one for the list. Go back into here. Here's dateadd, right. If you look at the parameters, that expiration_num_years says as double. That means you should be able to send a double, meaning a number with a decimal value into dateadd. I should be able to add half a year. But you know what? You can't. That actually only works with whole numbers. The Access team, you guys need to change that number as double to either number as long or number as integer. Doubles don't work. So Sammy, add this to the list.
What we're going to have to do is I think the easiest way to handle this is to convert that expiration_num_years to months. We could go back to the table and make that months. If you want to or we can just do a little math right here. We can say num or we'll just make it expiration_num_months as long. After we look this guy up, we can say expiration_num_months equals expiration_num_years times 12. If this is a one, it's going to give me 12. If it's a 0.5, it's going to give me six. Now we can come down here and we can add months and make it expiration_num_months.
Save it. Debug compile. Come back out here. Let's try it again. AC 102. This should be a full year. Yep. AC 101. Let's go completed. There we go. There's our six months. That's the fix for that. Yeah, I left that in here intentionally. This has happened to me before. I'm like, why can't I add a fractional year or even a fractional, you know, whatever.
That's if they pick completed. What if they change it to in progress or expired or any other status as well? We'll talk about that tomorrow. So tune in tomorrow, same bat time, same bat channel. Or if you remember, you can watch it right now because I'm going to record a couple more of these today. I'll do at least two or three.
Of course, if you need extra training on any of the stuff that I covered today, the after update event, dlookup, NZ, dateadd, all of those, I have separate videos for those on my website. I'll put links down below in the link section. That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part eight.
TOPICS:
Locking a combo box in a Microsoft Access form
Setting completed and expiration dates with VBA
Using refresh in Access forms with VBA
Detecting "dirty" records in Access
Understanding double-click events to open VBA editor
Locking fields in Access form to prevent edits
Setting up status change logic in Access
Using Notepad for quick reference notes
Implementing the AfterUpdate event in Access
Using DLookup to retrieve values from a table
Handling expiration logic based on table data
Using NZ function to handle null values in VBA
Adding years to a date using DateAdd function
Converting years to months for date calculations
COMMERCIAL:
In today's video, we're continuing with part seven of our employee training series. We're going to learn how to track the completion of employee training courses by updating the completed and expiration dates when a user marks training as complete. You'll see how to lock fields to control user input and ensure data consistency, utilize VB code to execute SQL statements, and apply date calculations using functions like dlookup and dateadd. We'll also address a common issue with Access involving fractional years, showing you how to work around it by converting years to months. 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 primary focus of part seven in the employee training series?
A. Reviewing previous training videos
B. Tracking and managing employee training completions
C. Designing a new employee training program
D. Evaluating training effectiveness
Q2. Before making changes to the form, what must be done first if a record is dirty?
A. Save the form document
B. Backup the database
C. Refresh the data
D. Delete the changes
Q3. What was the purpose of locking the combo box in this tutorial?
A. To prevent accidental data loss
B. To restrict user access to certain features
C. To ensure changes are made in a controlled manner
D. To improve database performance
Q4. How is the expiration date calculated if the course requires regular renewal?
A. By adding a fixed number of days to the completed date
B. By using a hardcoded date value
C. By using the expiration number of years from the course table
D. By randomly selecting a future date
Q5. What happens if the expiration_num_years is set to zero for a training course?
A. The training never expires
B. The training expires immediately
C. The training expires in one year by default
D. An error is generated
Q6. Why was there an issue with using the dateadd function with a double for expiration_num_years?
A. Doubles cause performance issues in Access
B. The dateadd function only accepts whole numbers
C. The dateadd function does not support year additions
D. Doubles are outside the function's range
Q7. How was the issue with the dateadd function resolved in the tutorial?
A. By rounding the double to the nearest whole number
B. By converting expiration_num_years to weeks
C. By converting expiration_num_years to months
D. By using a different function to handle the date addition
Q8. What does NZ function do in the provided video?
A. Changes null values to zero
B. Applies numerical zero formatting
C. Enhances the database's performance
D. Transforms dates to text format
Answers: 1-B; 2-C; 3-C; 4-C; 5-A; 6-B; 7-C; 8-A
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 is part seven of my employee training series. We're focusing on managing the employee training records for an organization, which can include certifications or classes they need to complete. If you haven't viewed parts one through six yet, it's a good idea to check those out first for better understanding.
In this session, we're addressing how to handle completion dates for in-progress training. We'll implement a system where, once a user completes a training module, the associated completion and expiration dates are automatically populated. You can customize the level of control over this process based on your preferences. You can either restrict actions strictly according to the rules or allow users some flexibility.
One of the initial steps involves securing the combo box to prevent unauthorized changes. This will be achieved by employing some VBA code. Such code can issue background SQL statements to execute update queries, but the goal here is for users to utilize a dedicated form for any changes, enhancing centralized control.
You'll notice a lock symbol indicating that a record is uneditable because it's been "dirtied" - that is, it's in the process of being modified. Before opening this form, a refresh is necessary to avoid outdated information. This can be accomplished by adding a simple refresh command in the VBA editor for specific events.
Once refresh procedures are in place, the next task is to lock certain form elements to prevent alterations. This can be done through the form's data tab by setting properties to lock specific controls, depending on the status of the training.
When it comes to marking training as completed, there's an important update event tied to changing any in-progress status. VBA's After Update event is used here to trigger automatic updates to completion and expiration dates when status changes to completed. The status identifiers are defined in a separate table and are handy to reference when working through this process.
To determine the correct expiration date, you draw information from a designated course table which indicates validity periods. If a course has an expiration date, convert this into a format that can be handled programmatically to update the form accurately. Note that for calculations involving time, it's essential to use the correct parameters in functions like DateAdd.
It's important to test any changes made, ensuring that everything functions as expected. One peculiar issue with Access is its handling of DateAdd and fractional years – as such, converting expiration from years to months can resolve these quirks. This involves simple math to adjust how expiration periods are treated within the system.
If a different status is chosen, such as "in progress" or "expired," there are different considerations and actions to take. These aspects will be covered in the subsequent parts of this series. Meanwhile, if you require additional guidance on related topics like After Update events or functions such as DLookup, NZ, and DateAdd, I have additional resources available on my website.
You can find a complete video tutorial with step-by-step instructions on everything we've discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Locking a combo box in a Microsoft Access form
Setting completed and expiration dates with VBA
Using refresh in Access forms with VBA
Detecting "dirty" records in Access
Understanding double-click events to open VBA editor
Locking fields in Access form to prevent edits
Setting up status change logic in Access
Using Notepad for quick reference notes
Implementing the AfterUpdate event in Access
Using DLookup to retrieve values from a table
Handling expiration logic based on table data
Using NZ function to handle null values in VBA
Adding years to a date using DateAdd function
Converting years to months for date calculations
|
