Enabled 2
By Richard Rost
9 months ago
Control Data Entry with the Enabled Property Part 2
In this Microsoft Access tutorial I will show you how to control the enabled property of a field using VBA for more advanced logic, including how to check the current user's Windows username to allow specific users to override field settings, how to use events like After Update and On Current, and how to reset values based on your rules. This is part 2.
Members
There is no extended cut, but here is the file 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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, enable field with VBA, conditional formatting vs VBA, disable field based on another field, form on current event, form after update event, environment variable username, user-based field enable, credit limit logic, check state subroutine, prevent field edit by state, setting field to 0 with VBA, restricting data entry, form load VBA, simple user logon logic, field properties continuous form, hide field continuous form, access control by user, security with environment variables
Subscribe to Enabled 2
Get notifications when this page is updated
Intro In this video, we'll continue our Enable and Disable Fields series for Microsoft Access by using VBA to control when a form field is enabled or disabled based on the value of another field and additional user conditions. I will show you how to write a subroutine, use the On Current and After Update events, check the logged-in Windows user, and apply multiple conditions to set field properties. We'll also cover clearing unwanted values automatically and discuss why this technique does not work in continuous forms. This is part 2.Transcript Today is part two of my Enable and Series, part two of two.
Yesterday in part one, I showed you how to disable or enable a field based on the value in another field using just conditional formatting. But there is only a limited amount of stuff you can do with that. If you want more complex rules or complex logic, you'll have to use a little bit of VBA. So today I'm going to show you how to do that.
But first, some prerequisites.
Obviously, if you haven't watched part one yet, go watch part one first so you know what we're doing. You would be surprised at the number of comments I get from people who jump into part four and they're like, oh, there's two. Go watch parts one through three, then, if you're in part four.
All right, see, big two right there, part two. That's why I make it nice and big and colorful. Don't jump in the middle.
Also, we will be using VBA. This is a developer level video. So if you've never used VBA before, don't be scared. Go watch this first. It's about 20 minutes long. It'll teach you everything you need to know to get started.
We're going to be using an if then statement. We're going to be using the on current event, which is what runs when you move from record to record, including the first one. We're going to be using an after update event, which is what fires when you change the value of a field. Go watch this video.
We're not going to be creating our own function, but we are going to be creating our own subroutine, which is very similar. I know I have to make a video for creating a sub, but this will tell you what you need to learn.
I'm going to use an extra condition of we're going to allow a manager to be able to change that field even if they're not from Florida. I'm just going to use a basic user log on. We're going to look at the user that's logged on to the computer with the system environment variable username. This video explains how to do that. You can use any condition that you want, or if you have your own built in login system, that's fine too. Go watch this if you're curious.
That should be it today for the prerequisites. If you haven't watched any of these, go watch those first and come on back. These are all free videos. They're on my YouTube channel. They're on my website. You'll find the links down below in the description.
All right. Here I am in the database we created yesterday.
Now, the first thing that I'm going to do is get rid of the conditional formatting that we put in here because the two are incompatible. In fact, you know what I'm going to do? I'm going to leave the conditional formatting in this customer list form because the VBA method does not work in the continuous form.
If you change the property of a field, it changes all of them. I'm going to leave this in this form so that my gold members have that when they download the template. We're just going to change this guy. I'm going to go back to the form at conditional formatting and we're just going to delete that rule. That puts this guy basically back to normal.
Now we can see who's logged on. Let me close this for a second here. We can see who's logged on by looking at the environment variables. So let's put in the main menu when this guy opens event. We're going to use the on load event.
I did not cover this in the prerequisites. I know the on load event runs when the form loads up. Here's my VBA window. Resize it and move some things around here. Hold on. Fix that and fix this.
Now in the form load event, we can say in here status greetings and then in username. That grabs the username from the Windows system environment. Save it. Close it. Close it. Open it. Greetings. Amma. Yes, my Windows username is Amma. Don't ask. It's a long story. I think I talked about it in that other video.
Status is my little box here that just writes stuff to this box instead of having to use message box all the time. I have a separate video for that. I'll put a link down below if you're curious. So we can look at this and determine if this person can override the settings that I got in here.
So how do we do that? Well, we're going to use a couple of events to control when this thing gets enabled or disabled.
The first event we would use would be the after update event for the state field. So when the user changes the state, it'll update whether this is enabled or disabled. The second place we'd have to put it is up here in the after update event, because the after update event runs when you move from record to record, including the first one when the form loads.
But let's start here in the after update for state. So go to state, go to events, go to after update, hit the dot, dot, dot button and that puts you in here.
What are we going to do in here? Well, we're going to type in check state.
What's check state? Well, we have to write it. We're going to make our own subroutine, private sub.
What's it doing? It's doing a sub and a function. They're both procedures. A sub does not return a value, a function does. That's the difference.
Private sub check state, and all this guy is going to do is determine whether or not that credit limit is enabled.
So what are the conditions for the credit limit being enabled? Well, the state's got to be Florida and we're going to check the user. So we'll say if state equals Florida or the environment of username is equal to me, Amma Kerr, then credit limit dot enabled equals true. Otherwise, credit limit dot enabled equals false.
Do we have to check that null condition? No. Because if it's null, it's not going to be equal to Florida. So if either one of these conditions is true, then that field will be enabled.
Debug compile once in a while. Come out here. Close it. Save it. Open it.
All right. That one's good. That one's good. Wait, they're all good. That's because user Amma Kerr is logged on.
So let's close that. Let's go back to our editor. Save it. Close it. Close it. Open it.
Florida's good. Oh, oh, oh, we're not good. What's going on here? Guess what? I only put that in one spot. I only put that in the after update event. We have to also put it in the form current event. That's why it's not running as we move from record to record. I make these mistakes intentionally to teach you sometimes.
So go to the on current event, which is click on the form properties, event, on current. Now we do not have duplicate code in our database. We never do that. We put in here check state, and now that will run the same code down below.
Save it. Debug compile. Good enough. Come down here. Close it. Close it. Open it. Look at that. There we go. I was blocked. Florida's good. Blocked. See the null condition is blocked. It's not Florida. Good, good, good, good. All right. Everybody's good.
Let's test changing it. Go over to this IO1 here and change it to Florida. Now it's unlocked.
One thing you might want to prevent people from doing is changing it to Florida, putting a credit limit in, and then changing it back to whatever it was before. Now there's a credit limit in there, and it's locked again. So what you might want to do is come in here and say in the after update event, if state is not Florida, then credit limit equals 0. You would also want to check the environment variable too. So if the state is not Florida, and this is not true, and that's not true. So if both of those conditions are not true, then set the credit limit back to 0.
So if I come in here and do that trick, then make it Texas, it puts it back to 0.
Obviously, I'm just using this as the real simple user log on mechanism. You could put whatever logic you want in here, and make an is manager function or whatever you've got going on. I'm just showing you the simplest way you can check to see what user is in the database.
Of course, as I say in the environment video, this can be spoofed. Someone can easily change this if they know how to use the OS commands. If you want to set up real security for your Microsoft Access database, check out my security seminar. I show you how to set up user and group accounts, manage the workflow of your database, control who has access to what objects, forms, or reports, and lots more. Check it out. There's the link.
One more thing, this type of thing will not work on a continuous form, because if you change the properties of a control, it changes all of them. There are some tricks you can play. For example, in this video, I show you how you can hide a field in a continuous form. You could do something similar with this, but it is not exactly the same. But you can check the state and see. It's a trick, but it works.
Aside from that, that should give you a pretty good idea how to do what you need to do.
So that's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like.
Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link. YouTube is pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on, and did I mention it's completely free? If you like level one, level two is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month. Yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.Platinum members get all of the previous perks, plus they get all of my beginner courses, all of them from every subject. You also get one free advanced or developer class every month after finishing the expert series. You can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Removing conditional formatting to prepare for VBA enable disable logic
Displaying the current user with Windows environment variables
Using the form's On Load event to run VBA code
Creating a Private Subroutine in VBA
Writing a subroutine to enable or disable a control
Using If Then logic to check field values and user status
Using the State field After Update event to trigger VBA
Utilizing the Form Current event to refresh field status for each record
Checking multiple conditions before enabling a field
Automatically resetting a field value based on state and user
Preventing unwanted data entry by programmatically clearing a field
Explaining the limitations of VBA control properties on continuous forms
COMMERCIAL:
In today's video, we're continuing with part two of our Enable and Disable Fields series for Microsoft Access. You will learn how to use VBA to enable or disable form fields based on another field's value, with more advanced logic than conditional formatting allows. I'll show you the prerequisites, working with the On Current and After Update events, creating your own subroutine, and using user information from Windows to set conditions for enabling fields. I'll also cover how to handle exceptions, resetting fields, and explain why this approach does not work for continuous forms. 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 advantage of using VBA instead of conditional formatting to enable or disable a field in Microsoft Access?
A. VBA allows for more complex rules and logic.
B. VBA works faster than conditional formatting in all cases.
C. Conditional formatting cannot disable fields.
D. Conditional formatting can only be used on reports.
Q2. Which event in Access runs when you move from record to record, including the first one?
A. On Load event
B. On Current event
C. After Update event
D. On Open event
Q3. Why does the VBA solution for enabling or disabling a control not work properly on continuous forms in Access?
A. Continuous forms do not support VBA code.
B. Conditional formatting overrides VBA in continuous forms.
C. Changing the property of a field in a continuous form changes it for all records.
D. VBA cannot access form controls in continuous forms.
Q4. What is the correct way to determine the logged-in user in the example shown in the video?
A. Check the Access database login table
B. Use the Windows system environment variable "username"
C. Require the user to enter their name on the form
D. Use a hidden field on the form
Q5. When using VBA to enable a field based on conditions, which two events should you attach your subroutine to for it to work properly?
A. On Load and On Click
B. After Update and On Current
C. On Open and On Activate
D. After Insert and After Delete
Q6. What is the essential difference between a VBA Subroutine (Sub) and a Function?
A. A Sub returns a value, a Function does not
B. A Sub cannot be called from an event, but a Function can
C. A Sub does not return a value, a Function does
D. There is no difference between them
Q7. In the example, what logic is used to enable the Credit Limit field?
A. The Credit Limit is enabled if the user is an administrator only
B. The Credit Limit is enabled if State is Florida OR the user matches a specified username
C. The Credit Limit is disabled if State is Texas
D. The Credit Limit is enabled if no value is present in the State field
Q8. What security warning does the video give regarding using the environment variable "username" as a user check in Access?
A. It is impossible to retrieve the username in Access
B. The username value can easily be spoofed or changed using operating system commands
C. It will only work on Windows 11
D. The username is encrypted and cannot be used
Q9. What is a potential issue if you allow users to input a Credit Limit for Florida, then change the state to something else?
A. Nothing will happen; the Credit Limit stays the same
B. The field will automatically clear
C. An error will occur and crash Access
D. The Credit Limit will remain unless code resets it to zero
Q10. If you want to reset the Credit Limit when the condition is no longer met, what should your code do?
A. Set Credit Limit field to zero if the state is not Florida and the user does not match
B. Set Credit Limit to null for all users
C. Display an error message and stop the form
D. Set Credit Limit to a random value
Q11. What does the 'After Update' event refer to in Access VBA?
A. When the form is closed
B. When a user opens the form
C. When a user changes the value in a control/field
D. When the form is saved
Q12. Why is it important to avoid duplicate code when adding logic to multiple events?
A. Duplicate code increases the speed of Access databases
B. Duplicate code ensures security across your forms
C. Duplicate code makes maintenance harder and increases risk of errors
D. Duplicate code is required for macros to function
Q13. According to the video, what is the best way to handle logic that needs to run from multiple events (like After Update and On Current)?
A. Write the logic directly in each event
B. Use macros instead of VBA
C. Write a single subroutine and call it from both events
D. Only use the logic in the After Update event
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-D; 10-A; 11-C; 12-C; 13-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 Access Learning Zone demonstrates how to enable or disable a field in a Microsoft Access form using more advanced techniques than just conditional formatting.
In part one, I discussed how to make a field enabled or disabled based on another field's value using only conditional formatting. That works well for simple scenarios, but when you need more complex rules—such as checking multiple conditions or combining several pieces of logic—VBA is essential. In part two today, I'll walk you through exactly how to make this work with code.
Before going further, I recommend watching part one if you have not already. You would not believe how often I get questions from people jumping into the middle of a series without seeing what came before. The previous video lays the groundwork for today's lesson, so it is important to cover those first steps.
Because today's lesson uses VBA, this is aimed at developer-level users. If you are new to VBA, do not worry—I have a separate introductory video to help you get started. It is about 20 minutes and covers all the basics you need. I also reference other videos throughout this tutorial that help explain the events and user logic I'll be using today. These are all freely available on my YouTube channel and my website.
Now, let's get into the database we created in the previous lesson. The first important point is that the VBA method we are covering does not work in continuous forms. If you change a property (like Enabled) for one field in a continuous form, it changes it for all of them. That is why I leave the conditional formatting in place in the customer list form. However, for today's single record form, I'm going to remove the conditional formatting so we don't have a conflict. I simply delete the rule, putting things back to normal.
To make decisions based not only on field values but also on which user is logged in, we can pull the username from the Windows system environment. This is accomplished by using the On Load event of the form. Even though I did not cover that event in my prerequisites, the On Load event runs when the form first opens.
You might see me use my custom "status" box in examples. This is just a simple textbox I use to display messages on a form instead of popping up message boxes. I have a separate video explaining how to set that up if you are interested.
Our goal is to enable or disable the Credit Limit field based on two factors: the value in the State field, and also whether a particular user (for example, a "manager") is logged in. In this case, I am using my Windows username as an example of a manager override. This way, if the user meets either of these criteria (State is Florida, or logged in as a manager), they can change the credit limit.
To control when fields get enabled or disabled, we must respond to changes as they happen as well as whenever records are loaded. That means using event handlers for both the After Update event of the State field and the On Current event of the form. The After Update event fires every time the State field is changed, while the On Current event runs whenever the user moves between records (including when the form is first opened).
Instead of duplicating code for each event, I create a subroutine called CheckState. The difference between a subroutine and a function in VBA is that a subroutine does not return a value, but a function does. The CheckState subroutine checks the State field—if it's Florida, or if the user is identified as the manager, then the Credit Limit field is enabled. Otherwise, it remains disabled. There's no need to explicitly check for nulls since null will never be equal to Florida.
I recommend compiling your code periodically to catch mistakes early.
Once the subroutine is set up, I make sure to call it in both the After Update event for the State field and the On Current event for the form properties. This way, the code runs both when a record is changed and when a new record becomes current.
After this is implemented, the form's behavior matches our business rules: the Credit Limit field enables for Florida residents and for the designated manager regardless of state. Tests confirm that changing the state to Florida allows editing the field, while changing it back disables it.
However, there is a potential loophole—users could temporarily switch the state to Florida, set a credit limit, then revert it back, leaving a value that should not exist. To handle this, I recommend adding logic that resets the credit limit to zero if the conditions are no longer met (that is, when the record is no longer Florida and the user is not authorized). That prevents users from working around your business rules.
It is important to note that using the Windows environment variable for checking the username is only a basic security measure. Anyone with enough knowledge could spoof this. If you need strong security in your Access database, you should set up real user and group accounts and permissions. I cover how to do advanced security in my full security seminar, so check that out if you need robust access control.
If you want to use similar rules in a continuous form, it gets trickier, because changes to field properties affect all rows. However, there are some workaround techniques, such as hiding fields conditionally, which I explain in another video.
I hope this tutorial helped clarify how to use VBA to control the enabled state of fields on your forms with custom business logic. You can always find a complete video tutorial with step-by-step instructions for everything I covered today on my website at the link below.
Live long and prosper, my friends.Topic List Removing conditional formatting to prepare for VBA enable disable logic
Displaying the current user with Windows environment variables
Using the form's On Load event to run VBA code
Creating a Private Subroutine in VBA
Writing a subroutine to enable or disable a control
Using If Then logic to check field values and user status
Using the State field After Update event to trigger VBA
Utilizing the Form Current event to refresh field status for each record
Checking multiple conditions before enabling a field
Automatically resetting a field value based on state and user
Preventing unwanted data entry by programmatically clearing a field
Explaining the limitations of VBA control properties on continuous forms |
