Today we're going to talk about how to lock a parent form and the subform based on some condition in Microsoft Access. For example, once an order is marked as paid, we're going to lock the order and lock all the detail sections in the subform there so you can't change that stuff too. All right, we'll talk about that in today's video.

Today's question comes from Gary in Warwick, Rhode Island, one of my Platinum members. Gary says, "I followed the techniques that you showed in your 'Disallow Editing Data' video to lock orders once they are marked as paid. However, this doesn't prevent the user from modifying the records in the order detail subform. How can I lock the whole thing down?"

Well, Gary, you're right. The 'Disallow Editing Data' video only dealt with a single form. We covered allowing edits, deletions, and additions, but we only dealt with a single form and only that record, and we didn't have any subforms on it. So it's a little more complicated, but let me show you how to do that in today's video.

First up, of course, if you haven't watched this video first, go watch this first so you understand how all this stuff works. This is also a developer level video. What does that mean? Well, that means we're going to need a little bit of VBA, not much, just a little bit. So go watch this video if you've never done any VBA before. Make sure you understand how to use an if-then statement, after update event as well as the on current event, and if you haven't watched my invoicing video yet, go watch this first because this will teach you how I built the order entry system in the invoicing and all that stuff. These are all free videos; they're on my website, they're on my YouTube channel. Watch them all and come on back.

Okay, here I am in the TechHelp free template. This is a free database you can grab a copy of on my website if you want to. And in here, we've got customers, and customers can have orders. And now what we want to do here is, whenever an order is marked as paid, we want to lock this all down. Now in the other video, I showed you how to lock the fields on this particular order right, but we didn't talk about subforms.

So let's go, first of all, and set it up so that we can lock the parent form. Okay, to do this I'm going to start off by putting something in the after update event. Okay, so let's go to the is paid field, bring up its events, go to after update. I'll bring up my VB editor, here we are. Okay, now I'm going to put in here, I'm going to say if is paid then we're going to do some stuff, right. Me.AllowEdits equals false. Me.AllowDeletions equals false. We talked about this in another video. You can also turn off AllowAdditions, but I don't think that's necessary here. If you want to add a new order, go right ahead. I just want to prevent a user from modifying a paid one.

Otherwise, if this guy is not paid, then we're going to flip that. Remember, we have to do this for each one because if you go to an unpaid order, we still want to be able to modify it. Okay, and that should lock down the order when the is paid is changed. That happens in the after update. All right, but I also need this to happen when I open the form and when I move from record to record. That's the on current event. Right, so I'm going to put the same stuff in the on current event, but I don't want to duplicate my code. I don't want to have the same code in two places. So we're going to take all of this, cut it out, and we're going to make a new subroutine.

Private Sub, we're going to call it LockOrder, and I'm going to put that stuff in here in LockOrder. So now from the after update event, I just got to call LockOrder, and now I can put the same stuff in the forms current events. I don't have to have the same code duplicated in two locations. Always try to avoid that. If it's the same stuff, make your own subroutine. All right. So now I can come back here, I can go to the form's properties, go to events, find on current, dot dot dot, and right in here, LockOrder, lock or unlock order depending on the situation. Okay. Save that. Always throw in a debug compile. Let's come back out here. I'm going to close the order down. Let's open her back up again.

Now, this is a paid order, so I should not be able to modify stuff here. Can't modify, can't modify. Let's see if I can delete. Can't delete. Everything's good. But I can still come down here and change the items in the order. Which is not a good thing. I can still come down here and do that. Well, now it looks like he paid a much bigger order than he actually paid for.

And I should also add in at this point, there's nothing to stop the user from going to the table if you have the table open, and they can just change as paid there. You can set up any kind of other security that you want in the database to keep that from happening. I usually set up a manager password. I got whole separate videos on how to do all that stuff. Today, we're just worried about if this order is paid, I want to lock down the subform.

All right, so how do we lock down this subform? Well, if you look at the form, right, this guy here, it's a separate form but it's also a control on the order form. What's the name of that control? Right, OrderDetailF is the name of the control. Now, on the data tab, you'll see there's Enabled and Locked. We can set the Locked property to yes in our VB code if we want to lock this thing down. So let's go back to our code and right in here we're going to say OrderDetailF, that's the name of the subform, .Locked, that's the control's property, equals true. We've got to flip it here. We've got to lock it, and that's true. And again, come down here below it and make this false. Unlock it if the order is not paid.

