Fitness 62
By Richard Rost
44 days ago
Using a Macro to Prevent Multiple Form Load Conflicts
In this Microsoft Access tutorial, I will show you how to prevent load conflicts when opening multiple forms at the same time by using a macro. We'll talk about common timing errors that can occur when loading multiple forms on startup or with a button, and I'll walk you through how to set up your AutoExec macro so that forms load in the correct order without errors. This is part 62.
Members
There 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!
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, prevent load conflicts, open multiple forms, timing conflict, macro, VBA code, AutoExec macro, trusted folder, security warning, NotTrustedF, OpenForm, event procedures, OnCurrent event, OnLoad event, OnDirty event, main menu, FoodLogF, tab order, TechHelp free template
Subscribe to Fitness 62
Get notifications when this page is updated
Intro
In lesson 62, I will show you how to prevent load conflicts when opening multiple forms at the same time in Microsoft Access. We will look at common timing issues that can happen when starting your database or using buttons to load more than one form, and I will teach you how to solve these problems using a macro. You will also see how to adjust your AutoExec macro to ensure forms open in the correct order without errors, making your database more reliable. This is lesson 62.
Transcript
Today I'm going to teach you how to prevent load conflicts if you try to open multiple forms at the same time.
Now, this can happen when you start up the database or if you've got a button in your database that tries to load two different forms and you run into a random error. Usually, it's a timing conflict. Today, I'm going to show you how to fix it with a macro. Tomorrow, I'm going to teach you how to fix it with some VBA code. These are two slightly different techniques. I like the VBA code better, but the macro is actually easier. So it's just whichever way you prefer doing it.
Now, this is part 62 of my fitness database series, but it doesn't matter if you're into fitness or not. If you don't care about tracking calories, macros, exercises, or any of that stuff, the point of this database series is for me to teach you cool tips, tricks, and techniques, and I'm just loading them in here. It doesn't matter if you're doing customers, orders, inventory, whatever your database happens to be. These are awesome tips and tricks to put in your database.
I actually got a comment from one member who said he was canceling his membership because I won't end this fitness database series. Well, I'm sorry. I've gotten tons of positive feedback on it, and after I mentioned that in the last Quick Queries video, I heard from a dozen people who said they joined as a member because of this fitness database series.
So I hope you're enjoying it. I hope you're getting something out of it. As always, post your thoughts below. I always want to read your comments. I'm having a lot of fun with this. Yes, I'm building it for my own use too, but I'm throwing in all kinds of cool tips, tricks, and techniques like the one today, and they can be applied to any database. It's easier if I have a working database that I can put this stuff in. I can't just start from scratch like the TechHelp free template every time because these are a little more advanced things. So we need a database that we can work with, and this is a great example of one.
All right, enough of that. Let's get to today's content.
One thing that I've done since the last video, sometimes I do a little off-camera edit if it's just no big deal. I fixed the tab order on the food list on this guy. We haven't used this one in a while, but down here, the tab order was just jumping around. So I came in here. Now, I want it to go down, down, down, down like that and then over to here and so on. That's all I did. I edited it off camera and I don't want anybody to get confused if you download the database and it's not working the exact same way. I try to keep a list of all the things that change off camera.
For today, what I'd like to do, now that this thing is pretty much built and established, is that nine times out of ten when I open this database, I want it to go right to the food log because that's what I generally do. I open it up multiple times a day. I want it to go straight to the food log, which does open to today, so that's a time saver right there. But I want this guy to open when the database opens, so I'm going to throw it right in the main menu here when it opens.
Let's go into design view, open this guy up, and let's go into its unload event. Here's some stuff that runs when it loads. So down here, I'm going to say DoCmd.OpenForm and it's the FoodLogF. OK. Save it. Debug, compile, everything looks good.
I'm going to close this. Close this. Close it. Let me close that. Shut the database down. All right, here's my copy that I'm working with. Let's open it back up and make sure everything works.
Fitness database can't find the field 'FoodDay'. What? All right, let's debug it. OK. Let's go into my MemberMod, ChangeSortF controls. Why? What's going on here? This obviously works. Let's stop it. Let's close this. Can I open it right from here? OK, it works from there. Why won't it run when this guy opens?
We can simulate restarting the database just by closing the main menu and reopening it. I have a button right here that does it, or you can do it from the navigation pane. But the same problem is happening.
Now, if you look at it, the line that it stops the compiler on doesn't really do it justice as far as telling you what the problem is. But essentially what's happening is this guy here has all kinds of code that runs when it loads. If you look in here, you've got all these event procedures. Something happens in the OnCurrent event, the OnLoad event, the OnDirty event, and all these different things.
So essentially what's happening is this guy is trying to load this guy before this guy is completely finished. So one of the events is running, and it's trying to run before all the fields are loaded, and it's just a nightmare. It's a problem. You will run into this a lot if you try to have one form that's opened and, in its event, try to open another form before this form is fully loaded. There's all kinds of stuff happening behind the scenes, and it just gets confused.
So how do we fix it? Well, sometimes you can't just load a second form in the first form's load event because this form isn't fully finished loading yet. All right. So there are a couple of things we can do. There's an easy trick and there's a little more difficult, but I think better, trick.
Now, the easy trick in this particular case involves using a macro. I know I don't teach a lot about macros because, honestly, I'm not a big fan of macros, but this is one case in which macros actually can do a pretty good job.
For this database, I actually do have an AutoExec macro. I'm not using a startup form. The reason why is because if we look at this guy, this will be in the TechHelp free template if you download the latest version. The reason being is because I have this thing that I added to check to see if the database is in a trusted folder.
The number one complaint I get, especially from beginners, is they download the TechHelp free template, try to open it, and they get that security warning that says this is not a trusted database, but it is. So this thing just simply opens up a NotTrustedF, which is right here. It says it looks like you're not running this database from a trusted location. Some of you might have seen it. Then there's a link that will take you to my video that explains that it's not a trusted location, and it tells you how to do it.
So, what we can do is, if we are opening in a trusted folder, we open the main menu as the next step. All I'm going to do is sneak another step in after that. Load this, then the macro engine will wait until this guy is loaded, and then it will load the next one. So we'll just come down here and do an OpenForm. What's the form name? It's FoodLogF. All we have to do is slide this guy up into here.
If you look at it, you've got: if it's trusted, open the main menu, then open the food log. Otherwise, open up this guy. Then that's it. Now, save it, close it, close it, open it up, and now they both should load side by side there because the macro engine waits. It says, OK, this guy is loaded, now we'll load this guy, and you don't run into that timing problem with one form trying to load another form and then this form trying to do stuff before the first form is even fully loaded.
That's one way to get around it. The second way to get around it is to use a little bit of trickiness in your VBA code, but I actually like the second mode better, A because I'm not a big fan of macros, and B because you get more control from a programming standpoint. We'll talk about that in tomorrow's video.
So tune in tomorrow, same bat time, same bat channel. Members, of course, can watch it right now because I'm going to record it in just a few minutes.
That's going to do it for your TechHelp video for today. Post a comment down below, let me know how you liked today's video. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 63.
Quiz
Q1. What is the main issue that occurs when trying to open multiple forms at the same time in an Access database?
A. It causes a timing conflict that can lead to random errors
B. The database cannot store the forms correctly
C. The forms display on top of each other
D. The forms automatically close each other
Q2. In the context of the video, which technique is explained as the easier way to avoid load conflicts?
A. Using VBA code
B. Editing the database options
C. Using a macro
D. Duplicating form events
Q3. Why does opening a second form in the load event of the first form often cause problems?
A. Macros cannot execute properly within events
B. The first form might not be fully loaded before the second starts loading
C. Forms cannot be opened in succession at all
D. Access does not allow multiple forms to be opened from events
Q4. What is the key advantage of using a macro in this scenario, as opposed to VBA?
A. Macros are faster to load
B. The macro engine waits for each step to complete before moving on
C. Macros prevent users from closing forms unexpectedly
D. Macros improve form layout automatically
Q5. According to the transcript, what is the macro named that runs automatically when the database opens?
A. StartUpMacro
B. LoadForm
C. AutoExec
D. FirstRun
Q6. How does the example macro determine if the main form (main menu) should open?
A. If the FoodLogF form is empty
B. If the database is in a trusted folder
C. If the user is an administrator
D. If no other forms are currently open
Q7. What alternative does the presenter promise to explain for handling form load conflicts in the next video?
A. Using a query to delay loading
B. Rearranging the tab order
C. Using VBA code for better control
D. Deleting form events
Q8. Why does the presenter continue using the fitness database series for his tutorials?
A. He wants to focus only on fitness applications
B. He lost access to other databases
C. It provides a working example to implement advanced techniques
D. Database templates are no longer available
Q9. In what way does the macro technique prevent timing conflicts between two forms?
A. By ensuring forms are loaded in the background first
B. By disabling events on all forms
C. By enforcing a sequential loading process
D. By automatically closing the first form before opening the second
Q10. What feedback did the presenter receive regarding his fitness database series?
A. The majority wanted him to stop the series
B. He was told to remove all macros
C. Many people joined as members because of the series
D. Users found macros too confusing
Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-C; 10-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 will teach you how to prevent load conflicts when opening multiple forms at the same time in Microsoft Access.
This situation often comes up when starting your database or if you have a button that is programmed to open more than one form at once. You might see intermittent, confusing errors, and most often these are related to timing conflicts as the forms try to load simultaneously. In this lesson, I will show you how to avoid these problems using a macro approach. In the next lesson, I will walk you through a VBA solution, which I personally prefer for the additional control it offers, even though the macro method is simpler to set up. You can choose the approach that works best for you.
Although this is part 62 of my fitness database series, the content here is useful for any kind of Access database, not just those related to fitness, exercise, or nutrition tracking. The purpose of this series is to share practical tips, tricks, and techniques that anyone can incorporate into their own databases, whether you are working with customers, orders, inventory, or anything else.
Some viewers have commented about the ongoing length of this fitness series, but the overwhelming response has been positive. Many people have actually joined as members specifically because of this series, and that positive feedback encourages me to keep going and to continue introducing new concepts in each installment. I am also using this database for my own personal needs, and it makes a great platform for demonstrating more advanced Access techniques. If you have any feedback, please share your thoughts in the comments. I always enjoy hearing from you.
Now, on to today's topic.
Before we get started, I want to mention a small change I made off-camera since the previous video. I adjusted the tab order on the food list form to make data entry more seamless. If you notice this difference after downloading the database, that is the reason for it. I keep a record of these minor updates so you do not get confused if things work slightly differently on your end.
What I want to focus on today is ensuring that the Food Log form opens automatically each time I launch this database. Since the Food Log is what I use most frequently, I want the application to take me straight to this form as soon as it opens. To accomplish this, I will place the command to open the Food Log right in the main menu's events so it launches at startup.
To implement this, I start by entering the form's design view and accessing the appropriate event, such as the load event, to trigger the command to open the Food Log form. After saving and compiling the database, I test the change by closing and reopening the database to see if it works as intended.
However, this process reveals a problem. When the database attempts to open the Food Log form in this way, I get an error message about a missing field, specifically 'FoodDay'. Upon debugging, I confirm that the Food Log form itself works when opened directly, so the issue stems from the way it is being loaded from within another form's event.
This conflict occurs because the main menu form might not be fully loaded when it tries to open the second form. There are multiple event procedures running, such as OnCurrent and OnLoad, and if one form tries to open another before it has finished loading completely, Access can become confused and fail to properly populate the new form's fields. This is a common issue if you try to load multiple forms in quick succession through form events.
To address this, it is important not to load a second form from within the first form's load event, since the form is not entirely finished with its own loading process. To solve the problem, we have two options: a macro-based solution and a VBA-based solution.
The macro approach is the easiest fix in this case. Although I do not usually use macros in my Access projects, this particular scenario is one where a macro is very effective. In this database, I am already using an AutoExec macro rather than a startup form because I have a built-in check to see if the database is running from a trusted location. This prevents frequent security warnings that new users often encounter. If Access detects that the database is not in a trusted folder, it opens a special form explaining how to fix this issue.
To implement our solution, all I have to do is modify the AutoExec macro. If the database is running in a trusted location, the macro first opens the main menu, then immediately loads the Food Log form as an additional step. The macro engine ensures that the first form is fully loaded before starting the next one, which prevents timing conflicts. This simple adjustment allows both forms to open smoothly side by side without errors, since the macro waits for one form to finish loading before moving to the next.
While this macro solution is quick and effective, I prefer using VBA for these situations. VBA provides more control and flexibility from a programming perspective. In the next video, I will show you how to handle this scenario using a VBA-only approach.
This wraps up today's TechHelp tutorial. As always, feel free to leave comments and let me know what you thought about today's lesson. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Preventing form load conflicts in Access
Explaining timing issues when opening multiple forms
Using macros to control form loading order
Editing the AutoExec macro to open multiple forms
Using macro steps to load forms sequentially
Bypassing startup form in favor of AutoExec macro
Checking for trusted folder before opening main menu
Adding additional forms to open after main menu
Ensuring both forms load correctly without errors
Troubleshooting field not found errors on form load
Article
Sometimes in Microsoft Access databases, you might want to open multiple forms automatically when the database starts or when clicking a button. However, you can run into what is called a load conflict or a timing conflict. This happens when one form tries to open another form before it has completely finished loading itself. This timing issue can cause random errors or odd behavior, especially if the forms have complex event procedures running when they load.
For example, suppose you want your database to automatically open your main menu and your food log form every time the database starts. Maybe you try to accomplish this by putting code like DoCmd.OpenForm "FoodLogF" in your main menu's load event. You save everything, close your database, and open it up again. Sometimes it works, but other times you get a strange error message, maybe about a missing field, or nothing seems to happen at all. The issue is that Access is trying to start loading the second form before the first one has completely finished, which causes a conflict between their various event procedures. The forms might try to interact with each other or the underlying database tables before they are fully available.
This problem comes up quite often and it is not limited to fitness databases or any particular type of database. This timing conflict can happen in any scenario where you try to open multiple forms at once, whether you are building a customer management system, inventory database, or any other type of Access application.
The reason for the conflict is that Access processes the events of opening a form in a specific order, and if you trigger another form opening from within one of these events (like OnLoad or OnCurrent), Access sometimes does not finish processing the first form before moving on to the second. That is when fields might not be available yet or control logic hasn't finished.
To avoid this, rather than trying to open another form from within a form's load event, you are better off opening both forms at startup with a macro. The macro engine in Access processes actions in sequence and waits for one form to finish loading before starting the next action. This makes it much less likely that you will run into timing issues.
To set this up, you can use the special macro in Access called AutoExec. This macro runs automatically whenever your database is opened. You might already be using an AutoExec macro, for example, to check if your database is running from a trusted folder. If you are not, you can easily create one.
Suppose your current AutoExec macro checks if the database is in a trusted folder. If it is, it opens the Main Menu form. If it is not trusted, it opens a NotTrustedF form that informs users about running the database from a trusted location.
To open the FoodLogF form automatically after the Main Menu, simply add a new action to your AutoExec macro—another OpenForm action—after the one that opens the main menu. The macro actions might look like this:
If (IsTrustedLocation)
OpenForm MainMenuF
OpenForm FoodLogF
Else
OpenForm NotTrustedF
What is important is that the OpenForm for FoodLogF comes directly after the one for MainMenuF. Access's macro engine is designed to open the first form, wait for it to finish, and then move on to the next action. This sequence avoids the timing conflict you get when VBA tries to open a form from within another form's load event.
Now, when you open the database, the autoexec macro checks if the database is trusted and opens the main menu. Then, once the main menu has finished loading, the FoodLogF form is opened, and both forms appear side by side. No error messages and no random behavior due to timing issues.
This solution with a macro is quick and easy, and it works well in most situations where you need to open two or more forms automatically. You do not have to stick with macros if you would rather use VBA for greater control, but for this common startup scenario, using the AutoExec macro is both effective and simple.
The takeaway for any Access developer is this: if you need to open multiple forms at database startup or from somewhere else, do not try to open one form from another form's load event. Let a macro do the work instead and you will avoid those confusing load conflicts. This tip will make your database more reliable and easier to manage, no matter what type of data you are tracking.
|
