Is Form Loaded
By Richard Rost
3 years ago
How to Tell if a Form is Loaded (Open) in Access
In this Microsoft Access tutorial, I will show you how to determine whether a form is currently open (loaded) in Microsoft Access. We will use this to determine if a specific form is open so that if it is, we can update a value on it if needed.
Jeremy from Bethesda, Maryland (a Gold Member) asks: I have two forms in my database: one for a technician and another for the job that's assigned to him. On the technician form, I like to keep a count of how many jobs they have assigned. If I try to update this count from the job form and the technician form is closed, I get an error message. Is there any way to check if the technician form is open before I try to update that value?
Prerequisites
Code Vault
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Is loaded, isloaded, is form loaded, isformloaded, isopen, isformopen, is form open, how to tell if a form is loaded, how to tell if a form is open, Check if Form is open, close one form if another is closed, run-time error 2450, cannot find the referenced form
Subscribe to Is Form Loaded
Get notifications when this page is updated
Intro In this video, I will show you how to check if a form is currently open (loaded) in Microsoft Access using VBA. We'll talk about why this is useful to avoid runtime errors when updating values on another form, and demonstrate both quick error handling with "On Error Resume Next" and the preferred method using the IsLoaded property. I'll walk you through creating a global function to easily check a form's open status anywhere in your database. This video includes practical examples using customer and order forms to make the process clear.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to show you how to determine whether a form is currently open, otherwise known as loaded, in Microsoft Access. We will use this to determine if a form is open so we can update a value on it and avoid getting an error message.
Today's question comes from Jeremy in Bethesda, Maryland, one of my Gold members. Jeremy says, "I've got two forms in my database, one for a technician and the other for the jobs that are assigned to him. On the technician form, I like to keep a count of how many jobs they have assigned. If I try to update this count from the job form and the technician form is closed, I get an error message. Is there any way to check to see if the technician form is open before I try to update that value?"
Yes, this is certainly possible. There are a million different ways to handle this, so let me translate this into something that everyone else will understand. Let's do this with customers and contacts, since that's what I use in my TechHelp database, and everyone who's been watching my videos should be able to understand the analogy. But this will work the same for any two forms.
Before we get started, this is a developer-level video, so we are going to be using some VBA. If you haven't watched my intro to VBA video yet, go watch it now. This will teach you everything you need to know to get started in about 20 minutes. Optionally, go watch my Create a Function video. I'm going to show you how to use a global function so you can use this anywhere in the database. These are free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Here I am at my TechHelp free template. This is a free database. You can download a copy off my website if you want to. In here I've got Customers and I've got Contacts. Each time you talk to a customer, they get a contact. I also have Orders. Every customer gets multiple orders.
Now, let's do this with Customers and Orders. Let's say on the Customer Form you want to have an Order Count. So you know how many orders they have. You could do the same thing with the total of their orders and sum it up or whatever you want. I'm just keeping it simple for class. So let's put a calculated value down here.
Let's put in here and we'll call this the count of the orders. How many orders do they have? We'll make the name count orders or count order. I like to keep things singular. We'll use a DCount function in the control source. I'm going to zoom in so you can see it better.
This would be NZ, DCount, all the records from the order table where the customer ID equals the current customer ID. Give me a zero if it's null. If you don't know how to use DCount or NZ, I've got videos to teach you those too. I'll put links down below in the link section. I usually assume by the time you're at the developer level, you've used these functions before. If not, I have videos on them. Go watch that.
I'll hit OK. Let's get rid of that currency format so it's just a number. Save it. Let's close this and reopen it.
This customer's got two orders. If I go to the next one, one order. There's no real reason to save that value. You don't have to save it in the table because you can calculate it on the fly. There are exceptions, but generally you don't need to save that in the table. You can quickly and easily see how many orders there are.
If you were using the sum of their order total, this might give you a good example of what the customer is worth.
In any case, when we're on the Order Form, if I add a new record to this one or delete an existing record, then I want this value to refresh because users will get confused if it changes over here and this doesn't update right away.
If I come in here and add a new record, just some garbage, I'll add another one. Notice this does those two. Now I can come over here and manually refresh this by hitting F5, and now it's updated to four. But I want this to refresh whenever this updates, whenever you add a new one or you delete it.
The best way to do that is to put an event in this form. I recommend On Current. The On Current event runs when you move from record to record and when the form opens, but it also runs if you add a record or if you delete a record. It encompasses all of those different events, and you only have to put it in one spot. Yes, it's a little extra recalculation when you're just moving through the records, but that doesn't happen much, especially with a single form.
In here, I'm going to put:
Forms!CustomerF.Refresh
That tells that form to refresh any calculations that you've got going on there.
Save it, close it. Close the order form, then reopen it again. Now if I go to a new record, if I add some stuff, as soon as this record moves to another record, this updates to five. It doesn't happen while you're editing, but as soon as you're done and you move off that record, now it updates to six. That's a lot better than what we had before.
You can throw other events in here too, but this is just general. This is giving you an example of what we're doing here.
Likewise, if you come back here and delete one of these, because we've got to delete the details first and the order, as soon as you delete it, watch that and update to five. That one event covers everything.
Now, here's the problem you'll run into. This is what Jeremy is going through. If the user closes the Customer Form and I try to go to another record, there's your runtime error 2450. The database can't find the referenced form because you're trying to refresh a form that is not open. If you hit debug, it takes you right to that line.
There are two ways you can deal with this problem. There's the cheesy way, which works, and there's the better, proper way.
The cheesy way is to use some error handling and just get around that. You can come right in front of this line and say:
On Error Resume Next
That says if any line from this point on generates an error, just ignore it.
Save it. Come back out here and now I can move around with reckless abandon, and there's no big deal. It just ignores that line.
Anyone who tells you not to use On Error Resume Next, don't listen to them. It's not bad. I use it all the time, but only for very simple things. Only in little form subs like this where you've got one or two lines of code that you are expecting to generate an error message.
If you've got a big long block of code here, try to avoid that because it'll cause errors and you won't even realize you're getting an error message and stuff just doesn't work. My code doesn't work and I have no idea why, and then my code works and I have no idea why.
If you want to learn more about this, I have an error handling video. I'll put a link to that down below as well. It talks about this in some other different cases. This is the cheesy method. We're going to try not to do this. So what would be better?
Let's say for example, this is in a big block of code. You don't want to do that. The better method, the proper method, is to check to see if that form is open.
We can do that by looking at the form's IsLoaded property, but it's not just that easy. It's a little bit more involved than that.
We can say:
If CurrentProject.AllForms("CustomerF").IsLoaded Then
' do that
End If
That's how you check to see if a form is loaded.
Why the nice guys at Microsoft didn't just make it, you know, Forms!CustomerF.IsLoaded, I don't know, but you have to go through this. It's hard to remember, but this does the same thing.
If I go through here now, look, it checks to see if that form is loaded and it's not generating the error. If the form is open, it'll still work for us. If we come down here and we add another record, our code should still run and we're back up to six. That's the better way, but I never remember this. I always end up having to look it up. So what I do is I take this and I make it a function.
In fact, I put this function in my code vault and I've opened this page up. This is now free for everybody. You can come in here and get this code. It's right here. Here's a global function you can use, because now all you have to remember is IsLoaded.
You put this in a global module and you can use it anywhere in your database. You don't have to remember this whole CurrentProject.AllForms stuff. Just copy that. I'll put a link to this page down below. Look for the "Is Form Loaded" code link in the links section. This will take you to the code vault, which normally you have to be a Gold member for, but I opened up this page for everybody as a little teaser for you.
Copy this to the clipboard. Come back to your database. If you have a global module like I do here, you can just drop it in here. If not, create a new one. Create a module, not a class module. Just a module.
I'll put it in my GlobalMod, and I just paste it right here. Public Function IsLoaded. Now IsLoaded(FormName) will return that value. You don't have to remember all that. You can just come in here now and close that. Instead of all this, you could say:
If IsLoaded("CustomerF") Then
' do that
End If
Fix the capitalization.
In fact, you can just do a one-line solution like that there.
Save it. Give it a quick debug compile. Now we can move through like this. That's the wrong form. We can move like this. It sees it. It's open. If I delete a record, it should go down to five. And everything works fine. If I close this guy and delete another one, it should avoid any error messages. Perfect.
There you go, IsLoaded function. I hope that answers your question, Jeremy. For the rest of you, I hope that helps you out, you learned something, and you see what kind of goodies are in my code vault. There are a lot of goodies in there.
That is 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.Quiz Q1. What problem was Jeremy experiencing in his Microsoft Access database related to forms?
A. He was unable to add new technicians to the database
B. He received an error when trying to update a value on a closed form
C. He could not delete records from the jobs table
D. He was unable to use the DCount function properly
Q2. Which function is used in the video to calculate the total number of orders for a customer in a form control source?
A. DSum
B. DMax
C. DCount
D. DLookup
Q3. Why is it generally unnecessary to store calculated values, like order count, in a table in Access?
A. Storing calculated values takes up too much space
B. Calculated values can change frequently and are best recalculated on demand
C. Access does not allow calculated fields in tables
D. It is required by Microsoft Access best practices
Q4. What event does the video recommend using to update calculated values when records are added, deleted, or moved in a form?
A. On Open
B. On Load
C. On Current
D. After Update
Q5. What is the VBA command shown in the video to force another form to refresh its displayed calculations or controls?
A. Forms!CustomerF.Requery
B. Forms!CustomerF.Refresh
C. CustomerF.Update
D. RefreshForm("CustomerF")
Q6. What error occurs if you try to reference a form in VBA that is not currently open?
A. Compile error
B. Runtime error 1004
C. Syntax error
D. Runtime error 2450
Q7. What is the "cheesy" method for avoiding an error when trying to reference a closed form, as described in the video?
A. Using the If Then statement before the code
B. Adding On Error Resume Next before the code
C. Using a try-catch block in VBA
D. Disabling the form
Q8. According to the video, why should "On Error Resume Next" be used sparingly?
A. It slows down code execution
B. It hides errors in large blocks of code, making debugging difficult
C. Users cannot see error messages
D. VBA does not support it reliably
Q9. Which is the better, more proper way to determine if a form is currently open in Access VBA?
A. Try to close the form and see if an error occurs
B. Check the form's record source
C. Use the IsLoaded property of the AllForms collection
D. Use a global variable
Q10. What does the IsLoaded function, as described in the video, allow you to do more easily?
A. Open multiple forms at once
B. Check if a form is open by simply passing its name to the function
C. Automatically refresh data in all open forms
D. Convert forms to reports
Q11. According to the video, how should you implement the IsLoaded function for use anywhere in your database?
A. Create a macro with the function's code
B. Enter the code in a class module
C. Add the function to a standard (global) module
D. Put the code in a table rule
Q12. If you properly check if a form is loaded before referencing it, what is the main benefit according to the tutorial?
A. The form loads faster
B. It prevents runtime errors when the form is closed
C. The database becomes multi-user ready
D. The DCount function works better
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-D; 7-B; 8-B; 9-C; 10-B; 11-C; 12-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 focuses on determining whether a form is currently open, or loaded, in Microsoft Access. This is important for situations where you need to update values on a form only if it is open, in order to avoid errors.
A common scenario involves having two forms, such as a Technician form and a Jobs form, where you want to keep an updated count of jobs assigned to each technician. The problem arises if you attempt to update this count from the Jobs form when the Technician form is closed, which results in an error. The solution is to check whether the form is open before attempting any updates.
To make this example relatable, I will use customers and orders in the TechHelp sample database. This method works the same way for any pair of forms in Access.
Before continuing, I want you to be aware that this is a developer-level concept and it requires some knowledge of VBA. If this is your first time using VBA, I recommend watching my introduction to VBA video and my video on creating functions, both of which are freely available on my website and YouTube channel.
In the TechHelp sample database, each customer can have multiple orders. Suppose you want to display an Order Count on the Customer Form so you know how many orders each customer has. This value can be calculated using the DCount function and the NZ function. These functions will count the number of orders related to each customer and handle null values by returning zero when no orders exist. If you need a refresher on DCount or NZ, I have specific videos on those topics as well.
Once the count is displayed in a text box on the Customer Form, it will accurately reflect the number of orders for each customer as you navigate through records. Generally, it is best to calculate this value on the fly rather than storing it in the table.
Next, you will probably want the Order Count to update automatically whenever a new order is added or deleted, rather than forcing users to manually refresh the form. The best way to handle this is to use the On Current event of the Orders form. This event is triggered when moving between records, opening the form, adding a new record, or deleting a record. By placing the correct code in this event, you ensure that the Customer Form refreshes any relevant calculations whenever necessary.
The code needed will tell the Customer Form to refresh, causing the Order Count field to update after any change in the Orders form. After saving your work and testing it, you will see that the Order Count is updated automatically when records are added or deleted, as soon as you move off the current record.
However, if the Customer Form is closed and you try to execute this refresh code, you will experience runtime error 2450 because Access cannot find the referenced form. This is a common pitfall.
There are two main ways to handle this. The first, which I call the "cheesy" method, uses On Error Resume Next before the code. This approach tells Access to ignore any errors that occur afterwards. This can be acceptable for short routines with only one or two lines you expect might fail, such as refreshing a form that might not be open. However, for larger code blocks, this technique is not recommended because it can lead to hidden errors that are difficult to diagnose.
A better and more professional approach is to check whether the form is open before running the code. You can do this by checking the IsLoaded property of the form using CurrentProject.AllForms. If the form is loaded, then you can proceed with your refresh or update. Unfortunately, the syntax is cumbersome and not easy to remember.
To make this process easier, I recommend wrapping this check into a global function called IsLoaded. By creating a simple function in a global module in your database, you only need to remember to call IsLoaded with the name of the form you want to check. This function returns True if the form is open and False if it is not. I have made the code for this function available in my code vault, free for everyone. You can copy it and add it to your own database in a global module.
Once this function is in place, you can replace your previous code with a simple conditional check using IsLoaded, followed by your refresh or update routine. This streamlines your code and makes it much easier to maintain.
With this setup, your forms will only attempt to refresh or update values when the target form is open, and you will avoid any runtime errors. Everything works smoothly whether the form is open or closed.
I hope this answer provides you with a solid method for checking if a form is open in Access and helps you keep your applications running smoothly. 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 Determining if a form is open in Microsoft Access
Using DCount and NZ to calculate related records
Displaying order count on a customer form
Refreshing a form value after data changes
Using the On Current event to update form fields
Adding error handling with On Error Resume Next
Checking if a form is loaded with CurrentProject.AllForms
Creating a global IsLoaded function in a module
Using the IsLoaded function to prevent runtime errors |
