Employee Training 4
By Richard Rost
14 months ago
Employee Training Tracking in Microsoft Access Part 4 In this Microsoft Access tutorial, we'll continue our employee training series by creating a subform to track employees' roles and the courses they've taken. I'll show you how to bind this subform to a table and use combo boxes to facilitate data entry and comparison between completed and required courses. This is part 4. 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, tracking employee training, employee certifications, Microsoft Access subform, role requirements, expired certifications, continuous form modification, table design in Access, VBA subform programming, combo box configuration, form customization, Access course management, role and class tracking
Transcript
Today is part four of my employee training series where we're tracking employee training. Say that ten times fast. If you haven't watched parts one through three, go watch those first and come on back. Okay, we got our roles and departments, our courses. We put together in the last class the role requirements. So we know for each role what courses it requires: HR 101, HR Level 2, and so on. All right, I put a couple of sales ones in here.
Okay, so today, continuing with that theme, we're going to make a subform on the employee form to show what roles they have. All right, and then we're going to be able to see what classes they've taken. And then eventually we'll be able to see, okay, I need to compare these two lists. What have they taken versus what have they not taken? What might be expired? That kind of stuff. So today, let's start with employees and the roles that they're in.
First, we need to go to the employee form. And down here, we'll put a subform showing what their roles are. Because they could have multiple roles. Probably just one or two, maybe three. I don't know. You know, someone could be trained for HR and sales and customer service. I mean, like me, you know, a small business owner. You know, I've had, at one point, I had 12 employees working for me and I wore all the hats. I had to be able to do sales, fix computers, teach people, all that stuff. So yeah, it depends on the size of the company. The bigger the company, the fewer hats you have to wear.
So we're going to make an employee role subform. Let's go back to my continuous form here. Copy paste. We'll call this employee role subform. All right, design view. Now I'm going to change color up a little bit because actually the other one, the role form, is purple. We can stick with purple for roles. Anything role-related will be purple. This we should have changed the color to something to represent classes. Let's make classes represented by, let's go green.
So we're going to modify the role requirement for this guy. These are courses. So let's make this green. Let's go with a light green here and a little bit darker green. I'll do that. I like to keep all of the same kind of thing the same color. So now if I look at the roles. These are roles in purple. These are courses in green. It just helps the eye, I think.
Okay, so we're going to do employee role sub F. And these are purple. So these are going to be roles again. Now this is a situation where we don't need that. This is going to be a combo box. And all of this stuff could probably go in the footer. Actually, I put the primary in with the regular list of items. But the rest of this stuff can go in the footer.
So let's start by binding this to the employee X roll T. Where are you? Oh, we didn't make it yet. We got to go make it. I keep forgetting. We got these, you know, spreadsheets and stuff made up. But we haven't built the tables yet. So all right, let's build a table real quick. I'm going to close this for now. Right out here. Really easy to do. Table design. And again, you don't have to watch me build the whole table.
All right, real simple. Our auto number, the employee ID, the role ID, start date, end date, qualified date, and notes. I'm going to save that as primary for later. I'll keep it for an optional lesson. Maybe an extended cut later on. Because you have to have it so you can only have one for each employee. And it's a little bit tricky, a little bit of VBA. Maybe we'll do that in the future.
All right. So it's good to have a few bits of sample data in here to do this. I'm just going to put one employee in here. And we'll need to see some roles. Where's the role table? Okay. So let's say me, employee ID one. I am HR level one. We'll put the start date and all that in stuff and then later. And I'm also sales level two. So that's role six. Actually, that should be role ID two. Okay. I'm two and six, which is HR level one and sales level two.
All right. We'll put more employees. And later again, this is easier to do once you've got the form built. Let's go back to the form. Design view. All right. Now we can bind it to that table. There you go. Let's add existing fields. I'm going to bring over. We don't need these two top two things. We're going to bring in all these things. Right now, let's put them on the footer for now.
All right. So the role ID, let's get rid of these. This is going to be a combo box. So we need a combo box with roles in it. So I don't have one built yet. So let's form design combo box. Drop it there. We're going to get this data from the role table. I need that and the description. I don't need the department ID. One nice thing about the codes that we have is it can tell you what the department is.
We're going to sort by description. Next. That's what it's going to look like. Actually, let me think here. Yeah, these don't have the codes in them today. Let's, I'm going to cancel this. Let's go back. A role table. Where are you? Let's see here. Yeah, these don't have that. So let's make a query that joins together the department code along with the role. Because not each one of these has a code in front of it. I mean, they kind of are, but not really.
So I just need the role ID. Actually, we're bringing the role ID and all this stuff into a query. Right. Create query design. Let's bring in the department and the role. And we're going to show the department code and then all of the role data. And we'll call this roll queue or roll queue is fine. Nice, simple. And that's going to look like this. And then we can put this next to this in our combo box.
So we can come in here and go design view. We'll put in here roll with department code. And that will be the department code. And maybe a dash. And the roll t.description. Because there's descriptions in both of those, remember. Okay. You got to tell it which table you're getting that description from. And now we've got a field that looks like this. Which will go very nicely in our combo box.
Okay. So now let's save that. Now let's make the combo box design. This is the kind of like on your toes thinking, on your feet, on your toes, up on your toes, on your feet thinking that you have to do in your building. Sometimes you can take a step back.
All right. So queries, this time roll queue. I want this is actually getting stored in the roll ID. So that'll be first. And then we just need this to display it. That's all we need. We put those two fields together and that. Sort it by this. That's fine. This is what it's going to look like. Now remember we don't have, this is based on a query and not a table. So we don't have the checkbox here that says hide the key columns. We have to manually do that. Just drag it with the zero.
Next, what is the bound field roll ID? Where are we saving that? We're saving that in the roll ID in the junction table. Next, what label do you want? Doesn't matter. We're going to delete it. And get rid of that. And this. I want to make this bigger of course because this is a longer description. I was thinking courses before it was like HR 101. These don't have that. Let's call this guy the roll combo.
Now do you want to put this stuff next to it or do you want to just have this stuff in the footer so you can see it for each one? I kind of like putting it in the footer. I'm going to just put it all down here like this. We'll make this stuff black so we can actually read it. Or you could make it so you can double-click on this and it will open up another form. You know, a single form that has the details in it. This is fine for me. I like this.
Okay. Notes could be bigger. Let's grab another notes field. It doesn't need to be quite that big though. Maybe like. Okay. We're going to select everybody, right-click, size to grid. There we go. We can get rid of this label. Rolls. Right. Rolls. Now I'm hungry. Just kidding. Go. There we go. Save it. Close it. And now we're ready to drop a subform in. Right down here. Where'd you go? Look, drag, drop. Delete the label. And this shouldn't have to be super big but you got this big footer so you got to have at least room for a couple of them there.
All right, let's see what it looks like now. Save it. Close it. Open it. Yeah. I think I want to make this. I think I want to put this stuff in a separate form. Yeah. Let's do that. So we got the roll subform. Let's copy this. Copy paste. Let's make it just employee roll F. We'll make this a single form. I think it's just too squished.
So what I'm going to do here is let's open this up. We're going to move all of this stuff back up into here and treat it like a single form. We don't need the footer anymore. And this can be a little bit bigger. And then we'll just put, you know, you just leave the roll big like that if you want to. Right. And then I just got his dates and stuff on here.
All right, save that. And then what we'll do is we'll go back to the subform design view. Get rid of all of this stuff. Okay. And now we're going to make this so you double-click on it to open the other form.
Now to do that, I usually like to signal it with like a light blue color like that guy. It's just a training issue. If users see a field that's blue like that, you double-click on it and it does something. All right. Here comes our first little bit of programming. Remember I said that we're going to be sprinkling in some VBA here and there. So if you haven't watched intro to VBA, now would be the time.
We're going to double-click on this guy. I go to events and find the on double-click event, which is right there. Now I want to open the other form and go to specifically the one that they click on. Now the value that they're clicking on is a role, but we actually want this employee ex roll ID. That's the specific record in the junction table that we want to go to. Right. That roll for that employee.
So it's going to be do command open form employee roll F comma comma comma where the employee ex roll ID equals the employee ex roll ID on this form. Now this field doesn't exist on either of these forms because we didn't put it there. Sometimes you have to have it there and just have it invisible. But let's see. Sometimes it works. Sometimes it doesn't. Let's see what we got here.
All right. Employees. Let's go to the S R the sales level two double click and it worked in this particular case. Usually you only need it if the other form is referring to that value like across forms. You can't say forms customer F last name unless you actually have that last name. But in the VBA code nine times out of 10, you can get away with this. Even if the field doesn't exist as a control on the form as long as it's in the underlying record set.
Right. Because that exists in the query and the table underneath each of these. Then you can get away with it. Right. So now I can open up this. I would, on this form, I would turn off these things so that they come in here. They can just make changes in here. That's up to you. You might want to still allow them to add stuff or whatever.
Put a start date in, put an end date in, put a qualified date in. Close it. Those are really, I mean, these aren't really necessary. What's necessary is this stuff. You want to see what roles they're in. Because next we're going to do the same thing over here with what classes they've taken. Then we can do another list or a pop-up or something to say, okay, these are the roles you have. These are the classes you've taken. These are the classes you need. What are you missing? Or what's expired?
So that's going to be coming up in the next part. So yeah, tune into my awesome bad times. Or be a member and you can watch it right now because yes, yes, I'm feeling a lot better today than I was yesterday. I started recording yesterday and I just I didn't get a good night's sleep the night before. I don't know if you can tell right now, but yesterday part three was I was just kind of dragging. I was I was I was phoning it in. I was trying to like I really felt like recording but I was just I don't know. I think I got like three hours of sleep the night before. But I feel much better. I feel much better today.
So tune in tomorrow. What's the date today? Let's see, today is this is going to air on Halloween on the 31st, which is a Thursday. So yeah, tomorrow and November 1st will be part five. That's going to do it for part four folks of the TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Creating a subform in Access
Tracking employee roles
Using color coding for forms
Binding forms to tables
Designing combo boxes
Query creation and data joining
Building a query for role display
Using VBA to open forms
Implementing event handling in Access
Managing multiple roles for employees
COMMERCIAL:
In today's video, we're continuing with part four of our employee training series where we're developing an employee role subform! We already have our roles, departments, and courses laid out. Today, you'll learn to create a subform on the employee form to display their roles. Watch as we design a combo box to tidy up and organize our data visually with colors for easy recognition: purple for roles and green for courses. We'll walk through binding the subform to the necessary tables and even sprinkle in a bit of VBA to open detailed forms on demand. Don't miss out on enhancing your database with these new functionalities and stay tuned for part five. 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 main goal of the employee training tracking system being discussed?
A. To manage employee attendance
B. To list all company assets
C. To track courses required and taken by employees in various roles
D. To evaluate employee performance
Q2. Which concept is used to differentiate between roles and courses in the form design?
A. Shapes
B. Text size
C. Colors
D. Font styles
Q3. In the tutorial, what type of form is being designed to show what roles each employee has?
A. A single form
B. A summary form
C. A subform
D. A pop-up form
Q4. What data is directly bound to the employee role subform discussed in the tutorial?
A. Employee attendance data
B. Monthly performance rating
C. Employee x roll T (junction table data)
D. Department management information
Q5. How is a specific role selected within the role form design?
A. Via a text box
B. Using a checkbox
C. Through a combo box
D. By clicking a hyperlink
Q6. What is the purpose of creating a query that combines the department code and role description?
A. To make data transfer faster
B. To generate summary reports
C. To display a full description of roles in the combo box
D. To identify duplicate roles
Q7. What visual cue is used to indicate that a form field can trigger another form upon double-clicking?
A. The field is highlighted in red
B. The field text is underlined
C. The field is colored light blue
D. The field is outlined with a border
Q8. Which programming language feature is introduced for opening a form on specific user actions?
A. SQL script
B. JavaScript event handler
C. Excel VBA
D. VBA event procedure
Q9. What does the instructor suggest doing to accommodate additional role details for an employee?
A. Adding details directly on the main employee form
B. Creating a separate single form for role details
C. Including a detailed summary on a report
D. Using advanced filtering options
Q10. According to the instructor, what is a potential timeline for future lessons in the series?
A. Weekly releases on Tuesdays
B. Monthly webinars
C. Daily uploads
D. Scheduled videos on specific dates
Answers: 1-C; 2-C; 3-C; 4-C; 5-C; 6-C; 7-C; 8-D; 9-B; 10-D
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 focuses on part four of our employee training series, where we continue tracking employee training. If you haven't seen parts one through three, I recommend checking those out first for a comprehensive understanding of the process.
Previously, we covered setting up roles, departments, courses, and role requirements so we know which courses are required for each role, like HR 101 or HR Level 2. Today, our aim is to enhance the employee form with a subform that displays the various roles an employee holds and the classes they have completed. This will ultimately allow us to compare the courses they have taken with those they still need to take, identifying any expirations or gaps.
Let's begin with examining the roles held by our employees. We'll start by adding a subform to the employee form, as many employees might have multiple roles, such as being trained in HR, sales, and customer service. Depending on company size, employees might wear many hats or just a few.
We'll create an employee role subform by duplicating an existing form, renaming it, and adjusting its design. Keeping our forms visually organized helps; for example, roles are shown in purple, and we'll use green for courses. As we adjust design elements such as color, it makes it easier for the eye to differentiate between roles and courses.
Next, we work on setting up the data source for our employee roles subform, which involves creating a table and a query. We'll design a table that includes fields such as auto-number, employee ID, role ID, start and end dates, qualification date, and any notes. This foundational data will be used to organize and display an employee's roles.
With this data prepared, we bind the subform to the newly created table and add necessary fields to it, such as role ID. Implementing a combo box for roles from the role table can help users easily select the appropriate role for each employee. We create a query to compile relevant data, including department codes and role descriptions, which will refine our combo box to display comprehensive information.
Given that the subform might prove too cluttered when displaying all information, we may decide to switch to a single-form view, providing more space to arrange details like roles and dates. We can offer additional functionality by allowing users to double-click a role to open another form with extensive details.
At this point, there's a bit of VBA programming to facilitate the transition between forms. The on double-click event is utilized to open another form specifically focused on an employee's selected role, providing detailed information that enhances usability.
As we wrap up, remember that keeping such layers of data and functionality well-organized will prove crucial as we continue to build out this system. Our future lessons will address how to track the courses employees have completed, match these with the required courses, and determine any outstanding courses or expired qualifications.
You can revisit detailed video tutorials on these processes on my website through the link provided below. Live long and prosper, my friends.
Topic List
Creating a subform in Access
Tracking employee roles
Using color coding for forms
Binding forms to tables
Designing combo boxes
Query creation and data joining
Building a query for role display
Using VBA to open forms
Implementing event handling in Access
Managing multiple roles for employees
|
