Lock Field
By Richard Rost
3 years ago
How to Lock a Specific Field in Microsoft Access
In this Microsoft Access tutorial I will teach you how to lock a specific field once a value has been entered into it. We will use the On Current event and a little bit of VBA code.
Pre-Requisites
Recommended Courses
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, lock single field, lock single control, How do you lock a field after data entry
Subscribe to Lock Field
Get notifications when this page is updated
Intro In this video, I will show you how to lock a specific field in a Microsoft Access form once a value has been entered, using a small amount of VBA code and the on current event. I will demonstrate how to prevent users from editing a field like credit limit after it has data, give the field a visual cue by changing its background color when locked, and discuss why proper database security is important for this technique.Transcript Welcome to another FastTips video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to lock a specific field or fields once a value has been entered in your Microsoft Access Database. This is a question I get once in a while in various forms, but basically, you've got a field like, let's say, credit limit. If there's a value there already, you don't want the user to be able to click on there and change it. I am trying to click right now and type stuff, and I can't. But if I go to a record that doesn't have anything there, the user can enter something. And then once they leave it and come back to it, now it's locked, so you can't change it.
Let's see how we can do that.
Before we get started, this is a developer-level video, which means we need a little bit of VBA. If you have not watched my intro to VBA video, go watch it now. It is about 20 minutes long, and it teaches you everything you need to know about VBA to get started. We're going to use an if-then statement in our code, so you need to know how to do that. Go watch this video if you've never used an if-then before. You need to know what null is and how to check for a null value using is null.
The star of the show today is the on current event. This is an older video. The on current event is an event that runs when you move from record to record or when a form opens and you get the first record. And this is what is going to actually make everything work. Go watch this video. These are all free videos. They're on my website. They're on my YouTube channel. I'll put links down below that you can click on to go watch this stuff. Go watch it and come on back. I'll wait for you.
All right. Here I am in my TechHelp free template. This is a free database. I'll copy it up to my website if you want to, and I've got a customer form. On that customer form, pick a field, any field, I don't care. Let's do credit limit. We'll say if credit limit has something in it already, then you can't change it. I'm going to delete a couple of records before we put the code in. Let's delete that one. My tab order is kind of weird; it goes from credit limit up to notes. That's just me hitting tab. We'll leave one in there and then delete that one.
So all the records that don't have a credit limit, we could put some in there. If it's got something already, it's going to be locked. When do we determine whether or not to lock that particular text box? We're going to use the on current event for that. The on current event runs when you move from record to record, or when the form opens and loads the first record. That's the perfect time to see if the credit limit is null or not.
Let's go to design view.
Let's open up the properties for the form, which is where the rule bar is. Meet that little box right there, double-click. There it is, property sheet for the form. Go to events and find on current. It's the first one. Click on dot dot dot. That will open up your VBA editor, and right in here is where we're going to put our code.
We're going to check to see if credit limit is null. We're going to say: if isnull(creditlimit) then do some stuff. What are we going to do? We're going to lock the credit limit field. If it's null, then we want to unlock it. So we're going to say: creditlimit.locked = false. It's null, so you can edit it - in other words, unlock it. Otherwise (else), if it's not null, we're going to say: creditlimit.locked = true. End if.
If credit limit's got nothing in it - if it's null - locked is false. OK, let the user put something in there. Otherwise, if there's something in there, lock it. That will run in the current event, the form current event. Save that.
Back over here, I'm going to close my form. Always close and reopen your forms, people.
All right, there we go.
This is null right now, so I should be able to put something in there. Perfect. Let's move off of it, and now let's move back to it and try to edit that. I can't. I'm typing right now. Nothing is happening because the field is locked, because there's something in there. When the record loaded in the on current event, it noticed that that was not null and it locked itself.
Now, it would be nice if I had some kind of visual cue to tell me that that was locked. Maybe let's change the background color. We can do that with one more line of code - well, two technically. Edit creditlimit.backcolor. If it's unlocked, leave it white, so we'll say: = vbWhite. That's a special code for white. Then down here, we'll say: creditlimit.backcolor =
What do you want? Red? There's vbRed, vbBlue, vbGreen. I want gray, like a light gray. Now, there is no vbGray, so for that, I like to use the RGB function (red, green, blue), and you can specify the three individual components. 255, 255, 255 is white. If you switch it to 0, 0, 0 that's black. Let's go with like a light gray. We're going to bring the color down from white just a little bit. Maybe 200, 200, 200 like that. That should give us a light gray.
I usually put a comment like "light gray" there, because someone looking at the code is going to wonder what that means. Now if we come back to our database and go to a field that's locked, look at that. It shows up gray in the background. See that? Just kind of neat.
Now, you can alternately lock this value as soon as it's typed in. If I put a 500 in there and hit tab, my focus moved up to my notes field. Notice the record is still dirty. It's still able to be edited at this point, because the user hasn't saved the record and moved off of it. That doesn't happen until you leave the record and come back to it or close the form and reopen it, so they can still edit it. If you want to lock it at that point too, you can put something in the after update event.
I don't know if I would, because then anytime the user makes a mistake, they have to call the boss and say, "I made a mistake, I should have typed in 2500, I accidentally typed in 500." So I would just stick with this. That's the way I like it. Now, if you want to throw something in the after update event, there's a video on that too. It's another similar event; you do the same thing.
Of course, none of this matters if you don't have your database secured. If people can just go into the tables and change whatever they want to change, then your forms don't really matter, whatever kind of events you have in there. Go watch this video on simple security. It will show you the basics of what you need to do to lock down your database to prevent most users from getting in there and making changes that you don't want them to.
If you really want to lock down your database, I have this thing called a security seminar where I show you how to seriously, professionally lock it down as best as Access can be.
Finally, how do you get it so that you can edit that if you want to? If you have a manager, you can make a manager form using the tips that I show you in the security seminar and other resources. You can make a separate form that managers have to log on to. Or you can do something as simple as double-clicking here and asking for a password. You can use a simple input box. I actually show how to do that in this video. Enter a manager password, type it in, and it unlocks the field.
That, in a nutshell, is how you can use a little bit of VBA to lock a specific field once a value has been entered into it. That is your FastTip for today.
Live long and prosper, folks. I'll see you next time.Quiz Q1. What is the main purpose of locking a field in Microsoft Access as described in the video?
A. To prevent users from deleting the record
B. To stop users from re-entering a value once it has been entered
C. To prevent users from viewing the field
D. To automatically calculate the value
Q2. Which event is primarily used to determine when to lock or unlock the field?
A. On Click
B. On Load
C. On Current
D. On Open
Q3. What does the IsNull function do in this context?
A. Checks if the form is open
B. Checks if the current record is locked
C. Checks if the field value is empty
D. Checks if the user has administrative access
Q4. If the field is null, what should the code set the Locked property of the text box to?
A. True
B. 1
C. Null
D. False
Q5. When does the On Current event fire in an Access form?
A. When a new field is added
B. When switching from record to record or when the form opens
C. When a value is typed in the field
D. Only when the database is first opened
Q6. What visual cue does the video suggest adding to indicate the field is locked?
A. Change the font style to bold
B. Change the field border color
C. Change the background color to gray
D. Display a popup message
Q7. How is the gray background color specified in the VBA code?
A. vbGray
B. RGB(200,200,200)
C. Color.Gray
D. vbBackgroundGray
Q8. Why is locking the field immediately after update not always the best choice?
A. It locks all fields, not just the current one
B. It prevents users from saving any data
C. Users cannot correct typos or mistakes easily
D. It slows down the database performance
Q9. Why does the security of the database itself matter, even if you lock fields in forms?
A. Users can still access and edit tables directly if security is not in place
B. Locking fields in forms automatically secures the database
C. Security is required to change VBA code
D. Security prevents the database from being opened
Q10. What is one way suggested to allow managers to edit locked fields if necessary?
A. Have the manager uninstall Access
B. Use a separate manager form with password protection
C. Use macros instead of VBA
D. Send a request to Microsoft for special access
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-C; 7-B; 8-C; 9-A; 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 video from Access Learning Zone covers how to lock a specific field on your Microsoft Access form once a value has been entered. This is a question I am asked quite often. For example, suppose you have a credit limit field for your customers. If that field already contains a value, you probably want to prevent users from making any changes to it. You want users to be able to enter a value on new records, but once something is entered, it becomes locked and uneditable.
This feature is helpful in cases where you want to avoid accidental changes to sensitive information. Let me explain how to accomplish this.
This is a developer-level topic and uses a little bit of VBA, so if you're not yet familiar with Visual Basic for Applications, I recommend you check out my introductory video on VBA first. In that video, I explain the basics, including if-then logic and working with null values using the IsNull function. It will be important to understand these concepts for today's lesson.
The key event we rely on is the On Current event for the form. This event occurs whenever the user navigates from one record to another or when the form loads its first record. That makes it the perfect place to check whether the credit limit field (or any field you choose) is empty or has a value.
In my free TechHelp template database, I have a customer form with a credit limit field. To make this work, choose the field you want to lock – like credit limit. The plan is simple: if the credit limit is empty, the field will remain unlocked and the user can type in a value. Once a value is entered and the user leaves the record, when they return the field will be locked and further editing will not be possible.
Setting this up involves opening the form in Design View and opening the properties for the form itself. In the Events section of the property sheet, find the On Current event. Enter the VBA editor for this event. This is where you add an if-then check: if the credit limit field is null, unlock it so it can be edited. Otherwise, if it has a value, set it to locked.
With this in place, every time you navigate to a record, the form checks the state of the credit limit field and decides whether the user should be allowed to edit it. If you open a record without a credit limit, you can enter a value. Once the record is saved and you move away, when you come back the field is locked and cannot be edited. This gives you control over who can change important values and helps prevent mistakes.
To make it clear to users that the field is locked, you can also add a visual cue by changing the background color. For example, if the field is unlocked, keep it white. When it becomes locked, change it to light gray using the RGB color function. This way, users can immediately identify whether a field is editable or not just by looking at it.
One important note: the locking behavior based on the On Current event means that the field only becomes locked after you leave the record and return to it. If you want to lock the field as soon as the user enters something and leaves the field, you could use the After Update event for that control as well, but I usually prefer to have it activate only after the record is saved. That way, if a user realizes they typed in the wrong value, they can still correct it before leaving the record.
Keep in mind that form-level security is not enough if users have access to the tables directly. If you want to truly protect your data, make sure to secure your database properly. I have separate resources and a security seminar that discuss locking down your database further, so most users can't get in and make unwanted changes.
For added flexibility, if you need to allow certain users like managers to edit these locked fields, you can develop a special manager form or use a password prompt to temporarily unlock the field. Techniques like using an input box for a manager password can be implemented if you need that extra layer of control.
In summary, with just a small amount of VBA code, you can make your Access forms smarter by locking fields after data entry and providing clear visual feedback to users. If you want to follow along and see all of these steps in action, 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 Locking a field after data entry in Access forms
Using the On Current event in Microsoft Access
Writing VBA code to lock or unlock a text box
Checking for null values with IsNull in VBA
Changing field BackColor to indicate locked status
Using the RGB function to set custom background colors
Best time to apply locking logic in form events
Adding user feedback with visual cues
Considerations for using After Update event
Ensuring form-based protections reflect true data security |
