Today we're doing a follow-up to a video I made about three years ago that allows you to create an edit mode for your databases, where normally the user can just view what's on the form.

If they want to edit it, they have to click an edit button, and this prevents accidental changes and things like that.

One downside of the method that I used in that video is that it locks everything on the form, all of the fields.

What if you want to leave certain fields unlocked so that you can edit them regardless? Well, that's what we're going to talk about today.

Today's question comes from Ken in Rancho Cordova, California. That just sounds cool, Rancho Cordova. One of my Gold Members.

Ken says, I am interested in having my form be read only by default with an edit button to make it writeable. I just saw your videos about edit mode. In my case, I have a combo box in the header that selects a record, but it becomes frozen if I set the form's Allow Edits property to False. The only workaround I found is to set the Locked property for all the individual controls, but that's a lot of VBA if the form has many controls. Is this the best way to do it or is there a better approach?

Yes, that is the best way to do it, but you're right. If you have a lot of controls, that's a lot of work. Not only is it a lot of work to write in the first place, but it's a lot of work to maintain it if you add fields or delete fields. You have to constantly update your code.

The best way to do it is to simply loop through all of the controls on your form and lock the ones you've specified. I'm going to show you how to do that today.

But first, this is part two. If you haven't watched part one yet, here's the original video. Go watch this first. This is very helpful, and it includes all the other prerequisites you should know before today, like basic VBA and stuff like that. So go watch this video first, for sure.

We're also going to use the Tag property today. I just released this video yesterday, so go watch this.

We are going to use a For Each loop to loop through all of the controls on our form. We are not going to create a function today, but we are going to create a subroutine today. It's very similar to this. The team just doesn't return a value like a function does, but go watch this so you understand how to create your own procedures, subroutines, and functions.

These are all free videos. They're on my website. They're on my YouTube channel. You will find the links down below. Go watch those, and then come back.

OK, here I am in the TechHelp Free template. This is an older version that I built three years ago that I used for the original video. And in here, you can see that if I try typing, I can't. I'm trying to type right now. All these fields are locked. Everything's locked. I have to hit the Edit Mode button, and it goes into Edit Mode. It says right here.

In the extended cut, I show you how to do cool stuff like change the colors and all that. That's not a big deal. But now I can come in here and I can edit stuff.

Now the problem that Ken is having is that he's got a search combo box across the top of the form, and that will also get locked with this method. I have a video on how to do that where you can put a combo box up here, and it will search for records based on the data in your form. But this will become locked, which means you can't change its value. And why is that? Well, it's what we used to lock this form.

If you go into the Edit Mode button, you will see it's right here. Me.AllowEdits = True, Me.AllowDeletions = True. Otherwise, it's False. We set that in the Form Current event. Once you set AllowEdits to False, that means all of the fields are locked. The user can't change anything, even on unbound fields.

So what we're going to do instead is use the Locked property of each individual control.

Now, to do them all would be time consuming because you would have to say FirstName.Locked = True (if I could spell Locked). This is the Form Current event. LastName.Locked = True. And so on through all of them. That becomes a nightmare of updating all of your forms. You would have to type them all out here once for every single control on here that you want to lock. But if you add stuff, if you delete stuff, you have to come in here and change your code, which I hate to do.

So what we're going to do instead is use the Tag property. We're going to tag the fields that we want to lock. Then we are going to loop through all of the fields on the form and lock the ones that have that tag.

Come back in over here. We're going to go into the properties. Remember from yesterday's video, we have this Tag property on here. It's just sitting there waiting for you to do something with it. We're going to do something cool with it right now.

I want you to click and then hold down the Shift key and click on all of the controls in here, the text boxes that you want to lock when this routine runs. Let's also lock this checkbox. We're going to leave Notes unlocked, so no matter what, the user can come in here and change the Notes. They don't have to go into edit mode. We will leave this guy unlocked just to show that this works.

Now over here in the Tag property, we're going to put something unique so that when our code runs, it knows which fields to lock. I'm just going to put in lock me. One word, lock me. We're going to look for that in our loop.

