Toggle Close Button
By Richard Rost
13 months ago
Toggle Close Button on MS Access Form at Runtime In this tutorial, I will show you how to toggle the close button on and off at runtime using VBA. While it's straightforward to change these settings during design time, the challenge is modifying them while your application is running - a useful feature to prevent accidental form closures during operations. I'll demonstrate the use of Windows API calls for seamless toggling of the close, minimize, and maximize buttons without having to reopen the form. This technique is beneficial for situations where background processes can be interrupted by premature closure. Join me as I walk you through the process and share some code snippets. MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, toggle close button, VBA runtime changes, control box VBA, prevent accidental form closure, form load VBA, Windows API VBA, disable close button VBA, enable close button VBA, custom save button VBA, intercept ControlF4 VBA, Access delay timer, Access on load event, VBA validation rules, API calls in Access, record navigation VBA
Intro In this video, I will show you how to toggle the close button on Microsoft Access forms at runtime using VBA, including how to handle the ControlBox and CloseButton properties and why you may want to disable the close button while background operations or record processing are taking place. You will learn how to use Windows API calls in VBA to enable or disable the close button without flicker or closing the form, and see examples of handling temporary variables and simulating background activity to control when users can interact with your forms.Transcript Today I'm going to show you how to toggle the close button. This little guy up here in the upper right-hand corner. Close, max, min, whatever you want to be on this whole thing. I'm going to show you how to toggle that on or off using VBA at runtime. It's easy to change at design time. It's a property for it, but how do you change that at runtime?
All right, today's question comes from me, because I actually wanted to do this in my own database. The reason why is because I got a form that opens up when I do my customer service. I'm not going to show it to you guys. I got it. It's messy. I'm like the auto mechanic who drives an old car. Runs great under the hood, but it's not great to look at. But it's something like this. Let's see, get your customer list here.
Now my customer will hang out there. I put a delay in there intentionally, and I'll show it to you in just a second. When I move from record to record doing customer service, it pops up the customer form next to it. So as I'm looking at your emails, I can see your customer form. And if I move to a different record, all right. I put some delay timers in here to simulate what happens. I got it to work. I'll show you what I did in just a minute.
The problem is, let me turn these delay timers off. Hang on. I didn't turn the delay timer off, but I turned off the toggling of the close boxes. So watch this as I go to another record. What happens is in the background, it goes out to the table, and it gets your information. It does some lookups to see if you have any other messages. It displays this. So there's like a good second or two of stuff that happens when I move from record to record over here before this guy loads.
Now what happens is sometimes if I'm impatient, if I click on this and then close this before everything finishes running, see that? There was an action that happened in here that messed everything up. So what I thought, well, wouldn't it be nice if I just turned this off while that background stuff is running. There's a query that runs, there's a lookup, there's some other stuff here and there. Just so I can't accidentally do that. And so that's what I spent a few hours today figuring out how to do.
Now that I got the code in there, I put it back in just now. I moved to a different record. Notice it disables those until the record, this record finishes load. If I click on Jority, I can't close this until Jority loads and this stuff is finished loading. See that? This technique, this stuff is also good if you have a situation like in my original PreventClose video. In this video, PreventClose, what I taught you how to do was shut off the ControlBox and the CloseButton and the MaximumInButtons and create your own SaveInCloseButton.
So you can control when this thing is visible. You can control if the users enter the right amount of data in here, allow them or don't allow them to close. Either way, you can also intercept the ControlF4 so they can't do that. But I thought to myself, I could do this, but there's got to be a way to turn those buttons off temporarily at least. And so that led me into a while of googling and asking GPT and doing a whole bunch of other stuff. I learned a few things about how access processes this stuff.
First of all, let me show you. Let me walk you through some of the things I did. So let's take this form. This form has nothing in it right now. You can easily come in here and you can turn off the ControlBox and the CloseButton. But that's at design time. You got to go and design mode to do that. Now, these are both properties. You'd think you can just modify these at runtime. ControlBox and CloseButton. Let's see what happens. Let me drop a button right here. Do I have a button on here? I can copy. No, let's go grab one.
Form design. Grab a button. I'll just put it up here. Cancel the wizard and we'll just put a couple of access in there. So this button, let's bring this over here. Turn this off. We don't need that. In here, let's try turning off the CloseButton. Me.closeButton equals false. Save it. Let's see what happens. This is the first thing I tried. Ready? To set this property, open the form or report and design view. Why it's even an option in here. I don't know. VB team or access team. Why even have that? I mean, I guess so you could get the property, but you can't set it here.
The next thing I tried was me. That control box. That's the control box property. Now let's try that. We close it and reopen it and hit the button. Nothing appears to happen. You can set it, but it doesn't take effect until the window, until the form is closed and reopened, but it reads it from the controls. The only way you can get this to work properly is if you save it in like a temp var or something, close the form and then reopen it. I tried that with this form. I'm here, but there's a whole lot of work involved.
I got it to work. See? I'm toggling it on and off, but it's got to close the form, save all the settings and variables that you want to save, and then reload the form, which is a bit of a pain. Hey, let me show you what I did. Here's a public cell I created called control box. Actually, let me show you the code in the main menu and then this thing first here. So when this guy loads, the on load event is the only place you can change this. Right here, me.control box. Because on load happens before the form is actually drawn on the screen.
So we're using temp bars because this has to survive the form closing and reopening again. So this line just says, all right, at the temp bars, if it's null, that hasn't been initialized yet, set it to true, the default. And then say me.control box equals whatever that value is. Do events is necessary here. So this stuff processes before form load exits and draws the form. Now my button that I put on there called toggle control box, that's just a simple toggle. What that does is it runs this guy. It's got to be in a global module because it has to be outside that form.
I declared a bunch of variables up here because you can save some properties. I turned a lot of this off just for demonstration purposes. But right here, this is where we flip the variable. So it's going to come in here either true or false. Because the form's already loaded. So it's either true or false. We're going to not it. So that's going to make it false if it's true, true if it's false. Save whatever properties you want to save. Like I just saved the record source, but you can save the filter filter on, order by what ID number you're on, all that stuff. I'm keeping it simple.
Right here we have to close the form and then reopen it again and then restore whatever properties you want. You want to restore the filter, the order by that was on. You want to jump back to the record. You can use records at clone. This works kind of sometimes. I ran into a few instances where it didn't, that gave me bookmark issues and all kinds of stuff, but it's not bad. It's just in a flicker too.
Then I decided, you know, let me break down and ask GPT what it thinks. It told me you could actually use a Windows API call to turn those buttons off on any form. Here it is. I'm going to share this with you guys for free. It's right here. I'm going to put this in the code vault. You can go and click on it if you want to and grab it.
Now a Windows API call. What does that mean? These are functions. Get window long pointer. Set window long pointer. These are in windows. Any application that uses VBA can use this. You can do this in Excel. You can do it in Word. You can do it in PowerPoint. This disable closed button is going to take two parameters. The form and whether you want to enable or disable those buttons. It uses these Windows API calls in these contents. You don't need to know all the stuff what it means. You just use it. If it's disabled, it does this. If it's enabled, it does that.
That's what I used in this guy. To turn off. I know there's a delay in there to turn off these things. Right. And back. See, let me show you what I got in here. Designed. I've got in my on current event. I'm simulating stuff happening. First, the first thing you do is disable the closed buttons. Then it do events just to make sure I just got sleep calls in here. Just making it wait. This is simulating stuff happening in the background. Going out to the server and grabbing a bunch of records. Then it opens up the customer form. Then it simulates some other stuff happening. Then it just does a simple check for that. That's okay. Then it turns the closed buttons back up.
If you get rid of the sleeps, you'll see what it actually does faster here. When you're in the sleeps. Again, this is good if you got a really slow network. Mine's not slow, but it does a lot of calculations. I'm constantly accidentally closing this form while this performs the doing something. That you can see them flashing on and off there. But if you wanted to now, you could use here, I'll put the toggle button in this one. So copy paste. I'll just make two buttons in here. This will be on. And this one will be off. Turn those control boxes on. This is a better solution. This is disable closed buttons. So right-click, build event.
Now all you got to say is disable, close button, me. And then you want to disable or enable it. This one is the on button. So we'll save false, we're enabling it. And then this one will be the opposite. That will be true. You can use this for very validation rules or any of that kind of stuff. Whatever you want to do. On, off. And it doesn't have to close the form. There's no flickering. See. It's just the Windows API call that controls all of that. Let me see if control F4 still works. Yeah, control F4 does still work. So you would have to intercept that as well. And you'd use the key down event for that. I got a video for that too.
That's pretty much it. I just wanted to share this with you guys because I spent a few hours trying to figure it out for my own database. I figured I'd share some of the headaches that I had. Yeah, even I have to take a while to figure some of this stuff out from time to time. I don't know everything about access myself. When I come across some interesting stuff, I like to share it with you guys because you're like family.
I'll put a link to that code down below. You can all go into the code vault and get it. No membership required. But if you do want to become a member, you get tons and tons of extra stuff that's in the code vault too. Plus extended cut videos. Plus you can download these databases, all kinds of extra perks. But this one's a freebie. If you want to learn more, if you like my lessons, check out my developer lessons. Got tons and tons of stuff on my website that you can use to further your access skills.
That is going to do it for today folks. That's TechHelp video. Hope you learned something. Live long and prosper my friends. I'll see you next time.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series. Platinum members get all of the previous perks plus they get all of my beginner courses. All of them from every subject. You get one free advanced or developer class every month after finishing the expert series. You can become a diamond sponsor and have your name listed on the sponsor page on my website.
That's it. Once again, my name is Richard Rost. Thank you for watching this video. Brought to you by accesslearningzone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper my friends. I'll see you next time.
TOPICS:
Toggle the close button at runtime with VBA
Use of delay timers for form operations
Control Box and Close Button properties
Control initial load states using temp vars
Limiting form actions during background processes
Implementing Windows API calls in VBA
Disabling close button using Windows API
Enable/disable form buttons without flicker
Simulating background processing with sleep calls
COMMERCIAL:
In today's video, we're going to learn how to toggle the close button on your Access forms using VBA at runtime. I'll show you the challenges of managing the ControlBox and CloseButton properties, especially when dealing with background processes and how this can prevent accidental closure of critical forms. We'll explore how to implement this using Windows API calls to create a seamless user experience without flickering or having to close and reload forms. You'll also discover how to intercept Control-F4 actions to add an extra layer of control. 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 objective of the tutorial in the video?
A. Show how to design a new form in VBA
B. Toggle the close button of a form at runtime using VBA
C. Create a custom Save and Close button in VBA
D. Display customer information from a database using VBA
Q2. Why is it necessary to disable the close button during certain operations?
A. To improve the aesthetic appearance of the form
B. To prevent users from closing the form accidentally while background operations are running
C. To enhance the speed of the application
D. To prevent unauthorized access to the database
Q3. How can the close button be toggled off at design time?
A. By using a special Windows API call
B. It cannot be toggled at design time
C. By changing the CloseButton property in design mode
D. By using a temp var to store the property
Q4. What is a Windows API call used for in this video?
A. To modify form colors
B. To create records in a database
C. To temporarily disable or enable form buttons
D. To change text fields to numeric fields
Q5. What is one of the challenges mentioned when trying to toggle the close button at runtime?
A. The form must save settings and close before the changes take effect
B. The code requires a premium software license
C. It requires changing the entire database structure
D. Runtime toggle of the close button is not supported by VBA
Q6. Which function is mentioned as allowing for using Windows API calls across multiple Microsoft applications?
A. TurnOffCloseButton
B. CreateTempVar
C. GetWindowLongPointer
D. InitializeCloseForm
Q7. Which type of event is used to perform actions before the form is drawn on the screen?
A. OnClick event
B. KeyDown event
C. OnLoad event
D. OnClose event
Q8. What does the DoEvents command ensure in the VBA code provided in the tutorial?
A. It pauses the execution of code indefinitely
B. It allows other processes to run before completing the current procedure
C. It automatically saves the form design
D. It changes the form's color scheme
Q9. What does the tutorial suggest using to maintain settings through form closures and reopens?
A. A hidden field on the form
B. An external configuration file
C. TempVars in VBA
D. A separate database table
Q10. How can users prevent the use of Control + F4 to close the form?
A. By setting a global Windows policy
B. Using the KeyDown event to intercept the keystrokes
C. By creating a new database table
D. By disabling the keyboard entirely
Answers: 1-B; 2-B; 3-C; 4-C; 5-A; 6-C; 7-C; 8-B; 9-C; 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 will guide you through toggling the close button in the upper right-hand corner of a form using VBA at runtime. This feature is easy to set at design time by adjusting a property, but altering it during runtime presents more of a challenge.
The inspiration for this tutorial came from my own need to modify a customer service database form. The form is less than pristine aesthetically but functions well, similar to how a mechanic's reliable car might not look great. While managing customer service, the form shows the relevant customer information next to emails, and moving from record to record incurs a brief delay. I've put delay timers in as a demonstration.
The issue arises when closing the form prematurely during background operations, such as data lookups, which can disrupt the intended process. To solve this, I figured I'd disable the close button while these processes were pending. It took some exploration, but I finally worked it out.
By using VBA, I now have a method where, as the form loads a new record, the close button is disabled until the record fully loads. This ensures that nothing interferes with the ongoing processes. This approach is similar to another one I previously covered in the PreventClose video, which explains controlling form interactions by managing the ControlBox and CloseButton, allowing you to control when users can close a form based on data completeness or other conditions.
Initially, I tried to toggle the ControlBox property directly in runtime, but encountered limitations. The ControlBox and CloseButton properties can't be set directly while the form is open, as changes only take effect when reopening the form. To achieve the desired effect, I had to close and reopen the form while preserving its state through temporary variables.
After much trial and error, I discovered that employing Windows API calls allows toggling the close button dynamically without closing the form. These API calls use functions like GetWindowLongPtr and SetWindowLongPtr to manage form properties across applications that support VBA.
The setup involves a procedure that disables or enables the close button by calling the API functions. This solution efficiently disables the close button during form operations and re-enables it afterward, all without flickering or requiring the form to reload. It is adaptable for various validation rules and scenarios where you need precise control over form behavior.
For those interested, I will make the code available in the code vault on my website, free for anyone to access. Although a membership is not required to access this particular resource, members receive additional benefits such as extended video cuts, downloadable database samples, and access to other exclusive content.
I hope this tutorial has provided valuable insight into managing form interactions in Access using VBA. For a detailed video tutorial including step-by-step instructions, visit my website at the link provided below.
Live long and prosper, my friends.Topic List Toggle the close button at runtime with VBA
Use of delay timers for form operations
Control Box and Close Button properties
Control initial load states using temp vars
Limiting form actions during background processes
Implementing Windows API calls in VBA
Disabling close button using Windows API
Enable/disable form buttons without flicker
Simulating background processing with sleep calls |