All right, back up here, let's close this, close it back down, open it back up again. All right, that's locked and now this is also locked. I'm clicking in here, I'm trying to type, I can't change things. All right, if I go to a different order, this one is not paid, I should be able to come in here and make changes and make changes and make changes. Okay. All right. Come back over here and I can't make additions either, but unfortunately, I can still delete records. I don't like that.

In fact, we need to add that to the list of stuff for the suggestions for the Access team. Right, Sammy, add that to the list. If the subform control is locked, I should not be able to delete records either. All right, that's just, I don't like that. But we can still control that with one more line of code.

Now, this is a little more tricky. This involves modifying a property on the subform itself. All right, the AllowDeletions property of the subform. Okay, so let's go back into here. Okay, so now it's going to be OrderDetailF.Form.AllowDeletions. All right, that's going to be equal to false.

What does that mean? So, it's the OrderDetailF, is the control, .Form says I need to access the form properties. Right, the properties of that form itself, not the properties of the OrderDetailF control. Right because remember, you got a control and then inside the control, you have the form. I know it's weird; it's weird if you're new to Access, if you're new to developing this, it will take you a little while to get, but all of those properties, okay, save that, all of the properties of the form, right, this parent form up here, has a whole bunch of properties. Allow Deletions, Allow Edits, all this stuff, Record source, okay?

All of these same form properties are available to this subform in here. And notice that distinction. If I'm up here, if I click on this thing once, I just selected the control, the subform control. If I click on it again, now I've got the form properties inside. Okay, so it's an important distinction to keep in your head. You got two things you're working with here, well three things really. You got the parent form, you got the subform control, which we can lock but that doesn't change the ability to delete inside here, which you should. And then we also have the control of the properties of the form itself inside the control.

Yeah, I know, I know, I know, you get used to it after a while. Okay, so now that I've done that, this guy's paid so I should not be able to change this. I'm trying to change this, nothing's happening. I can't make changes here, I can't make changes here, I can't delete stuff. Trying to press delete, can't delete it. Okay, that's good. Let's go to the next record. All right, changing things here, changing things here, deleting stuff here, and everything works fine.

And there you go. That's how you can lock down the parent form and the subform. And now, how you get to the point where you can undo this, where you can unlock that, that's up to you. I got another video that I put together where I have a thing called an edit mode, which you can use for that, and you can switch between being allowed to edit and not edit something. Same concept here with once something is paid or not. This is because some people have users that, they get confused that you can just come in here and just change whatever you want whenever you want. They want to have to manually make them click an edit button to switch to either an edit or a viewing mode.

All right, so that's one way you could do it. Or, of course, I got this video where I show you how to prevent deletions and in the extended cut, I show you how to set up a manager password. So if you do try to make a change, like in this other video, for example, if you try to delete the record but you could easily have it so if someone tries to mark an order unpaid, it just asks them for a manager password. Or, of course, you can go through the techniques I show in my security seminar which I show you how to lock the whole database down so that users can't do certain things or they can do certain things and you can just set it up so that a manager type account can go in and mark stuff unpaid or can even change things that are marked paid. It's all completely up to you. They're your Legos; you build them however you want to build them.

And I also, one more thing, I also do show another technique for locking and unlocking if an invoice is paid in my Access Developer Level 9 course where I actually give you a button that says, 'This order is paid, click here to edit.' Then you can ask for a manager password or any other kind of security that you want to as well. There's all kinds of things you can do, but Gary, there you go, that's how you lock down the order and the subform, and that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.

TOPICS:
Locking a parent form in Microsoft Access
Locking subforms in Microsoft Access
Conditionally locking forms when an order is marked as paid
Event-driven form locking using After Update event
Using VBA to control form editability
Preventing record deletions when a form is locked
Subroutine creation in VBA to avoid code duplication
The On Current event to initialize form state
Using the Enabled and Locked properties in forms
Accessing subform control properties through VBA
Differentiating between form and subform control properties
Implementing AllowDeletions on subforms
Adding security measures to prevent table edits
Manager password implementation for editing locked records
Utilizing an edit mode to toggle between edit and view states
Advanced locking techniques in Access Developer Level 9 course