In part one, Juan talked about the sequence of events fired when loading a form with a number of subforms in Access and how one can reduce the cascading effects of events being re-fired due to operations performed during the load. We will talk about how we can delay loading the subforms. By putting off the load of the subform, we not only ensure that the parent form can fully load first but also we reduce the overall burden by only using the subform that the user actually requests.
Built-In Solution
If you’re on Access 2010 or newer, and you’re working on a new project, you should definitely look at the Navigation Control introduced in Access 2010. It’s basically a tab control and a subform control combined into a powerful navigation system. Best of all, it enables delayed loading all for free, with no coding required on your part. Each time you switch a tab on the navigation control, the old subform is unloaded then the new subform gets loaded. This is a very good thing if your users usually need to visit a subform once and don’t need to flip between subforms that often.
However, navigation control isn’t always a perfect fit. For one thing, you might have to support Access 2007 or earlier. Other consideration is if you need two subforms to be able to interact with each other, you can’t really do that with a navigation control which always has a single subform loaded at all time. Finally, maybe your user workflow is such that they do need to be able to flip between two subforms quickly and therefore would prefer to trade in slower initial loading time for faster time to flip between two tabs or have one main subform loaded at all times and delay load only the additional tabs.
Doing it the Old School Way
Most of time, when a form has multiple subforms, we’ll be putting them on a tabcontrol, each tab page housing a single subform. Note that it is also possible to use a single subform control with an option group of buttons or similar control(s) instead which gives us very similar behavior to navigation control but also the same problem — inability to load & cache a subform. So for the rest of the article, we’ll be working with the tab control + subform control in each tab page design pattern.
The first step is to not load any form into the subform control. Typically, we’d set the SourceObject property to a form or a report on a subform control. In this case, we’d blank it out.
We will populate the SourceObject property via the tab control’s Change Event. The minimum code to accomplish this would be something like this:
Private Sub tabCtl_Change()
Dim ctlSubForm As Access.Subform
Dim strSource As String
Select Case tabCtl.Value
Case 1 ‘Second Page
Set ctlSubForm = Me.frmCustomers
strSource = “frmCustomers”
Case 2 ‘Third Page
Set ctlSubForm = Me.frmOrders
strSource = “frmOrders”
Case 3 ‘Fourth Page
Set ctlSubForm = Me.frmTransactions
strSource = “frmTransactions”
End Select
If Len(ctlSubForm.SourceObject) = 0 Then
ctlSubForm.SourceObject = strSource
End If
End Sub
With this change, we are able to delay the loading and ensure that loading only occurs exactly once as long as the parent form is kept open. This approach helps us spread out the cost of loading a subform so that the parent form can load quickly and only delay a bit longer when the user selects one of the tabs.
Frequently, one of those tabs is also the default tab that you want to show on the page so in this case there might be no point of delay-loading this one subform. In this scenario, we would just leave the SourceObject property populated and skipping. This is why the sample code shown deals with the 2nd page and onward, rather than with the first page.
Whether to Link or Not to Link, That is the Question
This sample also assumed that we would be using the built-in form-subform linking by leaving in the LinkMasterFields and LinkChildFields properties filled. However, we’ve found that sometimes we need the extra control such as being able to specify more specific filter that is set by the user on the parent. Sometimes it’s just that the linking is not needed, especially on the main form where a user may start different workflows that are unrelated to each other.
In this scenario, we would also blank out both LinkMasterFields and LinkChildFields. Then, for each subform where we want to manage, we set their initial recordsource to equivalent of “SELECT * FROM tblSource WHERE 1 = 0;” As Juan discussed in the first part, using the impossible criteria “1 = 0” ensures that the form loads quickly without pulling any records so that we can then apply filter. This is necessary because with a subform, we have no way to pass in filters until after subform has loaded. The sample code then changes to something like this:
If Len(ctlSubForm.SourceObject) = 0 Then
ctlSubForm.SourceObject = strSource
'At this point the subform is fully loaded
If Len(strRecordSource) Then
ctlSubForm.Form.Recordsource = strRecordSource
End If
End If
Cleaning Up After Yourself
This is only a start but hopefully by now, you have a good idea of how far you can go with the manual control of the subform’s loading and if necessary, the filtering. However, when the parent form has navigated to a different record, and you either find that the linked subforms that are already loaded take too much time or that unlinked subforms are now out of synchronization. We need to deal with this situation. The problem with the linked subform is that unless you use a custom navigation system, you cannot easily intercept when the users have clicked the navigation bar’s buttons to navigate to another record. Unloading the subform on a parent form’s Current event would be too late because the navigation would force the linked subform to re-filter. If you do not want to use a custom navigation system then you may have to consider using unlinked subform and managing the filtering yourself.
For this example, we will presume that navigating to a new record on the parent form requires that we unload all unlinked subforms and also set the user back on the first page, requiring the user to re-select a tab in order to fire the Change event and therefore load the subform. Here’s the code you could put in the parent form’s Current event:
Private Sub Form_Current()
Static varBookmark As Variant
Select Case True
Case Me.NewRecord, varBookmark <> Me.Bookmark
Me.frmCustomers.SourceObject = vbNullString
Me.frmOrders.SourceObject = vbNullString
Me.frmTransactions.SourceObject = vbNullString
Me.tabCtl.Value = 0
If Me.NewRecord Then
varBookmark = Null
Else
varBookmark = Me.Bookmark
End If
End Select
End Sub
It may be the case that in some workflows, the users wants to be able to stay on the same tab when navigating to a different parent record. This is easy to accomplish by checking the value of the tab control and skipping the setting of subform’s SourceObject property to vbNullString.
Conclusion
Hopefully, the samples provided will give you a good idea of how you can improve the user’s experience by taking more control of how the subform loads and if necessary, unlink the subform and manage the filter yourself. If you have any better ideas or want to share experience, please do in our comments.
Happy subforming!
Thanks for the very helpful article. We’re working on upgrading a very large Access 2003 application and have run into this very problem in Access 2010, 2013 and 2016. Subforms load in an inconsistent order, meaning sometimes everything works fine and the next time you get strange errors that never happened in Access 2003. We’re in the process of trying this solution to confirm it will solve these problems for us.
We’ve actually used the blank SourceObject propery for many subform objects already, but for a different reason. Our application has numerous subforms. Many of our parent forms have tabs with about 15 subforms. We found that if our users kept several parent forms open, they’d eventually get out of memory errors, due to each form and subform object consuming a window handle. Keeping the subform control’s SourceObject property blank avoids using a window handle until you actually set that property to a form object. We now set SourceObject to blank for almost all subforms on tab pages that aren’t visible.
This helps our parent form to load quickly. Switching between tab pages has a slight delay, but users can now keep many more forms open than before!