Job History 3
By Richard Rost
15 months ago
Recent Job History for Candidates in Access Part 3
In this Microsoft Access tutorial, we'll explore showing the most recent job history for candidates by adding a manual refresh button and implementing a simple VBA solution to automate refreshes when forms are closed. This is part 3.
Members
In the extended cut, we will learn how to identify employment gaps within job history records. I will show you how to detect when there is a period of unemployment lasting more than a set duration, like three months, and how to list these gaps, which can be crucial for job recruiters and employers.
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Most recent job history, VBA refresh code, VBA event handling, debug VBA code, error handling VBA, modal forms Access, Access VBA tutorial, Access command button, Access visual design, command button wizard, manual data refresh, read-only fields, employment gap analysis, Access developer lessons
Subscribe to Job History 3
Get notifications when this page is updated
Intro In this video, we continue with part three of the job history series in Microsoft Access. I'll show you how to keep your job data up to date by adding a manual refresh button using the command button wizard, and then explain how to automate the process with a simple VBA solution in the form's close event. We will also talk about setting fields as read-only, handling potential errors with "On Error Resume Next," and making your form modal to improve the user experience. This is part 3.Transcript Today's part three of my job history series. If you haven't watched parts one and two, you'll find links to them down below. Go watch those then come on back.
Alright, so in the previous two videos, we've got the current and previous jobs displaying down here. We got our big list of jobs, put all their jobs in the system here. And then when you go to one of their records like that, it'll show you what their current and previous jobs are. But what if this stuff changes and you close the form, you want it to come back here and be able to refresh it.
I'm going to show you two ways to do this. Now the first way is a manual effort, but it doesn't involve any programming. We can use one of the command button wizards for that. The second way will involve a teeny tiny little bit of code, literally one line, two lines if you want to be safe. I'm going to show you that in just a minute. But first let me show you how to do it manually.
Let's say you're coming. These are read-only by the way. You can't come in here and change these. These are D look up so they're read-only. In fact, what I usually tend to do with read-only fields like this is I like to make them like a light gray that just tells the user visually, hey, you can't change that stuff. That's just to look up and see only. So now they know they can't change that stuff.
But let's say you come in here and instead of fleet admiral, let's just put a couple of explanation points after the end of it. Close that and now it doesn't refresh. Now you close the form and reopen it, but that's a pain, especially if you're not on the first record. Now you can come in here on the home tab and hit this refresh button and that will then force this form to refresh. It pulls all the data in again. But again, that's a training issue. You got to teach your users where to find it. It's a bit of a pain. And personally, I don't like to let the users have access to the full ribbon.
I disable this ribbon and I talk about this a lot more in my more developer classes, my higher end classes. But what we can do is just make them a solo refresh button right here. So design view and again, this is on the command button wizard drop a button. It's going to be under form operations refresh form data. This is the same thing as it refresh on the ribbon. Next, text refresh is fine or if you want to picture, you know how to do all that stuff. Next, give it a meaningful name refresh button finish and there's your refresh button. I'll put it right. Maybe under jobs or wherever you want to put it.
So now save it close it. If you open it up, go into jobs, make a change. We'll give those explanation points. Close it and then hit refresh. It will refresh that form. Same thing if you're on. Let's go over here. Engineer. Edit. Engineering. Why do you want to close it and then it refresh.
Now, it'd be nice to be able to do this without the user having to hit that manual refresh button. Is there a way when this form closes that it can automatically refresh this one? Yes, it can. That'll involve either a macro or some VBA. Now personally, I prefer VBA. It's not hard. I think honestly, once you learn how to do VBA, it's easier than writing a macro. It really is, in my opinion. I've been teaching this stuff literally for decades. I think VBA is a lot simpler than using the macro editor.
Now, if you want to dip your feet in, if you want to get your toes wet, how's it different toes in, get your feet wet, whichever way that is. Go watch my intro to VBA video. It's about 20 minutes long. It'll teach you everything and you know it gets started. I'm going to show you right now what you have to do. But if you want to learn more, go watch this video too. This will really help you.
So here's how easy this is. You ready? Right click on this guy. Go to design view. Bring up the form properties by double clicking right here. That brings up the property sheet. This is the events tab. All of these events are different actions that fire at different times. There's an on open event that happens when you open the form up. There's an on delete. When you delete something, there's an on click when you click on something. What we're looking for is on close. There's an on close event. This event's going to run. When the former report is closed. Which is when we want this to refresh this guy with this form closes. Hit the dot dot dot button. Now on my system, you'll see this visual basic for applications window pop up. You might get a little window that asks you to choose your builder. There's three different builders. There's a macro builder, the expression builder and the code builder. You want to pick the code builder. In my intro to VBA video, I show you how to turn that little window off. So it always uses the code builder.
This is where we want to be right here. I'm in the form close event for the job form. Now in here, we're going to put one line of code. And it looks like this forms customer, that's a bang or exclamation point forms customer F dot refresh. That's it. That's all you need to do.
In other words, when the job form closes, refresh the customer form. That's it. See how simple this is? The one line of code can add that functionality. And now the user doesn't have to have to hit that refresh button. So save it. Close it. Close it. Now from here, I'm going to go into the jobs form. I'm going to change engineering. Let's just change the fact that engineer right now. Watch, I'm going to close this form. Bam. And it refreshed automatically without me doing anything. You see that? Isn't that cute? Isn't that cool? That's the power of VB. Just little things like that. One line of code here and there.
Now, like I said before, you need two lines of code just to be safe because this could possibly happen. Watch, they open this and they close this form. And then they do this. You'll get an error message because they can't find the customer F because the user already closed it. So what we can do in this case, we can go to debug. We'll fix this. All you have to do is before that line, just say on error resume next. Basically that says if you encounter an error, just ignore it and skip it and continue on. There's lots of different ways to handle error handling and all that, but we're not going to talk about that today. I got a whole separate videos on error hand. But that will just ignore it if this line throws an error.
And again, like I mentioned before, if you really want to prevent that from happening, you can take this form, go to other and now make this modal. Modal means you can't do anything behind the job form until you close it. And in this particular case, I would definitely use that. Save that. Close it. Close it. Let's open up this. And now when I open up the jobs form, this guy is in the front. Notice I can't click anything behind it. There's just some stuff you could do on the ribbon, but again, that's another reason why I actually turn the ribbon on. But I have to finish up with the job form before I can close this guy. So I have to come in here and say, fleet admiral, a division or whatever. And now when I close this, it runs and it refreshes and everybody's happy and everything works and everything's grand.
So that's how you can do the refresh, both without programming with a little button and with program with a little VBA. Now before I let you go, we are going to do an extended cut. I'm going to show you how to find employment gaps. So if they worked from 98 to 2020. And then they didn't have a job until 2023. It'll say, hey, there's an employment gap starting on the state. And then there's another employment gap here. We're going to do three months, I think, is the default that I have it set for. So if it sees a gap of more than three months, it'll put it in a list down here. Because that's important for job recruiters or for employers, what you want to know, hey, why weren't you working for these three years here? What were you doing? You were taking your time to study under the Microsoft Access Guru to learn how to do this stuff.
Speaking of learning, if you want to learn a lot more about access, I got all kinds tons of lessons on my website. My expert lessons are like just what you saw in this three lesson series. I teach you how to do really cool stuff, just learning functions like D look up and NZ and all that stuff without any programming. It's just all functions and events and stuff like that.
Then if you want to learn more than that, I do also have tons of developer lessons for the VBA programmer in you. You want to break out and learn how to program. Tons of lessons for both programmers and non-programmers on my website. Come and check them out.
But the extended cut is for the members of my channel of my website. Silver members and up get access to all of my extended cut videos. Not just this one, all of them. I got lots of them. And gold members and platinum members. Everybody gets some training, some lessons, some free stuff. Check it out. That is going to be your TechHelp video for today. Hope you learned something. Hope you enjoyed this little three-part series. Live long and prosper my friends. I'll see you next time and members. I'll see you in the extended cut.
TOPICS:
Manual form refresh using command button
Creating a command button with wizard
Setting read-only fields to light gray
Using the home tab refresh button
Creating a custom refresh button
Using macros or VBA for automatic refresh
Writing VBA code for form on close event
Handling errors with "On Error Resume Next"
Setting form as modal to prevent access behind
Automatically refreshing forms with VBA
Finding employment gaps in job history
COMMERCIAL:
In today's video, we're continuing with part three of our job history series. We'll cover how to refresh data in Microsoft Access forms, ensuring users always see up-to-date job information. First, I'll show you a manual method using a command button wizard that requires no coding. Then, we'll enhance functionality with a simple line of VBA code to automatically refresh the data when forms close. We'll also discuss error handling and how to make forms modal for added control. Whether you're a VBA newcomer or seasoned pro, there's something here for everyone. 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 are the two methods demonstrated in the video to refresh a form in Microsoft Access?
A. Manual refresh using a command button and automatic refresh using VBA
B. Using a macro command and automatic refresh using SQL
C. Manual refresh using the navigation pane and automatic refresh using a query
D. Using a script to refresh and manual refresh using the database tools tab
Q2. Why are read-only fields visually marked with a light gray color in the demonstration?
A. To make the form design consistent
B. To indicate that the fields are editable
C. To inform the user that they cannot change the data in those fields
D. To improve the loading speed of the form
Q3. What is the main drawback mentioned in the video of letting users access the full ribbon in Microsoft Access?
A. It makes the application slower
B. It requires more programming knowledge
C. It can be a pain for training users where functionality is located
D. It increases the likelihood of data corruption
Q4. What simple action does the VBA code perform when a form closes according to the video?
A. It automatically exits the application
B. It saves the form to a different location
C. It opens a new report
D. It refreshes the customer form
Q5. Which error-handling technique is suggested to avoid VBA errors caused by the closure of forms?
A. On error go to start
B. On error resume next
C. Try catch block
D. Manual error correction
Q6. What is the purpose of making a form 'modal' in Microsoft Access as discussed in the video?
A. To allow simultaneous editing of multiple forms
B. To prevent any other actions in the application until the form is closed
C. To increase the form's visibility
D. To reduce memory usage while the form is open
Q7. According to the video, why are employment gaps important for job recruiters or employers to identify?
A. They help determine the candidate's salary expectations
B. They show potential areas for skill development
C. They can indicate periods where the candidate was studying or unavailable for work
D. They are necessary for legal documentation
Answers: 1-A; 2-C; 3-C; 4-D; 5-B; 6-B; 7-C
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 the Access Learning Zone covers the third part of our series on job history. If you haven't checked out parts one and two, I recommend you do that first, as they lay the groundwork for what we'll discuss today.
In the previous segments, we set up our system to display the current and previous jobs of individuals. This functionality allows us to view specific job records and their history. However, data can change, and you'll want the updates to reflect accurately after closing or modifying a form. I'll show you two methods to achieve this refresh.
The first method is manual and does not require programming. It involves using a command button wizard to create a refresh button. The second method requires a small amount of VBA code. I'll explain both techniques, starting with the manual one.
The job positions are currently read-only, using the DLookup function, which means users cannot modify them directly. I typically use a light gray background for such fields to visually indicate they are uneditable. If a user makes any changes like adding exclamation points to a title, the system will not automatically refresh. Closing and reopening the form will update the data, but this process is time-consuming and can be problematic when not on the first record.
The manual refresh involves navigating to the home tab and using the refresh button to pull new data into the form. However, training users to find and use this can be cumbersome, and I generally prefer not to give them full ribbon access. Instead, you can provide them with a standalone refresh button directly on the form.
To add this button, access the design view and use the command button wizard to select 'Form Operations' and then 'Refresh Form Data.' The button will function like the ribbon's refresh option. Give it a meaningful name, and place it wherever you prefer on the form. Once set up, users can make changes, press the refresh button, and see the form update instantly.
A more seamless user experience is to automate this process without requiring manual input. By using either a macro or VBA, the form can automatically refresh upon closing. I recommend using VBA since it's straightforward and simpler once you understand the basics. I've taught this for decades and find VBA more efficient than the macro editor.
If you're new to VBA, my introductory video explains its fundamentals. For today's task, you just need to access the design view and the form properties, focusing on the events tab. The event of interest is 'On Close,' which triggers when the form closes. You'll input a single line of code that refreshes the customer form when the job form closes.
This simple automation eliminates the need for a manual refresh, greatly improving user convenience. After implementing and testing it, you'll see the form update automatically when you close it, making everything more efficient and user-friendly.
To prevent potential errors when a form is not open, add a second line of code: 'On Error Resume Next.' This ensures the system continues smoothly even if a referenced form is already closed. Additionally, setting the form as modal prevents users from interacting with other forms until the job form is closed, enhancing flow and reducing user errors.
This tutorial not only demonstrated how to perform refreshes with and without programming, but it also set the stage for discussing more advanced topics in our extended cut. We'll address how to find and highlight employment gaps, which is crucial for recruiters.
For those interested in diving deeper into Access, I offer detailed lessons on my website, covering various functions and VBA programming. The extended cut is available to channel members, offering even more insights and techniques.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Manual form refresh using command button
Creating a command button with wizard
Setting read-only fields to light gray
Using the home tab refresh button
Creating a custom refresh button
Using macros or VBA for automatic refresh
Writing VBA code for form on close event
Handling errors with "On Error Resume Next"
Setting form as modal to prevent access behind
Automatically refreshing forms with VBA
Finding employment gaps in job history |
