Prevent Sub-forms and Graphs to run automatically when I open a Form

[Cabrita]

Hello,

I have a form each is composed of different Graphs and sub-forms and whenever I open the form I have to wait for the Graphs and sub-form to run. I have in the form a bottom to make those graphs and sub-form to requery. So, I'm looking for a way to make those graphs and sub-forms to run only when I press the botton and not everytime I open the form.

Thank you very much for your help.

Cabrita

 

[krsherm]

Presumedly, each of these subforms is on a tab control (if not, you can modify to suit your needs). I have a similar situation wherein I have about a dozen pages on a tab control, and each page has a subform and/or graph. Since the typical user may not view any of the tabs (she may only want to see the data on the default tab page), there is no point in loading the data into ALL of the subforms... that would be a real waste of time and resources.

As such, get your form designed however you like, with all of your subforms. Once everything is working properly, select each subform control and delete its Source Object property (on the tabbed form). This will cause it to "disappear" though the control that holds it will still be there (it will be all white). Then use code similar to the below in the ON_Change event of the tab control. (My tab is named tabMainData).

The code checks to see if the recordsource for the selected subform is "" (that is, it hasn't been set yet). If it is, then it sets it to what you want it to be. This means that there is only network load when the end user WANTS to view the data on the specific subform. I have had an amazing increase in performance since I started using this technique.

Please note that I use application.echo = false to keep the screen from flickering while the subform is loading, and docmd.hourglass true so that the end user knows that something is happening for the half a second it takes for the subform to load.

The numbers in the Select Case statement (eg Case 1) relate to the Page Index value, which can be accessed from the property sheet of the individual tabs.

It shouldn't be too hard to adapt this from a tab scenario to a button-driven scenario. If you need help, let me know.

Rock ON!

Kevin

Private Sub tabMainData_Change()

Application.Echo False
DoCmd.Hourglass True
Select Case Me.tabMainData.Value
Case 1
If Me.ActivityLog.SourceObject = "" Then
Me.ActivityLog.SourceObject = "frmActivitySub"
End If
Case 2 'Payments
If Me.Payments.SourceObject = "" Then
Me.Payments.SourceObject = "frmPaymentsSubList"
End If
Case 3
If Me.frmSubCustomerInvoices.SourceObject = "" Then
Me.frmSubCustomerInvoices.SourceObject = "frmSubCustomerInvoices"
End If

Case 4
If Me.frmSubEquipmentInvoices.SourceObject = "" Then
Me.frmSubEquipmentInvoices.SourceObject = "frmSubEquipmentInvoices"
End If
Case 7
If Me.frmSubEquipment.SourceObject = "" Then
Me.frmSubEquipment.SourceObject = "frmSubEquipment"
End If
Case 9
If Me.frmSubBalanceFundor.SourceObject = "" Then
Me.frmSubBalanceFundor.SourceObject = "frmSubBalanceFundor"
End If
Case 10
If Me.frmSubBalanceCustomer.SourceObject = "" Then
Me.frmSubBalanceCustomer.SourceObject = "frmSubBalanceCustomer"
End If
Case 11
If Me.frmSubLateFees.SourceObject = "" Then
Me.frmSubLateFees.SourceObject = "frmSubLateFees"
UpdateLateFeeAmounts
End If
End Select
DoCmd.Hourglass False
Application.Echo True



End Sub

 

[Cabrita]

Hi Krsherm,

Thanks for you help, I'll try to apply you solution today and If I'll tell you about it tomorrow.

Cabrita