SubForms Makes navig. on main form slow (1 Viewer)

[Falcon88]

hii all
i have a main form with a tab , 9 sub forms
i notice navigation between records become slow , then i search for a solution , i find this code :

Private Sub ps_Change()

  Select Case ps.Pages.Item(ps.Value).Name
Case "pg1"
      ' Where sfrm1 is the name of the subform on page 1
      If Len(SubOrdersFrm.SourceObject) = 0 Then
        SubOrdersFrm.SourceObject = "SubOrdersFrm"
       ' Me.LinkMasterFields = "OrderID;InsurCmpName"
        SubOrdersFrm.LinkChildFields = "OrderNo;CmpIDDet"
      End If

    Case "pg2"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm1.SourceObject) = 0 Then
        SubOrdersFrm1.SourceObject = "SubOrdersFrm1"
        SubOrdersFrm1.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg3"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm2.SourceObject) = 0 Then
        SubOrdersFrm2.SourceObject = "SubOrdersFrm2"
        SubOrdersFrm2.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg4"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm3.SourceObject) = 0 Then
        SubOrdersFrm3.SourceObject = "SubOrdersFrm3"
       SubOrdersFrm3.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg5"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm4.SourceObject) = 0 Then
        SubOrdersFrm4.SourceObject = "SubOrdersFrm4"
        SubOrdersFrm4.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg6"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm5.SourceObject) = 0 Then
        SubOrdersFrm5.SourceObject = "SubOrdersFrm5"
        SubOrdersFrm5.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg7"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm6.SourceObject) = 0 Then
        SubOrdersFrm6.SourceObject = "SubOrdersFrm6"
        SubOrdersFrm6.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg8"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm7.SourceObject) = 0 Then
        SubOrdersFrm7.SourceObject = "SubOrdersFrm7"
        SubOrdersFrm7.LinkChildFields = "OrderNo;CmpIDDet"
      End If
Case "pg9"
      ' Where sfrm2 is the name of the subform on page 2
       If Len(SubOrdersFrm8.SourceObject) = 0 Then
        SubOrdersFrm8.SourceObject = "SubOrdersFrm8"
        SubOrdersFrm8.LinkChildFields = "OrderNo;CmpIDDet"
      End If
    ' Repeat for each tab page
    
  End Select
End Sub

it works good before click on that tab , but when i change tab pages it return slow .

 

[MarkK]

If the subforms are linked, every current event on the parent form causes every subform to requery its data.
Two things:
1) Use a single subform control, and on tab change, programmatically load the subform that should be visible for that tab. That will be way faster.
2) Think about designing your user interface not to show ALL the data, but to provide the user a tool to perform a single task. Remove everything from the the form that the user doesn't need to perform that task. Then make one form for each task the user needs to perform. So design your user interface around what the user needs to do, not around ALL the data.
hth

 

[arnelgp]

maybe add Index to your Linked Child Fields.

 

[Falcon88]

Coul

If the subforms are linked, every current event on the parent form causes every subform to requery its data.
Two things:
1) Use a single subform control, and on tab change, programmatically load the subform that should be visible for that tab. That will be way faster.
2) Think about designing your user interface not to show ALL the data, but to provide the user a tool to perform a single task. Remove everything from the the form that the user doesn't need to perform that task. Then make one form for each task the user needs to perform. So design your user interface around what the user needs to do, not around ALL the data.
hth

Could you explain step 1 or change my code to meet my request

 

[MarkK]

Your code still eventually loads all the subforms into different subform controls. Just use one subform control. Take it off the tab control. Use the tab_change event to set the SourceObject property of your single subform control.
Sample code...

Case "pg1"
        SubOrdersFrm.SourceObject = "SubOrdersFrm"
Case "pg2"
        SubOrdersFrm.SourceObject = "SubOrdersFrm1"
Case "pg3"
        SubOrdersFrm.SourceObject = "SubOrdersFrm2"

See how in my code the subform control, 'SubOrdersForm' is the same one. This unloads the currently visible subform, and replaces it, so you only ever have one subform running at a time, which makes sense since you only have one visible at a time anyway. This will make your form much more responsive.

 

[Falcon88]

Your code still eventually loads all the subforms into different subform controls. Just use one subform control. Take it off the tab control. Use the tab_change event to set the SourceObject property of your single subform control.
Sample code...

Case "pg1"
        SubOrdersFrm.SourceObject = "SubOrdersFrm"
Case "pg2"
        SubOrdersFrm.SourceObject = "SubOrdersFrm1"
Case "pg3"
        SubOrdersFrm.SourceObject = "SubOrdersFrm2"

See how in my code the subform control, 'SubOrdersForm' is the same one. This unloads the currently visible subform, and replaces it, so you only ever have one subform running at a time, which makes sense since you only have one visible at a time anyway. This will make your form much more responsive.

i could not give the same control subform for more than one subform on that same Mainform ....

could you gives me some example ?

 

[MarkK]

Post your database. That'll be simpler. Or create a database that demonstrates the problem, and post that. I'll edit it, post it back, and you can take a look at what I've done.