Lock Subform
By Richard Rost
2 years ago
Lock Parent & Subform in Access if Order Paid In this Microsoft Access tutorial, I will show you how to lock both a parent form and its subform based on a payment condition. Discover the steps to prevent edits, deletions, and additions to an order once marked as paid, ensuring data integrity in your database application. Gary from Warwick, Rhode Island (a Platinum Member) asks: I followed the techniques you showed in your Disallow Editing Data video to lock orders once they are marked 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? MembersThere is no extended cut, but here is the database 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, lock parent form, subform condition, Access form locking, prevent record modification, VBA lock form, disable form editing, conditional form control, paid order lock, VBA After Update, VBA On Current, Access subform locking, restrict Access subform, lock Access form fields
Subscribe to Lock Subform
Get notifications when this page is updated
Intro In this video, I will show you how to lock both a parent form and its subform in Microsoft Access based on a specific condition, such as when an order is marked as paid. We'll walk through using VBA to control editing, deletions, and the locked state at both the form and subform levels, discuss the use of after update and on current events, demonstrate how to simplify your code with subroutines, and explain the difference between control and form properties when managing subforms. This is a developer-level tutorial focused on keeping records locked down once certain criteria are met.Transcript 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 courseQuiz Q1. What condition triggers the locking of the order and the details in the subform according to the video?
A. When a new user is created
B. When there is a system error
C. When an order is marked as shipped
D. When an order is marked as paid
Q2. Which VBA event is used to lock the order when the "is paid" status is changed?
A. On Click
B. After Update
C. Before Update
D. On Load
Q3. What is the purpose of creating a new subroutine called ‘LockOrder’ in the video?
A. To automate the creation of new orders
B. To send an email confirmation for payment
C. To consolidate the locking code and avoid duplication
D. To initialize default values for order details
Q4. Which properties on the form are set to false in the VBA code to lock down the order?
A. Me.RecordSource, Me.AllowUserGroups, Me.AllowAccess
B. Me.Visible, Me.AllowDataEntry, Me.AllowThemes
C. Me.AllowEdits, Me.AllowDeletions, Me.AllowAdditions
D. Me.LockNavigation, Me.LockFilters, Me.LockFields
Q5. To lock the subform, what property is set to 'true' in the VBA code?
A. Subform.Enabled
B. Subform.Visible
C. OrderDetailF.Locked
D. OrderDetailF.AllowEdits
Q6. What does 'OrderDetailF.Form.AllowDeletions = false' in the VBA code imply?
A. It sets the deletions allowed for the main form to false.
B. It sets the form to allow deletions permanently.
C. It prevents deletions in the subform when the order is marked as paid.
D. It is a command to delete the subform.
Q7. What kind of database object is OrderDetailF in the context of the video?
A. A field in the main table
B. A query used for generating reports
C. A subform control on the main order form
D. A standalone Access form
Q8. What does the video suggest to do if you need code to function not only after an update but whenever the form is opened or when the record is navigated to?
A. Create a loop that runs the code continuously
B. Include the code within the On Open event
C. Use the On Current event as well as the After Update event
D. Only use an On Click event on all form objects
Answers: 1-D; 2-B; 3-C; 4-C; 5-C; 6-C; 7-C; 8-C
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 both a parent form and its subform in Microsoft Access based on a particular condition, such as when an order is marked as paid. In this lesson, I will show you the steps necessary to ensure that when a record is locked in your order form, the associated subform detail records are locked as well, preventing any additional changes.
Gary brought up a good question that builds on a previous lesson where I demonstrated how to lock a single form by preventing edits, deletions, and additions once a condition is met. However, that method did not address scenarios where a related subform exists, such as when you have an order record with multiple order detail items. So, let's walk through how to handle both the main form and the subform together.
Before going further, make sure you are comfortable with the basics of form locking in Access. If you have not yet worked with the After Update and On Current events, and if you are new to using If-Then statements or just getting started with VBA, I recommend watching my introductory VBA tutorials and my video on building invoice systems in Access. All these resources are available for free on my website and YouTube channel.
In my demo database, which you can download from my website, we have customers and their related orders, with each order containing multiple line items in a subform. Our objective is to lock both the order and its details whenever the order is marked as paid.
To begin, we set the locking behavior for the parent form. In the code behind the IsPaid field's After Update event, you can place VBA that checks if the order has been paid. If so, it disables editing and deleting on the form. Normally, you might consider disabling additions as well, but in most cases, you will want to allow users to add new orders, just not edit existing paid ones. Remember to flip those properties back if you move to a record that is not paid. This ensures the locking only applies to appropriate records.
You will quickly realize that you do not want to put the same code in multiple events. For example, both After Update and On Current need to react appropriately, but duplicating code is never ideal. The recommended way is to create a separate subroutine that handles the locking logic. Then, both event procedures can simply call this subroutine. This way, your code is neater, easier to maintain, and avoids accidental inconsistencies.
After implementing this, you will see that edits and deletions are indeed disabled on paid orders. However, you will still be able to modify records in the subform for that order, which defeats the purpose of locking once payment is made.
The next step is to lock the subform along with the main form. You need to treat the subform as a control on your main form, using its control name, such as OrderDetailF. Setting the Locked property of the subform control to True using VBA will prevent users from making changes to the detail records as well. Just be sure to also unlock it in your subroutine whenever the order is not marked as paid.
Even after locking the subform control, there is a subtle detail to watch out for: users may still be able to delete records in the subform, which is generally not desirable once an order is locked. To prevent this, you need to manipulate the subform's form-level properties, specifically AllowDeletions. In your subroutine, set OrderDetailF.Form.AllowDeletions to False when the order is paid, and back to True when the order is unpaid. It is important to recognize the difference between the subform control (on the parent form) and the form inside that control. Getting comfortable with that distinction will make working with main forms and subforms much easier.
After implementing those changes, you can check that all forms of editing—including changing, adding, or deleting records—are disabled when an order is marked as paid. When you move to a new or unpaid order, the editability is restored.
At this stage, you have a robust solution for locking down both a parent form and its subform based on a specific condition such as payment status. If you want to implement additional flexibility, such as allowing users to enter an "edit mode" where a manager can override the lock—perhaps by entering a password—this can be done with techniques I have taught in other videos. For example, one lesson shows how to add a manager password to allow changes, and my security seminar covers how to lock your database down even further, restricting user access and capabilities on a much broader level.
If you are interested in even more advanced techniques, in my Access Developer Level 9 course, I show how to provide users with an "Edit" button that toggles between view and edit states, offers password entry for secure edits, and provides additional user control. Everyone's needs vary depending on how much security and flexibility you want to offer in your database.
To recap, we addressed:
- Locking parent forms and subforms in Access when an order is paid
- Using event-driven VBA to conditionally control editing, deletions, and additions
- Avoiding code duplication with subroutines
- Understanding the distinction between subform controls and subform forms
- Implementing tighter security through subform properties
- Exploring options for edit modes and manager overrides
For a complete video tutorial with clear, step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Locking a parent form in Microsoft Access
Locking a subform control in Microsoft Access
Conditionally locking forms when order is marked paid
Event-driven form locking using After Update
Using VBA to control AllowEdits and AllowDeletions
Moving code to a subroutine to avoid duplication
Calling locking code from On Current event
Using the Locked property on subform controls
Setting AllowDeletions on subform via VBA
Accessing form properties of a subform through control
Testing locked and unlocked form behavior through navigation
Distinguishing between form control and the embedded form |
