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

[youtube]http://youtu.be/6VF5P7qLaEQ[/youtube]

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:
Public bolFinishedLoading As Boolean
Second, on each of your subforms open or load events add the following:
If not Me.Parent.bolFinishedLoading Then
        Exit Sub
End If
Once your main form has completed loading, switch the value to True:
bolFinishedLoading = 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:
Me.frmMyChild.Form.FinishLoading (frmMyChild is the name of your subform)
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:
If Me.Parent.bolFinishedLoading Then
        FinishLoading
Else
        Exit Sub
End If
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!