Go to Top

Avoid the Groundhog Day Effect: Delay Loading Subforms in Access Part 2

We use subforms in Access a lot around here. Sometimes, we may have upwards of 5 or 6 subforms per form. Adding that many can be a real drag on performance if you’re not careful. This is part one of two posts on the topic. Ben will talk about disabling the loading of subforms on startup in our next post.

2

Children Come First
Adding subforms will alter the event sequence of your application; instead of your main form firing off first, your subforms take over and fire their events then your main form events will go off. Here is what you might expect the sequence would be:

  • Open Event Main Form
  • Load Event Main Form
  • Open Event Sub Form x
  • Load Event Sub Form x

Instead the order of events changes:

  • Open Event Sub Form x
  • Load Event Sub Form x
  • Open Event Main Form
  • Load Event Main Form

Groundhog Day Effect

If you’re not careful, your subforms open and load events will fire off several times under certain conditions, slowing your app down and making you feel like you’re in the ‘Groundhog Day’ movie. For example, if you change your forms recordsource on the open event of the main form, the sequence will fire like so:

  • All of your subforms open and load events execute
  • Your main form open event fires off and changes the recordsource
  • Your subforms fire off again
  • Control comes back to the main form and now it filters the records
  • Your subforms fire off again
  • Control comes back to the main form and the rest of the code is executed

The above sequence can mean your application can take several seconds to load a form, much longer if it’s reading data from the cloud. The cycles just keep repeating based on what your main form does.

Use a Public Variable Stored on the Parent Form to Control Flow

You can’t change the behavior of Access but you can minimize the impact in two ways: by separating code into their own procedures and by using a global variable on the main form. You can then check the value of the variable on your subforms and stop execution until the main form has finished loading.

Separating Out Code

It’s quite tempting to do everything all at once in a single event and often you can, but as stated earlier, doing certain actions in an event may cause the cascading of events that may only serve to muddy the waters and slow things down. Therefore, a general rule of thumb should be adhered to for assigning code:

Use WhereCondition to perform filters upon the form via DoCmd.OpenForm whenever possible.
Use Open event only to check if the form is allowed to open, nothing more.
Use Load event to set up defaults and if appropriate, change recordsource.
Use Current event to manipulate the current record only.

This typically helps in most circumstances but sometimes we have more complicated forms where we need to control the order and that’s where the next item comes in…

Using Public Variable

First, add a module level variable to the main form:

Second, on each of your subforms open or load events add the following:

Once your main form has completed loading, switch the value to True:

You may then want to call subform’s loading routine especially if it needs to be performed after the parent form has finished loading. You typically can do so like this:

provided that your subform has a public procedure named FinishLoading.

You also may want to call FinishLoading procedure from the subform’s Load event especially if you reload the subform after the parent form has finished loading, in which case you will want the code to look like this instead:

With this special procedure, you can ensure that the additional loading code is always executed whenever the subform’s Load event fires or whenever the parent form’s Load event fires but not when the parent form is still loading.

Happy Holidays!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

8 Responses to "Avoid the Groundhog Day Effect: Delay Loading Subforms in Access Part 2"

  • glBeatriz
    December 13, 2016 - 5:27 pm Reply

    Wonderful 🙂

  • grovelli
    December 31, 2013 - 5:17 am Reply

    Thanks Ben, which procedure in the parent form’s module would host Me.frmMyChild.Form.FinishLoading? You also say, “You also may want to call FinishLoading procedure from the subform’s Load event especially if you reload the subform after the parent form finished loading”; in which case would you reload the subform after the parent form has finished loading?

    • Ben Clothier
      December 31, 2013 - 8:14 pm Reply

      Grovelli- This really depends on what your subform’s FinishLoading has and when it need to be run. As an example, if it’s deferred until after parent’s Load event, then you might want to call Me.frmMyChild.Form.FinishLoading right after you set bolFinishLoading=True in the parent’s Load event. You have to decide if that is the appropriate time or if there might be other point that it needs to be called.

      As for scenarios where subform get reloaded — an example of this is changing the subform’s SourceObject. For example, you might want to have a single subform control that changes the view based on which button on the parent form is pressed.

      I hope that helps.

  • Frank R. Ruperto
    December 23, 2013 - 1:45 pm Reply

    My current app has a customer info form (parent) with a contracts sub-form (child). Performance lagged when users would scroll through customers because each customers contracts where physically scattered throughout the data file. So I created a CLUSTERED INDEX on the contracts table foreign key and the lag disappeared!

  • grovelli
    December 19, 2013 - 8:42 pm Reply

    you say you may want to call FinishLoading procedure from the subform’s Load event by using
    If Me.Parent.bolFinishedLoading Then
    FinishLoading
    Else
    Exit Sub
    End If
    so where is Me.frmMyChild.Form.FinishLoading used? And how do you know the main form has completed loading so you can set bolFinishedLoading = True
    ?

    • Ben Clothier
      December 30, 2013 - 9:18 pm Reply

      grovelli,

      The code you quoted is run in the frmMyChild’s Load event, so the “FinishLoading” in that module is actually the same procedure that “Me.frmMyChild.Form.FinishLoading” calls. The difference is that you use the “FinishLoading” inside the frmMyChild’s module but the “Me.frmMyChild.Form.FinishLoading” in the parent form’s module.

      As for the bolFinishedLoading, you set it during the parent form’s Load event, perhaps as the last line of the statement.

      I hope that helps.

  • Keimpsk
    December 19, 2013 - 8:19 pm Reply

    Great article. I’ve come across this bizar mix-up of events when adding subforms myself as well. It took me ages to realize that the errors I was getting in subforms code were due to the main form not having fully loaded yet.

    “Once your main form has completed loading, switch the value to True”

    Where or when can I see that the Main form has finished loading?

    • Ben Clothier
      December 30, 2013 - 9:20 pm Reply

      Keimpsk,

      As I told grovelli, that’d be set during the Main form’s Load event, usually as the last line within the Load event. Note that you do have to manually call FinishLoading procedure after you set the bolFinishLoading to true. Did that clarify?

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.