Fitness 52
By Richard Rost
28 days ago
How to Lock or Unlock a Field on a New Record
In this Microsoft Access tutorial, I will show you how to lock or unlock a checkbox field in a continuous form to prevent users from editing the field in a new blank record. We will learn how to use the Form Current and Dirty events to control when the field is accessible, discuss issues with event timing, and address problems that occur when users use Shift-click or try to interact with checkboxes on new records. This is part 52.
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
Recommended Courses
Up Next
Keywords
TechHelp Access, lock field on new record, unlock field after editing, continuous form checkbox, On Current event, Dirty event, prevent shift click error, hasEaten locked property, form event timing, prevent duplicate record, database fitness tracking, before update event, enabled property discussion
Subscribe to Fitness 52
Get notifications when this page is updated
Transcript
In today's video, I'm going to show you how to lock or unlock a field in a new record in your Microsoft Access database.
Now, what exactly does that mean? Well, if you have a bunch of checkboxes over here, looking at a continuous form, you want to be able to check those boxes. But you do not want to check this last one. If you click on that, it creates a blank new record. You do not want that. So you want to be able to lock this box and not let the user click that box if it is the new record. Otherwise, you can go ahead.
And that is what we are going to cover in today's video.
Now, this is part 52 of my fitness database series. Do not worry if you have not seen all the previous ones. You do not really have to. If you do not care about tracking fitness and calories and all that stuff, that is okay. This is a database series about building a database. I am showing you all kinds of cool tips and tricks.
So, are you ready? Here we go.
I just want to start off today by saying I really, really like using these eaten buttons now, because right now, for example, it is about four o'clock. I have just had lunch and I can easily plan the rest of my day. I know I am going to work out today. Actually, today is a push day, so let's put push in there. I know that I am going to have my fruit bowl and yogurt before I go to bed. That is like a nightly ritual. I look forward to that every night.
Thank you. That tells me I have about a thousand calories to work with for dinners. That lets me know, roughly, how big of a dinner I can eat and still fall within my guidelines for today. I would like to stay around 2000 if I can. Remember, the key with sustainable weight loss is you do not want to starve yourself. So you do not want to go too far. My BMR is right around 2400, so the 400 calorie deficit is plenty.
Anyway, getting back to the Access stuff. I did discover one little bug. If you click on, say, something here and you hold down Shift, click, you get that. I wonder if any of you have discovered that. Basically, it says "syntax error in query expression" and says "food a time less than" and there is nothing, just the two pound signs, octothorpes, or whatever you want to call them, and food time is greater than this.
That is because this value does not exist: it is null. So we basically just have to check for if this is null, which it should not be. I do not think we allow that, do we? Then it creates a duplicate record there. So let's delete that. I do not think we can just blank this. Let's see. Yes, invalidated, it does not let you do that.
So we really just have to check to make sure they are not clicking on the last record. And it is only with the shift click, not even with the regular click. So let's delete that again.
Let's go back into our code. I just did a search for shift click and there it is. This is quite simply, just before we do anything, if Me. Actually, we have to put it inside the shift click. We cannot put it there. So if you do hit shift click, right here: If Me.NewRecord, then Exit Sub. Just do not do anything. Get right out of Dodge. We have not really set anything. There are no loops at all. That should be a fix for that.
Save it. Debug. Compile, once in a while.
Let's try it again. Click and Shift click and nothing happens. When I say nothing happens, I mean you do not get that error anymore.
You still do get a blank item down here. I hate that too. Let's fix that too.
Basically, what I would like to do here, thinking of it logically in my mind, I would like to say, if we are on a new record, if we are sitting on a brand new record, do not let the user click on that box. Click or shift click.
Now I am just undoing everything because I shift clicked down there. I will put all these back in place here.
Let's see if this works. I like to experiment with things and see if they will work first because I like to go with the easy solution first.
Let's get rid of that. Let's try putting a Before Update event in this box. Before this is updated, check to see if we are on a new record. If so, cancel. Let's try that. Let's give that a shot.
When I say "let's try it," that means I already know it is not going to work, but I am trying to teach you something. I know this is not going to work, but let's try it anyway so you can see my thought process.
Let's go on the Before Update event, which is where you would think you would do this. We'll say If Me.NewRecord Then Cancel = True: Exit Sub.
You would think that you should be able to do that. I am trying to update this field. But if I am on a new record, cancel and exit out.
Let's give it a shot. Save it. Close it. Save it. Open it.
Come down here and click. Still did not work.
Let's make sure that event is firing. That is the next thing I think of. Is this event really firing? Let's just do a MsgBox "Hi" in there. Save it. Go back out here.
Try it again. Click. There is my "Hi." But look in the background. The record is already there. That is not going to work.
Delete.
If we cannot cancel it, how about an Undo? Instead of Cancel, let's try to throw it an undo. Let's do them both. Let's put a Me.Undo in. Undo undoes the changes that you just did. Let's try that. Let's see if that works.
No, still not working.
What is the solution here? Well, we cannot rely on that event because, and here is the lesson I wanted to teach you, when you click on that box, Access immediately puts a new record in there. Even before the event fires. Now you get into the Before Update. It is not trying to update a record because the record is already there. So NewRecord is no longer a new record.
In fact, you can see that if you put it in here instead of messaging "Hi." MsgBox New. We'll say in here, Me.NewRecord like this. Save it and click. It says "False." It is not a new record, zero. So it is too late by the time we have already clicked on this record. We need a different approach.
So let's get rid of this whole thing.
What we are going to do instead is we are going to prevent the user from clicking on that box in the first place if you are on a new record. How do we do that? We use the Form Current event.
Remember, On Current event runs every time you move from record to record. We are going to say if we click on this record, then lock that box.
That event actually does fire before it processes any data. Sometimes it is a matter of figuring out which event runs at the right time that you want it to. There are a million events and I try to cover them all in my developer course and in my advanced series, I go about event timing. Sometimes you just have to experiment with them because, to be honest, there are so many events that even I do not remember them all. I mean, look at them in here. I do not remember which one goes before which one half the time. Here are all the events for a form. There are a lot of them.
So when does this go? When does that go? Well, in this particular case, we are going to use the On Current event. So, Form - On Current, right here.
We are going to say If Me.NewRecord Then hasEaten.Locked = True. You could also use Enabled, but Enabled will make the whole column look gray. I have dealt with that one before. It locks all of them. Remember, this is going to lock every box on the form, but the On Current event fires first. So if you click on a different box, it will still work because we are going to say Else hasEaten.Locked = False. End If.
Now, we also need a way to unlock the box if the user starts entering a new record. We will deal with that one next. Let's first make sure this one works.
Save it. Debug. Compile.
Ready? I can click on this box. I can click on this box. I can click on this box and nothing happens because the On Current event locks the box before the record touches the table.
If I come over here and start typing and then I click on this, it is still locked. We have not fired a new On Current event yet. So we need a way to say: when the user starts putting that record in and the record actually exists, I need to unlock this box.
For that, we can actually use the form's Dirty event. There is actually an event called On Dirty, right there. This event fires as soon as a new record is created. In other words, when the form goes dirty, the whole thing we are talking about with making a form not dirty, which saves it.
So, as soon as the user starts putting something in here, hasEaten.Locked = False. That should unlock it as soon as they start creating a record.
Save it. Let's try it. Close it. Open it.
Here we go. Ready? Now I can click on you. I can click on you. I cannot click on you. But as soon as I start typing something in, now I can click on you. I cannot click on you. This also fixes the shift click on a new record problem too. Watch this - delete. Click here. Shift click here. Cannot click on it because it is not available to be clicked on.
So technically, we really do not need that code that we put in before, but I thought of doing this after I had already written that. So you learned both. The second bit of code is not really necessary, but it is not a big deal. This still works.
This came up in the forums this morning. One of my users asked. He said he was having problems because he was trying to shift click and go backwards. That is not how it is set up. The way the code works right now is the first one has to be chronologically on top. That is how I wrote the code. It starts from this one and then the loop goes to this one.
One of my other students said you could program it to go backwards if you really wanted to. Instead of looking at the times chronologically, you could just click on the last one, store in memory a variable. What is the ID that you just clicked on or the time, either one. Then when you do the shift click, pull that variable out and go from that one. If it sees that the times are backwards, then run the loop backwards.
It is not hard to do. I am not going to do it, but it would not be hard to do. So if you want to do it for homework, go right ahead. If you have problems, post it in the forums and we will take a look at it. If enough of you want to see it, post a comment down below and I will add it to a future lesson.
This series is all about what you want to see too. I am adding stuff to this that I want for myself. If enough of you want to see how to do something like that too, then I will throw it in.
It is like the spaghetti sauce. Which one was it, Ragú or Prego? It is in there. We will throw it in.
Well, that is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Locking a field in a new record on a Microsoft Access form
Problem with users editing fields in the new record row
Preventing errors from Shift+Click on continuous forms
Using the Me.NewRecord property to identify new records
Form Current event to control control locking
Locking a checkbox based on current record status
Difference between .Locked and .Enabled properties
Using the Dirty event to detect data entry in a new record
Unlocking a field after the user starts entering data
Step by step walk-through of code to lock or unlock a field
Testing and troubleshooting On Current and Before Update events
COMMERCIAL:
In today's video, we're continuing with Part 52 of the fitness database series and you're going to learn how to lock or unlock a checkbox field in a continuous form when dealing with new records in Microsoft Access. I will show you why simply using the Before Update event does not stop users from clicking that field, and instead, you will see how the On Current and Dirty events work together to prevent unwanted edits on new records. We'll also discuss a little bug with shift-clicking and why certain events fire the way they do, plus you will pick up some handy coding tips for dealing with 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 main purpose of locking a field in a new record in a Microsoft Access database, as discussed in the video?
A. To prevent users from editing existing records
B. To prevent users from clicking and modifying a control on a blank new record
C. To automatically save all entered records
D. To allow users to create duplicate records easily
Q2. Which event should you use to detect when the user navigates to a new record in an Access form, according to the video?
A. After Update
B. On Load
C. On Current
D. On Dirty
Q3. What issue arises if you try to use the Before Update event to prevent changes on a new record's field?
A. The event never fires for new records
B. The new record is created in the table before the event fires
C. The event always cancels every field update
D. The record's data is always deleted
Q4. What is the effect of using hasEaten.Locked = True on a control in Access?
A. Makes the control completely disappear
B. Prevents editing the control but does not gray it out
C. Allows editing the control
D. Grays out the control and prevents all interaction
Q5. Why is using Enabled = False for a control less desirable compared to Locked = True in this scenario?
A. It prevents all records from being edited
B. It makes the control look gray and inactive
C. It causes an error when clicking the box
D. It hides the control from the form entirely
Q6. Which event is best for detecting when a user starts entering data in a new record, according to the video?
A. After Insert
B. On Current
C. On Dirty
D. Before Update
Q7. When the On Dirty event fires, what should your code do to allow editing of the previously locked control?
A. Re-lock the control
B. Set the control's value to Null
C. Unlock the control by setting Locked = False
D. Delete the current record
Q8. If you wanted to prevent users from clicking a checkbox in a continuous form for a new record, which combination of events and properties does the video recommend?
A. On Load event with Enabled = False
B. Before Update event with Cancel = True
C. On Current event with Locked = True and On Dirty event with Locked = False
D. After Update event with Undo
Q9. What is one major lesson about Access form events highlighted in the video?
A. All events fire at the same time
B. The sequence and timing of events matter; some events occur before data touches the table
C. Only the After Update event is necessary for controlling field behavior
D. Event order is always the same for every form
Q10. What would be a valid approach to modifying the code to allow handling shift-clicks that go backwards in time order, as discussed at the end of the video?
A. Change the form property to enable reverse order
B. Store the clicked record's ID or time in a variable and loop backwards if necessary
C. Only allow single clicks
D. Add another button to the form for shift-clicks
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-C; 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 covers how to lock or unlock a field in a new record in your Microsoft Access database.
Let me start by explaining the problem. If you have a continuous form with checkboxes, you typically want users to interact with those boxes for existing records. However, when you reach the empty row at the end which Access provides for adding new records, you do not want users to click that checkbox. Clicking in that row automatically creates a new, blank record, and that is usually not desired behavior in this context. The goal, then, is to prevent users from clicking the checkbox when they are on a new record, but allow the box to be clicked for existing records.
This lesson comes from part 52 of my fitness database series, but do not worry if you have not followed the entire series or are not specifically interested in fitness tracking. The techniques I'll show you can apply to many different kinds of databases, and my focus here is teaching skills and methods that you can use in your own projects.
I want to mention something I really like about these "eaten" buttons on my form. For example, after lunch I can quickly plan out the rest of my day's meals, workouts, and track my calories. This feature helps me see how many calories I have left and guides my dinner decisions to stay within my daily goal. For healthy weight loss, I aim for a modest calorie deficit rather than a drastic reduction. This keeps things sustainable.
Now, back to the Access topic. I found a small bug. If you select a record and use Shift+Click, you might encounter a "syntax error in query expression," especially if there is a null value involved in the filter. This happens because Access does not handle nulls in the way we expect in the current code. If you end up with a duplicate or partial record from this, just delete it.
After some testing, it turns out this is only an issue with Shift+Click interactions, not with regular clicks. The solution is to check if the user is interacting with a new record, and if so, prevent any processing in that event.
When reviewing the code, I conducted some experiments. I initially thought I could add code to the Before Update event of the checkbox to prevent changes when on a new record. The logic would be: if the current record is new, cancel the event. In practice, though, this does not work. I walked through that approach to show you the thought process and help you understand the sequence of events in Access forms.
The reason this method fails is that as soon as you click the checkbox in the new record row, Access immediately creates a new record before the Before Update event fires. By then, NewRecord is no longer true, so the event cannot prevent the change. Using Undo instead of Cancel does not work either, for the same reason.
The lesson here is that timing matters. Different events fire at different stages, and sometimes you have to figure out which one occurs at the right moment for what you are trying to achieve.
To solve this, I switched to using the form's Current event. The On Current event fires every time the form moves from one record to another, and it fires before data is processed. So, in the On Current event, I set the checkbox's Locked property to True if we are on a new record, and False otherwise. This prevents clicking the checkbox for the new record row at the right time.
One thing to watch is that once a user starts editing a new record, you might want the checkbox to become available again, since the record now exists. For this, I use the form's Dirty event. The On Dirty event fires as soon as changes are made and the record is being created. In this event, I unlock the checkbox so it can be clicked.
With this setup, the checkbox is locked when on a new record, but becomes available as soon as the user starts entering data into the new record. This approach also fixes the Shift+Click issue on new records, since the checkbox is never available in that row unless data is present.
I also discussed ideas for customizing this logic further, such as supporting Shift+Click in reverse order. If you want to try implementing that yourself, store the relevant value (such as the record's ID or time) when Shift+Click is used, and adjust the code to handle backwards selection. I leave that to you as an exercise, but you are welcome to post in the forums if you need help, and if there is enough interest I can cover it in a future lesson.
This series is shaped by your feedback, so let me know what you would like to see.
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 in a new record on a Microsoft Access form
Problem with users editing fields in the new record row
Preventing errors from Shift+Click on continuous forms
Using the Me.NewRecord property to identify new records
Form Current event to control control locking
Locking a checkbox based on current record status
Difference between .Locked and .Enabled properties
Using the Dirty event to detect data entry in a new record
Unlocking a field after the user starts entering data
Step by step walk-through of code to lock or unlock a field
Testing and troubleshooting On Current and Before Update events
Article
If you are using Microsoft Access and want to make sure certain controls, like a checkbox, cannot be edited on a new record in a form, there is a simple approach you can use. This is especially useful when you have continuous forms with checkboxes and want users to check the boxes on their existing records but prevent them from clicking the checkbox on the blank new record row that Access automatically provides at the end of the list. If a user tries to interact with that checkbox, you risk them inadvertently creating an accidental blank record or introducing errors into your data.
The goal is to program the form in such a way that the checkbox is locked when the user is on a new record, but unlocked and usable on all existing records. That way, clicking on the new-blank record cannot create issues or confusing user experiences.
The first thing you might think to try is handling this using the checkbox's Before Update event. Inside that event, you could check if the form is currently on a new record and, if so, cancel the action. For example, you might try this kind of code in the checkbox's Before Update event:
If Me.NewRecord Then
Cancel = True
Exit Sub
End If
While this is a logical approach, it ends up not working as expected in continuous forms. The reason is that, when a user clicks the checkbox in the new record row, Access has already created the new record before the Before Update event fires. By that point, Me.NewRecord returns False even though the user just clicked in the new record row, so this event cannot reliably prevent the behavior you want to avoid.
Undoing the change by calling Me.Undo or combining it with Cancel = True does not work either. Once the event fires, the new record is already created.
The better way to handle this is to use the form's On Current event. The On Current event fires every time the user moves from one record to another in the form. If you put your logic here, you can check as soon as the form's record pointer shifts. Specifically, you want to lock the checkbox (so the user cannot click it) when the form is on a new record.
Open your form in Design View, go to the code editor for the form, and find the Form_Current event. Inside this event, add the following code, assuming your checkbox is named hasEaten:
If Me.NewRecord Then
hasEaten.Locked = True
Else
hasEaten.Locked = False
End If
When you do this, every time the user navigates to a new record row, the checkbox becomes locked and cannot be clicked. As the user moves back to an existing record, the checkbox is unlocked and editable again.
Now, there is one additional case to consider. When the user starts typing information in the new record row (for example, entering other data in other fields), the record becomes active and is no longer a brand new blank line. At this point, you want to unlock the checkbox so users can interact with it as they would with existing records.
To do this, use the form's Dirty event, which fires as soon as the user changes data in any control on the form. In the Form_Dirty event, use this code:
hasEaten.Locked = False
Now, to summarize, you have two simple procedures:
In the form's Current event:
If Me.NewRecord Then
hasEaten.Locked = True
Else
hasEaten.Locked = False
End If
In the form's Dirty event:
hasEaten.Locked = False
With this setup, when users move to the new record row, the checkbox is locked and cannot be changed. As soon as they start entering data and the record is no longer blank, the checkbox is unlocked, and they can check or uncheck it as needed.
This effectively prevents users from clicking the checkbox in new, blank records, which can prevent the accidental creation of blank data or unwanted errors. The approach works reliably and does not require complicated checking on every possible mouse click or key event. It leverages the appropriate form-level events for clean, straightforward control over your field's locked state.
If your checkbox has a different name, just substitute its name in place of hasEaten in the above code.
This technique is a handy example of how understanding Access' form events can help you make your database interfaces more robust and user-friendly. Experiment with this method in your own forms to see how it works, and adjust for your control names and form layout as needed.
|
