Gray Out Fields
By Richard Rost
4 years ago
Gray Out Fields Based on A Selected Value in Access
In this Microsoft Access tutorial, I will show you how to gray out fields based on the value of another field, such as a checkbox.
Randall from Seattle, Washington (a Platinum Member) asks: Is there a way that I can gray out certain fields on my customer form? There are certain types of information we only ask in certain situations, like if the customer has a policy with us. If not, I want those fields grayed out.
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, grey out fields, make field inactive, locked, enabled, Prevent users from changing data, How to Gray Out a Check Box in Access, Grey out fields on form, Grey out a Text box, Grey out fields in access form depending on field selection, Can I make a field inactive until another is checked
Intro In this video, I will show you how to gray out or disable fields on a form in Microsoft Access based on the value of another field, such as making specific controls inactive if a customer is not active. We'll walk through using simple VBA code to enable or disable fields dynamically, discuss the differences between locking and disabling controls, and see how to use the After Update and On Current events to ensure the correct fields are always grayed out depending on the current record.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost, and in today's video I am going to show you how to gray out fields based on a selected value in Microsoft Access.
Today's question comes from Randall and Cianna Washington, one of my Platinum members. Randall says, is there a way that I can gray out certain fields on my customer form? There are certain types of information that we only ask in certain situations, like if the customer has a policy with us. If not, I want those fields grayed out.
Well yes, Randall, graying out a field is called disabling the field, and if you want to do it based on some other values in the form, it is going to involve a tiny bit of programming. Let me show you how it works.
If you have never done any VBA coding before, go watch my intro to VBA video. It is about 20 minutes long, and it teaches you all the basics. VBA is not hard. After that, go watch my If Then video so you know how to write an If Then statement. You will need to know what the After Update event does, so when you click on a value it will update something else. Go watch that video.
These are all free videos by the way. They are on my YouTube channel, and they are on my website. Go watch these. You will need the On Current event, so go watch that video too. And finally, go watch my Lock versus Enable video. This is an old tip video; I have to update this one. I'll put links to all these videos down below. You can click on them to watch.
Once you have finished all those, come on back and I will teach you how to put this all together to gray out fields.
So here I am in my TechHelp free template. This is a free database you can download a copy of from my website if you want. Here is my customer form.
Now, Randall is dealing with customers with insurance policies, but let's say we have customer information here, and if "Is Active" is unchecked, we want to disable these fields over here. Let's say we only bother asking these things of active customers, which would be, Randall, in your case, the same thing as if the customer does not have a policy. You simply do not ask these questions; we will gray those out.
So what do we do?
Let's go into our design view, and then we will bring up our properties for the Is Active box. Right over here, under the event tab, you get On Click. I prefer using After Update myself. In this case, find the dot dot dot button that will bring up our Code Builder. Now I am in Is Active After Update.
So right here, I am going to say if Is Active, then we will make those fields enabled. What fields are we dealing with here? Let's take a look. We have Family Size, Customer Since, and Credit Limit.
So I will say right here:
FamilySize.Enabled = True
CustomerSince.Enabled = True
CreditLimit.Enabled = True
Else, if that is not the case - if Is Active is False. Remember, if Is Active, the default is equals True, so you do not have to specify equals True; Access assumes that. So if I uncheck that and Is Active is False, I want to do the same thing in reverse. I want to set the enabled property equal to, guess what, False.
Copy, paste, paste. Save it. Let's come back over here.
I am going to close that and open it back up again. Now watch: if Is Active, they are enabled. If it is not active, look at that, they get grayed out - they are disabled.
If you watch my other video, you will know that disabled is different from locked. You can also lock these, which means the field is still there, and you can still kind of work with it, but you cannot change the value. If you disable it, these fields are completely grayed out and disabled; you cannot even right click on them and do things like search or filter on them. These fields are literally locked out - disabled.
I do not want to say lock because locked is different. Locked means it is still there, you can still kind of click on it and copy and stuff, but you cannot change it.
You can lock it and gray them out - just change the Back Color property. Or you can disable them completely by setting the Enabled property to False, if that is what you want. And if I want to activate it, you just activate it like that.
Now, this is only half the game, because if you noticed earlier, when I first opened this form, look at that: Is Active is False, but these are still enabled. That is because the event only runs when this box changes value. So you need to also update that setting when you load the form up or when you move from record to record. For that, we use the On Current event.
On Current event: right, click design view, go to your form properties, event, find On Current, right there dot dot dot. Now this will run - the form current event will run when you move from record to record or when the form is opened.
Now, you could copy and paste this like this, but I do not like having duplicated code in my database. So I am not going to do that. There are two ways you can handle the situation. You could have Form_Current call this subroutine like this - copy, paste. So when Form_Current runs, it will then run IsActive_AfterUpdate, and that will work just fine. However, you may later add stuff in here that you do not want affecting up here. So what I like to do is make a separate subroutine out of this. You are going to make your custom subroutine right down here. Private means only this form can use it. Sub as opposed to a function.
Private Sub DisableFields
So we make our own subroutine called DisableFields. Cut this stuff out, paste it in here. Now we will just call DisableFields from both of these spots - there and there.
So the Form_Current event and the After Update event will both run DisableFields.
Here we go. Close it, close it, save changes. Yes, open it up. Move from record to record. Look at that. It disabled that one. Same. And that is how that works. That is how you can gray out those fields. So if the person here does not have a policy, gray it out. If they do, look at it. Same thing that you need, Randall.
I cover more stuff like this in my Access Advanced Level 2 class. More with the On Current event. We do a lot with locked and enabled - lots of stuff in here. If you want to learn more Access, I will put a link to this down below. And if you have any questions, feel free to post them in the comments section down below the video.
Thanks for watching and I hope you learned something. We will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video, and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What does "graying out" fields on a Microsoft Access form actually do?
A. It hides the fields from the user
B. It disables the fields so users cannot interact with them
C. It highlights the fields for editing
D. It deletes the fields temporarily
Q2. Which property should you set to False to gray out (disable) a field in Access?
A. Locked
B. Visible
C. Enabled
D. ReadOnly
Q3. Why is using the Enabled property different from using the Locked property for fields?
A. Locked fields are hidden, enabled fields are not
B. Enabled fields can still be edited, locked fields cannot
C. Disabled fields (Enabled=False) cannot be interacted with at all, while locked fields can be selected and copied but not edited
D. There is no difference
Q4. What Access event is recommended for updating the enabled status of fields when a checkbox is changed?
A. On Click event
B. On Load event
C. After Update event
D. On Delete event
Q5. If you want to make sure your fields are enabled or disabled correctly when moving between records, which event should you use?
A. On Load event
B. On Open event
C. On Current event
D. Before Update event
Q6. What is a recommended coding practice, according to the video, when you need to use the same code in multiple event procedures?
A. Duplicate the code in each event
B. Write the code in a separate subroutine and call it from each event
C. Ignore the duplication
D. Use only global variables
Q7. In the code example, if IsActive is checked (True), what should the code do to relevant fields?
A. Set their Enabled property to False
B. Set their Visible property to True
C. Set their Enabled property to True
D. Delete them
Q8. Why should you use the On Current event along with the After Update event for this functionality?
A. The After Update event only runs when the value changes, not when the form opens or moves between records
B. The On Current event is faster
C. The After Update event makes the field invisible
D. On Current event is used for filtering only
Q9. What is the proper way to reference a field called FamilySize in VBA to enable it?
A. FamilySize.Value = True
B. FamilySize.Enabled = True
C. FamilySize.Locked = True
D. Me.FamilySize.Visible = True
Q10. If you want to create a subroutine that only the form can use, what keyword should you use to declare it in VBA?
A. Public
B. Static
C. Private
D. Shared
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-B; 7-C; 8-A; 9-B; 10-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 video from Access Learning Zone demonstrates how to gray out fields in a Microsoft Access form based on a selected value. This particular scenario was inspired by a question from Randall, who wanted to know if certain fields in a customer form could be grayed out unless the customer meets a particular condition, such as having a policy with his company. Essentially, the goal is to make some form fields inactive, or "disabled," unless the customer record calls for it.
Disabling a field in Access involves setting a field's "enabled" property to False, which prevents users from interacting with that field entirely. To adjust this property in response to changes elsewhere on the form, you will need to use a bit of VBA code. If you haven't worked with VBA before, I recommend reviewing my introductory videos on VBA basics, If Then statements, the After Update event, and the On Current event. I also suggest watching my tutorial on the difference between locking and enabling fields. All of these resources are free and available both on my YouTube channel and my website.
Once you're comfortable with those concepts, the process is straightforward. In my example, we're working with a customer form from the free TechHelp template available on my website. Suppose you want to only ask for additional details if a customer is "active." If the "Is Active" checkbox is unchecked, certain fields like "Family Size," "Customer Since," and "Credit Limit" should be disabled. This way, inactive customers will have these fields grayed out, ensuring the form only shows relevant fields based on the current data.
To set this up, you first access the design view of your form and open the properties for the control that determines the logic—in our example, that's the "Is Active" checkbox. The "After Update" event is the right place to make changes, because it triggers whenever the checkbox is clicked or its value is updated.
Once in the correct code window, you add logic to check if "Is Active" is True. If it is, you enable the relevant fields. If not, you disable them. This ensures that when a customer is inactive, fields that are not applicable to them are grayed out and uneditable.
It's important to note the distinction here between "locked" and "disabled" fields. Locking a field allows users to view and copy data from it but prevents changes. Disabling a field, on the other hand, grays it out completely and renders it unresponsive to user actions like right-clicking, searching, or filtering. If you want, you can combine locking a field with a back color change for a grayed-out appearance, but disabling is generally more restrictive.
There is one more step to make this behavior consistent. The logic above only runs when you manually change the checkbox. However, it doesn't account for when the form is first loaded or when navigating between records. For that, you need the On Current event for the form, which runs every time the form is loaded or the user moves to a different record.
Rather than repeating the same code in both the After Update event and the On Current event, the best practice is to move this logic into a separate subroutine—say, one called "DisableFields." You then simply call this single subroutine from both event locations. This approach keeps your code organized and easier to maintain in the future.
On testing, you will see that your chosen fields will automatically gray out based on the active status of the customer, both when updating the checkbox and when moving between records. This matches the requirement that certain questions are only asked of certain customers.
I cover more advanced topics including the On Current event, working with locked and enabled properties, and other useful techniques in my Access Advanced Level 2 class. If you're interested, you'll find a link on my website. Feel free to ask questions in the comments section below the video if you need help with anything specific.
For those interested in supporting Access Learning Zone, memberships offer several perks. Silver members and above get access to all extended cut TechHelp videos and a free beginner class each month. Gold members receive downloadable sample databases, access to my Code Vault, higher question priority, and a free expert class monthly. Platinum members get all of these as well as access to all full-length courses for every subject covered, including Access, Word, Excel, and Visual Basic. Each membership level comes with its own benefits, so you can choose what fits you best.
Don't worry if you're not interested in signing up, though. These free TechHelp videos will keep coming as long as there's an audience watching.
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 Graying out fields using the Enabled property in Access
Disabling fields based on another field's value
Using the After Update event to trigger changes
Identifying field names to enable or disable
Writing VBA code to enable or disable multiple fields
Difference between Enabled and Locked properties
Setting the Enabled property dynamically in VBA
Creating a custom subroutine to avoid code duplication
Calling a custom subroutine from multiple events
Using the Form's On Current event to update field states
Ensuring fields are disabled or enabled when switching records
Implementing best practices to manage field interactivity in forms |
