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.