Let's save changes. That should be applied to all of those fields now. They should all have lock me. If you click on any one individually, it will say lock me. Lock me. Lock me.

Back into our code. Now I'm going to call this code twice. I'm going to call it here and I'm going to call it here. I'm just going to flip the value whether we're locking it or not. So we're going to make that our own sub routine up here.

This will be a Private Sub. We're going to call it LockFields. And we're going to send into it a value: are we locking or not locking? Are we locking or unlocking? We'll call it DoLock. That will be a Boolean, yes/no, a true/false value.

How do we loop through all of the fields on a form? Well, those fields are actually called controls.

We're going to Dim ctl As Control. So ctl is now a variable that is of type Control. What's a control? All of these things are controls. The labels are controls. These buttons are controls. These are all controls.

We're going to loop through all of them. How do you loop through all of them? We're going to say For Each ctl In Me.Controls ... Next. That's a For Each loop.

Now, inside this loop, we can access each one of those controls. It's going to loop through every control on the form. We're going to say if ctl.Tag, if the Tag property of the current control says lock me (that's the text in the tag), if that's the case, then ctl.Locked equals either True or False. We're sending that value in here as DoLock. So DoLock. End If. That's the end of our loop.

That's it. That's how you loop through all of the controls on the current form. Me is the current form, whatever form you're on. In each loop, the ctl value gets set to whatever control we're on. FirstName, LastName, all the labels, combo box, all the stuff.

If we see lock me in its Tag property, basically don't put lock me in the Tag property of any type of control that can't be locked. Otherwise, you'll get an error.

Now down here in our Form Current event, we're going to say LockFields True. As we move through the records and open up the first one, we're going to lock all the fields. Then in here, we'll get rid of this. Now we'll say LockFields False when they click on the Edit button. We're going to unlock everything.

Debug Compile once in a while. Come back out here. Save it. Close it. Open it. Oh, locked. Locked. I'm trying to type. Locked. This is unlocked. Oh, wait a minute. Oh, wait. Hold on. I can't type in here. What's going on?

I'm glad this came up because in the original database, remember what we did is set all the properties in here for the form to AllowEdits: No, AllowDeletions: No. Those are the default properties for the form itself, so we have to turn those back on to Yes.

Otherwise, you can't make changes. Remember, our code before would switch those values.

Glad that came up because in case you're using the same database, you'll be scratching your head just like I was for about three seconds. I'm like, wait a minute. What happened?

Save it. Close it. Open it. Now we should be able to not edit here, and we can edit here. Good.

Can't edit there. Hit the Edit button. Boop. We're in edit mode now. Now these fields are all unlocked. If I move to the next record, they should get locked again, which they did. Beautiful. Perfect.

One more thing. Bonus time. It's bonus time. Are you ready for some bonus? Bonus time. Excellent.

I was thinking about throwing this in an extended cut, but it's too short to make an extended cut out of, so I figured I'd just make it a bonus.

Are you ready? How about we gray out all the fields when they're locked? That way the user can visually see that they're locked. Come back in here.

We're going to say if DoLock, then ctl.BackColor = ... What do you want to make it? I'm going to use gray, and I like to control the colors with the RGB function. I'm going to go 200, 200, 200. That's a dark gray. Otherwise ctl.BackColor = vbWhite. There is a constant for that. We can use that.

Of course, I have a video on RGB and how to use all the colors if you want to learn more.

So if we're locking, set these colors to gray. If not, set them to white. Debug Compile.

Now, something interesting is about to happen. Watch this. Save it. Close it. Close it. Open it.

Oh, object doesn't support this property or method. What's going on? Debug. ctl.BackColor. What does that mean? You can't see what the control is. If you want to do a little troubleshooting, if you can't figure it out, you could come in. Look at those. That's interesting. Look at that. It's got some of them gray, but it didn't for others. I'll explain why in a second.

If you want to know what the control is, watch this. Just for some troubleshooting in here, I'm going to go Debug.Print ctl.Name. What does Debug.Print do? It prints down here in the Immediate Window, which you can't see right now. Turn on the Immediate Window. That's a little window down here. That's where all your debug messages go. I have a whole separate video on the Immediate Window. Here it is. I'll put a link down below.

But what this will do is it will let you see the name of the control before it throws that error. Save it. Debug Compile.

Notice some errors will get through the compiler. There are no compile errors here. This is what's called a runtime error.

Now, let's do this again. Open her up. Oh. Okay. Look at this. Object doesn't support this method. Debug. So what is control name? It's isactive. See, the last one down here is isactive. What is isactive? Let's stop the compiler. Isactive is the checkbox. Checkbox doesn't have a BackColor property. That's what is throwing the error message.

There are a couple of ways you can handle this. We can turn this off. Now we can get rid of this.

There are a couple of ways you can handle it. First, you could check to see what the type of control is and if it has a BackColor property. That's a lot of work, but you can do that. The other thing you could do is use some basic error handling.

Before the stuff that you know is going to throw an error, say On Error Resume Next. And then after it, say On Error GoTo 0. That will turn error handling back off. Because you want to know if this is going to throw an error message. That's important.

But for this, you know it's going to throw an error message. Sometimes that's OK. We'll just ignore errors right now and then turn it back on. This is done. I do this all the time.

There is no shame in using On Error Resume Next as long as you use it responsibly. It's like there's no shame in having a glass of wine with your steak. Just drink responsibly.

Of course, I have a video on error handling. Go watch this if you want to learn more.

Now Debug Compile and then close it. Save changes. Yes. Close it. Close it. Save it. Open it. Ready. Go. No errors and everything is properly grayed out. They can't click on that. Can't change that. It's a little change here. Let's go into edit mode. Click. Oh, look at that. Isn't that pretty?

Can you make this a toggle button? Sure, you can make it toggle so you could toggle back and forth between edit and not edit. I figured once you're into edit mode, it's pretty much done. You could change this to a Save button.

I have another video that I cover on how to do that, how to make a Save button. A lot of people, especially people who are coming from Excel, are used to having to save their work when they're done with it. They don't realize that if you just close this, Access saves that data for you.

Here is that video. Just make a Save button. Turn that Edit button into a Save button. Just change the caption on it.And then all you have to do is read the properties of one of the fields or read the caption if it says edit mode. Then you could just switch it back and lock all the fields again, or close the form, or whatever you want to do.

My job is just to show you all the Legos. You put them together however you want.

Speaking of the Legos, if you like this stuff, if you like learning VBA programming with me, if you are enjoying it, and if you want to make your databases go to a whole new level, check out my developer lessons.

My developer lessons do not jump around like the TechHelp videos do. The TechHelp videos, I have to say, well, we are going to do this, I am going to do that. It is pieces and parts from different videos.

The developer lessons are designed step by step from the beginning to cover stuff in order, the way you should learn it. So it is all one continuous thing.

And yes, I have lots and lots of videos. If you like my style, come and check them out. You will find a link down below.

But that is going to do it, folks. That is your TechHelp video for today. I hope you learned something.

Live long and prosper, my friends. I will see you next time.

TOPICS:
Creating an edit mode while selectively locking controls

Using the Tag property to identify controls to lock

Looping through all controls with a For Each loop

Writing a subroutine that receives a Boolean for lock state

Applying the Locked property based on the Tag value

Applying conditional formatting by changing BackColor

Handling runtime errors for controls without BackColor

Using On Error Resume Next and On Error GoTo 0 for error handling

Ensuring form properties like AllowEdits and AllowDeletions are set correctly

Calling the lock routine from Form Current and Edit button events

COMMERCIAL:
In today's video, we're continuing with the edit mode technique from a previous lesson. You'll learn how to let users keep certain fields on an Access form unlocked while locking the rest, so you can always edit ones you need, like a search box, while protecting others from accidental changes. I will show you how to tag fields you want to lock, write a VBA subroutine to loop through all form controls, lock only the tagged ones, and even use the Tag property and For Each loop in VBA. As a bonus, I'll show you how to gray out locked fields and use simple error handling in your code. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.