Enabled
By Richard Rost
6 months ago
Allow/Disallow Data Entry with the Enabled Property
In this Microsoft Access tutorial I will show you how to enable or disable a field on a form based on the value in another field using conditional formatting, all without any programming or VBA required. You will learn how to set up rules so, for example, only customers from Florida can have a credit limit, how to handle null values, and how these rules work in both regular and continuous forms. This is an expert-level tip using the "Expression is" feature in conditional formatting.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, conditional formatting expression, enable disable field based on value, disable form field by state, credit limit field Florida only, expression is conditional formatting, enable property without VBA, conditional formatting null values, disable fields continuous form, restrict data entry by state, macros for enabling fields, update query for field values, conditional formatting design view, customer form field enable, disable credit limit non-Florida
Subscribe to Enabled
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today I am going to show you how to enable or disable a field based on a value in a different field. For example, let's say you want to make it so you only give credit limits to customers from Florida. So if their state field is anything but Florida, we are going to disable the credit limit field. We are going to cover that in today's class.
This is an expert level class, so we can do this with no programming, no VBA required.
What do we need to know before we do this, though? You are going to need to use conditional formatting, and specifically, we are going to have to use the "Expression is" part of conditional formatting. This is why I consider this an expert level tip. It's a little bit more than just the basics.
Go watch both of these videos first and then come on back. You will find links to both of those videos in the description below this one.
Here I am in the TechHelp free template. This is a free database you can grab off my website if you want to. If I go into the customer form, here is where I'm going to say if this is anything but Florida, I want to make this disabled.
Right click, Design View. Open up the properties for credit limit. Go up to Format, Conditional Formatting. New Rule, and this is where we have to change this to "Expression is" and in here we're going to say state is not equal to that. That is a greater than and a less than, or less than and a greater than, and then in quotes, Florida. Just like that.
Be careful here, because in some older versions of Access you need to put the square brackets around state like that, or it will think that you're typing in an actual string, which you're not. Newer versions seem to have fixed that, but in older versions you have to be careful.
Now, this little guy over here that says Enable, you want to click that. Normally, this field will be enabled unless the state is not Florida; then it will disable it.
I'm going to copy this to my clipboard because we are going to use it again in a minute.
Hit OK, hit Apply, hit OK again, close it, save it, open it. This one's enabled. If I go to the next record, look at that, it's disabled. Can't click on it, can't do anything with it, you can still see it.
If you've got other values in your table that don't match, you are going to have to go in and change them. Open up your customer table, come over here and just delete anybody, set it to zero if they're not from Florida. You can use an update query. I have a video on that; I will put a link down below.
It's not going to change the data art in your table, but it will make it so that only people from Florida right now can do that.
The nice thing about conditional formatting is it updates as soon as you change that field. So if I make James Kirk from Florida and hit Tab, immediately it becomes available. That's pretty cool. The same thing works in continuous forms.
It's one of the nice things about conditional formatting. Go to Design View, go to Credit Limit, Format, Conditional Formatting, New Rule, Expression, "Expression is." Then I am going to paste in that same formula, hit that Enabled button, hit OK, OK, save it, close it, open it, boom. You can see only the people from Florida.
Here we have an issue here. Look, this one's still available even though he's got a blank value there. You also have to be careful to take into consideration null values, because if you compare null with anything, the result is null. It's not true or false. It's very weird. Null math is weird. In fact, I have a whole separate video on null math.
For this, make sure you come into here. Let's go back into the conditional formatting for it. We have to say "Expression is state is not Florida or is null state," just like that. Copy that again, hit OK, hit OK, save it, close it, open it, and there we go. Now it's blanked out, and make sure we put that in our other one too.
Where's our guy in here? Let's see, where is he at? I can't, yeah, see. I did not catch that when I was going through the first time doing this. I did not see that until we got to the continuous form. Sometimes even I have to throw out ables in these videos.
Credit limit, Format, Conditional Formatting, open up and edit the rule, and paste it in there. OK, OK, save it, close it, open it, find the card. Sorry, the card, you're not a Florida resident.
Now, that's great if your needs are simple. If this field is just simply based on another field. What if you want to get more complicated than that? What if, let's say, you've got your own login in this database, and you want to enable managers to be able to still go in there and edit that value? To do that, it's a little more complicated. It is going to involve a little bit of programming, and we'll cover how to do it with VBA in tomorrow's video.
Tune in tomorrow, same bat time, same bat channel, or you can watch it right now if you remember. That's one of the benefits of membership: you can see videos as soon as I post them. You do not have to wait for them to be released to the rest of the public.
If you want to learn more about the enabled property and you do not want to get into programming, in my Access Advanced Level 2 and Level 3 classes, I cover more with the enabled property.
You need a little bit of programming to do much more with it than what I just showed you, but you do not need VBA programming. You can do it just with macros. My advanced series is all about macros. It's six lessons to cover macros and some basic automations and things you can do with just macros.
In fact, what we will do is I will show you how to take it and make it so that you disable any invoices that are marked paid. Once it's paid, you can't change it unless you get the manager password.
We teach all that stuff in Access Advanced Level 3. No programming required.
I know a lot of people are afraid of VBA, but it's not something to be afraid of. It's really easy.
That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part two.
TOPICS:
Enabling or disabling a field based on another field's value
Using conditional formatting in Access forms
Applying 'Expression is' rules in conditional formatting
Disabling a control when a field does not equal a specific value
Handling bracket notation for field references in different Access versions
Setting the Enabled property using conditional formatting
Testing enabled and disabled fields on forms
Immediate updates with conditional formatting when field value changes
Applying conditional formatting in continuous forms
Dealing with null values in conditional formatting expressions
Editing and updating conditional formatting rules for controls
COMMERCIAL:
In today's video, I will show you how to enable or disable a field in your Access forms based on the value in another field, all without any programming or VBA required. We are going to use conditional formatting with expression rules, learn about handling special issues like null values, and see how this works in both single and continuous forms. If you want a quick way to make only certain records editable, like giving credit limits only to customers from Florida, this video is for you. You will also get tips on working with different Access versions and find out what to do if you want to get even fancier with VBA in the future. 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 purpose of the technique taught in the video?
A. Enable or disable a field based on the value of another field
B. Change the color of a field based on user preferences
C. Export table data to Excel automatically
D. Validate user login on a database form
Q2. Which Microsoft Access feature is primarily used in this video to achieve enabling or disabling a field without VBA?
A. Macros
B. Data Validation
C. Conditional Formatting
D. Form Wizards
Q3. When setting up a rule in Conditional Formatting, which option do you choose to compare fields?
A. Field Value Is
B. Expression Is
C. Formula Is
D. Value Equals
Q4. What should you be careful about when referencing a field in an expression in older versions of Access?
A. Use curly braces around the field name
B. Use single quotes around the field name
C. Use square brackets around the field name
D. Use parentheses after the field name
Q5. What happens to the "Credit Limit" field when a customer's state is not "Florida"?
A. It becomes hidden
B. It is deleted from the form
C. It becomes disabled
D. Its value is automatically set to zero
Q6. How does conditional formatting respond if the "State" field is changed to "Florida"?
A. Field remains unchanged until you refresh the form
B. Credit Limit field immediately becomes enabled
C. Credit Limit field remains disabled until form is closed and reopened
D. The form displays an error message
Q7. Why do you need to account for null values when writing the conditional formatting expression?
A. Null values can cause Access to crash
B. Null values are always considered as "Florida"
C. Comparing null with any value results in null, which is not true or false
D. Null values will automatically enable the field
Q8. What is a recommended solution for handling customers not from Florida who have existing credit limits?
A. Delete their records from the table
B. Change their state to Florida
C. Set their credit limit to zero using an update query
D. Ignore their values as it does not matter
Q9. If you want to allow only managers to edit the credit limit regardless of state, what approach does the instructor suggest?
A. Use advanced conditional formatting only
B. Use VBA programming to add this ability
C. Delete the credit limit for non-managers
D. Change the form layout to continuous
Q10. Which Access series does the instructor recommend for learning more about the enabled property and working with macros?
A. Access Absolute Beginner I
B. Access Advanced Level 2 and Level 3
C. Access Training for Developers
D. Access Web App Essentials
Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B
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 enabling or disabling a field in a Microsoft Access form based on the value of another field. A common example might be allowing only customers from Florida to have a credit limit assigned, and automatically disabling the credit limit field for all other states.
This is an advanced tip but does not require any programming or VBA. Instead, we rely on conditional formatting, specifically using the "Expression is" option. I consider this technique expert-level, as you need a solid understanding of how conditional formatting expressions function in Access.
Before proceeding, you should be familiar with conditional formatting and how the "Expression is" feature works. If you are not, I recommend reviewing those concepts first. There are helpful introductory videos linked on my website that you should consult before continuing with this topic.
In my free TechHelp template database, which is available for download on my website, you can see this in action. If you open the customer form, the goal is to disable the credit limit field unless the state is Florida.
Start by switching the form into design view and opening the properties for the credit limit field. Under the Format tab, choose Conditional Formatting, create a new rule, and set it to "Expression is." The expression checks whether the state is not equal to Florida. Remember that in older versions of Access you may need to include square brackets around field names so that Access recognizes them correctly. This is less of an issue in recent versions, but it is something to keep in mind for compatibility.
Once your expression is set up, adjust the Enabled property in the conditional formatting options so that the credit limit field becomes disabled if the state is not Florida. After saving and reopening the form, you will notice that only customers from Florida can interact with the credit limit field, while it remains visible but uneditable for all others.
If your data already contains customers from various states with existing credit limits, you might need to clean up or update those records. This can be done directly in your customer table or by running an update query. If you are not familiar with update queries, I have a tutorial available that you can reference.
One of the advantages of conditional formatting is that changes are reflected immediately. As soon as you change the state value, for example, the enabled or disabled status of the credit limit field updates right away. This approach also works well in continuous forms.
You should also be aware of the nuances involving null values. If the state field contains a null value, comparing null with anything in Access does not yield a true or false result. To handle this, you need to update your conditional formatting expression to also check for nulls. This way, the credit limit field is disabled for records where the state is not Florida or when the state is blank.
Make sure to implement this completed expression in both your standard and continuous forms to ensure consistency. Sometimes issues only become apparent after practical use, especially when dealing with different form types.
If your requirement is just to enable or disable a field based on another field's value, conditional formatting is sufficient. However, if you want to introduce more complex scenarios, such as allowing managers to edit certain fields regardless of other values, then some programming may be necessary. I will cover how to achieve this kind of flexibility using VBA in an upcoming tutorial.
For those interested in learning more about controlling the enabled property without writing code, I cover this topic extensively in my Access Advanced Level 2 and Level 3 courses, where the focus is on using macros instead of VBA. There, you can learn how to set up business rules such as disabling invoice editing once paid, with the option to override using a manager password.
Many users are hesitant about VBA, but I assure you it is straightforward once you are familiar with the basics.
That concludes today's TechHelp lesson. You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Enabling or disabling a field based on another field's value
Using conditional formatting in Access forms
Applying 'Expression is' rules in conditional formatting
Disabling a control when a field does not equal a specific value
Handling bracket notation for field references in different Access versions
Setting the Enabled property using conditional formatting
Testing enabled and disabled fields on forms
Immediate updates with conditional formatting when field value changes
Applying conditional formatting in continuous forms
Dealing with null values in conditional formatting expressions
Editing and updating conditional formatting rules for controls
|
