System Resource Exceeded Error Message
Lately we’ve been seeing a barrage of clients getting “System Resource Exceeded” when migrating to Windows 10, even though the system worked fine in prior versions of Windows. In some cases we’ve traced the issue to apps that use a lot of subforms in a tab control, so I’m going to describe a technique that will prevent the issue in those cases when each tab hosts a subform.
Tabs Make For Great UI – Until the memories run out
Just the other day I was working with a client’s app that was three tab levels deep: tabs within tabs, all of which were being loaded with subforms every time the form was opened. The app worked fine in Windows 7 but caused “System Resource Exceeded” error messages in Windows 10. The solution is to only load the subform when the user clicks on the tab and unload the subform when they click off to another tab.
Private Sub TabTasks_Change()
10 Static LastSubform As Access.SubForm
12 If Not LastSubform Is Nothing Then
14 If Len(LastSubform.SourceObject) Then
16 LastSubform.SourceObject = vbNullString
18 End If
20 End If
22 Select Case Me.TabTasks.Value
24 Case Me.Orders.PageIndex
25 If Me.frmOrders.SourceObject = vbNullString Then
26 Set LastSubform = Me.frmOrders
28 LastSubform.SourceObject = "frmOrder_sub"
30 End If
32 Case Me.Invoices.PageIndex
34 If Me.frmInvoices.SourceObject = vbNullString Then
36 Set LastSubform = Me.frmInvoices
38 LastSubform.SourceObject = "frmInvoices_sub"
40 End If
42 Case Me.Payments.PageIndex
44 If Me.frmPayments.SourceObject = vbNullString Then
46 Set LastSubform = Me.frmPayments
48 LastSubform.SourceObject = "frmPayments_sub"
50 End If
52 End Select
The magic happens on the tab’s Change event, which occurs when the user clicks on each tab of the control. In the code sample above I’m only interested in tabs that have subforms, in this case three tabs. (The technique is useless for tabs without subforms).
The static LastSubForm object, (line 10), keeps track of the last subform used and sets its SourceObject value to null in line 16. This will vacate the prior tab’s form from memory and keep memory use down to a minimum when the user switches off to another tab.
The select statement in line 22 is used to identify which tab was clicked on, then set the LastSubForm object to the subform contained in the tab and finally setting the SourceObject of the subform in line 28. Doing so will immediately load the subform into memory. The processed is repeated for the other two tabs of the control with subforms.
Keep In Mind
In order to minimize memory use you need to set the SourceObject property of each tab in question to an empty string in design view, otherwise all the subforms will load, which defeats the purpose of the technique.
The first tab of your control should load since it’s what your users will see when they open your form.
Just add more select statements as needed if you need to use this technique with more tabs, I’ve used it for a tab control that had 8 tabs.
If you have nested tabs you will need to repeat the approach for each tab control.
If your subform names are the same as your SourceObject property, you can further optimize the code by using a string variable in the code.
If you like this post share it!
Please share this post on LinkedIn, Twitter and Facebook, it will be much appreciated!
Join me on th enext Access with SQL Server meeting May 8th, at 6:30 PM CST, for more details please click here.