loading subforms in tab control

J

johnlute

Access 2003/Win 2000.

I have lots of forms that use multiple subforms so I've placed them on
tab controls. The subforms are rather complex so Access tends to crap
out when trying to run a report with a form open.

I decided to experiment with loading/unloading subforms via the tab
control change event. I've got this which works just fine:

Private Sub TabCtrlAttributes_Change()
Select Case Me.TabCtrlAttributes
Case 1
Me.sfrmProfilesCodes.SourceObject = "sfrmProfilesCodes"
Case 2
Me.sfrmProfilesApprovals.SourceObject =
"sfrmProfilesApprovals"
Case 3
Me.sfrmProfilesAllergens.SourceObject =
"sfrmProfilesAllergens"
Case 4
Me.sfrmPKCGPhysicalAttributes.SourceObject =
"sfrmPKCGPhysicalAttributes"
Case 5
Me.sfrmPKCGMaterialAttributes.SourceObject =
"sfrmPKCGMaterialAttributes"
Case 6
Me.sfrmPKCGFinishingAttributes.SourceObject =
"sfrmPKCGFinishingAttributes"
Case 7
Me.sfrmPKCGPerformanceAttributes.SourceObject =
"sfrmPKCGPerformanceAttributes"
Case 8
Me.sfrmPKAdditionalAttributes.SourceObject =
"sfrmPKAdditionalAttributes"
Case 9
Me.sfrmPKProfilesQualifications.SourceObject =
"sfrmPKProfilesQualifications"
Case 10
Me.sfrmProfilesShipping.SourceObject =
"sfrmProfilesShipping"
Me.sfrmProfilesStorage.SourceObject =
"sfrmProfilesStorage"
Case 11
Me.sfrmProfilesLocationIDs.SourceObject =
"sfrmProfilesLocationIDs"
Me.sfrmPKProfilesSuppliers.SourceObject =
"sfrmPKProfilesSuppliers"
Case 12
Me.sfrmPKProfilesAssociations.SourceObject =
"sfrmPKProfilesAssociations"
Case 13
Me.sfrmProfilesAttachments.SourceObject =
"sfrmProfilesAttachments"
End Select

End Sub

The problem is that the subforms don't appear to unload when changing
to another tab. For example, when I first open the main form and do
nothing except run a report - the report runs fine. When I click on
all of the tabs and then run a report - the report craps out. When I
first click on a tab there's a slight delay before its subform opens.
When I click away from it and then back to it then there's no delay in
the subform loading. This coupled with the fact that the report craps
out is telling me that the subforms aren't unloading entirely.

How can I get the subforms to unload? Or is this not the problem?

Thanks for your help!
 
M

Marshall Barton

johnlute said:
Access 2003/Win 2000.

I have lots of forms that use multiple subforms so I've placed them on
tab controls. The subforms are rather complex so Access tends to crap
out when trying to run a report with a form open.

I decided to experiment with loading/unloading subforms via the tab
control change event. I've got this which works just fine:

Private Sub TabCtrlAttributes_Change()
Select Case Me.TabCtrlAttributes
Case 1
Me.sfrmProfilesCodes.SourceObject = "sfrmProfilesCodes"
Case 2
Me.sfrmProfilesApprovals.SourceObject =
"sfrmProfilesApprovals"
Case 3
Me.sfrmProfilesAllergens.SourceObject =
"sfrmProfilesAllergens"
Case 4
Me.sfrmPKCGPhysicalAttributes.SourceObject =
"sfrmPKCGPhysicalAttributes"
Case 5
Me.sfrmPKCGMaterialAttributes.SourceObject =
"sfrmPKCGMaterialAttributes"
Case 6
Me.sfrmPKCGFinishingAttributes.SourceObject =
"sfrmPKCGFinishingAttributes"
Case 7
Me.sfrmPKCGPerformanceAttributes.SourceObject =
"sfrmPKCGPerformanceAttributes"
Case 8
Me.sfrmPKAdditionalAttributes.SourceObject =
"sfrmPKAdditionalAttributes"
Case 9
Me.sfrmPKProfilesQualifications.SourceObject =
"sfrmPKProfilesQualifications"
Case 10
Me.sfrmProfilesShipping.SourceObject =
"sfrmProfilesShipping"
Me.sfrmProfilesStorage.SourceObject =
"sfrmProfilesStorage"
Case 11
Me.sfrmProfilesLocationIDs.SourceObject =
"sfrmProfilesLocationIDs"
Me.sfrmPKProfilesSuppliers.SourceObject =
"sfrmPKProfilesSuppliers"
Case 12
Me.sfrmPKProfilesAssociations.SourceObject =
"sfrmPKProfilesAssociations"
Case 13
Me.sfrmProfilesAttachments.SourceObject =
"sfrmProfilesAttachments"
End Select

End Sub

The problem is that the subforms don't appear to unload when changing
to another tab. For example, when I first open the main form and do
nothing except run a report - the report runs fine. When I click on
all of the tabs and then run a report - the report craps out. When I
first click on a tab there's a slight delay before its subform opens.
When I click away from it and then back to it then there's no delay in
the subform loading. This coupled with the fact that the report craps
out is telling me that the subforms aren't unloading entirely.

How can I get the subforms to unload? Or is this not the problem?
Click to expand...


If all the subforms are the same size and position, then you
can remove them all from the tab control and put one subform
control on the main form on top of the tab control. Your
code would then set the one subform control's SourceObject
and whatever was there before will be "cleared".

If the subform controls are different sizes and/or at
different positions, then set the other subform's
SourceObject property to a ZLS (i.e. "") to clear them:

Assuming there are no other subforms on the main form:

Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acSubform Then ctl.SourceObject = ""
Next ctl
 
J

johnlute

Hi, Marshall!

Thanks for the very informative response! Sadly, I'm not following! I
get the part about using one subform control however I do have some
tabs with multiple subforms of varying sizes. As I read your response
this is the direction I need to take:
If the subform controls are different sizes and/or at
different positions, then set the other subform's
SourceObject property to a ZLS (i.e. "") to clear them
Click to expand...

So I need to keep all of the subform controls and set their
SourceObject properties to a ZLS?
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acSubform Then ctl.SourceObject = ""
Next ctl
Click to expand...

Assuming the answer to the above question is Yes then where do I put
this code? In the change event of the tab control?

Thanks!

johnlute said:
Access 2003/Win 2000.
Click to expand...
I have lots of forms that use multiple subforms so I've placed them on
tab controls. The subforms are rather complex so Access tends to crap
out when trying to run a report with a form open.
Click to expand...
I decided to experiment with loading/unloading subforms via the tab
control change event. I've got this which works just fine:
Click to expand...
Private Sub TabCtrlAttributes_Change()
   Select Case Me.TabCtrlAttributes
       Case 1
           Me.sfrmProfilesCodes.SourceObject = "sfrmProfilesCodes"
       Case 2
           Me.sfrmProfilesApprovals.SourceObject =
"sfrmProfilesApprovals"
       Case 3
           Me.sfrmProfilesAllergens.SourceObject =
"sfrmProfilesAllergens"
       Case 4
           Me.sfrmPKCGPhysicalAttributes.SourceObject =
"sfrmPKCGPhysicalAttributes"
       Case 5
           Me.sfrmPKCGMaterialAttributes.SourceObject =
"sfrmPKCGMaterialAttributes"
       Case 6
           Me.sfrmPKCGFinishingAttributes.SourceObject =
"sfrmPKCGFinishingAttributes"
       Case 7
           Me.sfrmPKCGPerformanceAttributes.SourceObject =
"sfrmPKCGPerformanceAttributes"
       Case 8
           Me.sfrmPKAdditionalAttributes.SourceObject =
"sfrmPKAdditionalAttributes"
       Case 9
           Me.sfrmPKProfilesQualifications.SourceObject =
"sfrmPKProfilesQualifications"
       Case 10
           Me.sfrmProfilesShipping.SourceObject =
"sfrmProfilesShipping"
           Me.sfrmProfilesStorage.SourceObject =
"sfrmProfilesStorage"
       Case 11
           Me.sfrmProfilesLocationIDs.SourceObject =
"sfrmProfilesLocationIDs"
           Me.sfrmPKProfilesSuppliers.SourceObject =
"sfrmPKProfilesSuppliers"
       Case 12
           Me.sfrmPKProfilesAssociations.SourceObject =
"sfrmPKProfilesAssociations"
       Case 13
           Me.sfrmProfilesAttachments.SourceObject =
"sfrmProfilesAttachments"
   End Select
Click to expand...
End Sub
Click to expand...
The problem is that the subforms don't appear to unload when changing
to another tab. For example, when I first open the main form and do
nothing except run a report - the report runs fine. When I click on
all of the tabs and then run a report - the report craps out. When I
first click on a tab there's a slight delay before its subform opens.
When I click away from it and then back to it then there's no delay in
the subform loading. This coupled with the fact that the report craps
out is telling me that the subforms aren't unloading entirely.
Click to expand...
How can I get the subforms to unload? Or is this not the problem?
Click to expand...

If all the subforms are the same size and position, then you
can remove them all from the tab control and put one subform
control on the main form on top of the tab control.  Your
code would then set the one subform control's SourceObject
and whatever was there before will be "cleared".

If the subform controls are different sizes and/or at
different positions, then set the other subform's
SourceObject property to a ZLS (i.e. "") to clear them:

Assuming there are no other subforms on the main form:

Dim ctl As Control
For Each ctl in Me.Controls
        If ctl.ControlType = acSubform Then ctl.SourceObject = ""
Next ctl

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...
 
M

Marshall Barton

Put it before the Select Case statement.

Note that this is a simple minded approach that, depending
on what else your form is doing, may need refinement. I
also have no idea if it will resolve the problem that led
you to ask your original question nor how acceptable the
performance will be.
--
Marsh
MVP [MS Access]


johnlute wrote:
[]
 
J

johnlute

Note that this is a simple minded approach...

Perfect! It's been explained to me throughout my life (and here on
this forum by a certain person) that I'm rather the simpleton!
...depending on what else your form is doing, may need refinement.  I
also have no idea if it will resolve the problem that led
you to ask your original question nor how acceptable the
performance will be.
Click to expand...

I simplified by eliminating the multiple subforms per tab by combining
them into a single subform (no, I don't have a normalization problem -
just something that happened over the course of development).

I experimented and came up with this:
Private Sub TabCtrlAttributes_Change()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Select Case Me.TabCtrlAttributes
Case 0
Me.sfrmCtl.Visible = False
Case 1
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesCodes"
Case 2
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesApprovals"
Case 3
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesAllergens"
Case 4
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGPhysicalAttributes"
Case 5
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGMaterialAttributes"
Case 6
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGFinishingAttributes"
Case 7
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGPerformanceAttributes"
Case 8
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKAdditionalAttributes"
Case 9
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesQualifications"
Case 10
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesShipStorage"
Case 11
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject =
"sfrmProfilesLocationIDsSupplierIDs"
Case 12
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesAssociations"
Case 13
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesAttachments"
End Select

End Sub

I ran it through the paces and the reports that crapped out are now
popping open. I won't scream, "Victory!" just yet. I'm sure I'm
overlooking something however it appears to be bug-free at the moment.

My most immediate question is regarding the Source Object of sfrmCtl.
Do I just leave this blank?

Thanks for your time and direction!!!
 
M

Marshall Barton

johnlute said:
I simplified by eliminating the multiple subforms per tab by combining
them into a single subform
Click to expand...

That makess the form much simpler and may be the key to the
original problem.
I experimented and came up with this:
Private Sub TabCtrlAttributes_Change()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Click to expand...

Don't use those archaic MenuItem things. They are only
there to maintain backward compatibility with Access 2. For
several versions now, the preferred way to save a record has
been:
If Me.Dirty Then Me.Dirty = False
or just
Me.Dirty = False
may do the same thing.
Select Case Me.TabCtrlAttributes
Case 0
Me.sfrmCtl.Visible = False
Case 1
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesCodes"
Case 2
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesApprovals"
Case 3
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesAllergens"
Case 4
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGPhysicalAttributes"
Case 5
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGMaterialAttributes"
Case 6
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGFinishingAttributes"
Case 7
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKCGPerformanceAttributes"
Case 8
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKAdditionalAttributes"
Case 9
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesQualifications"
Case 10
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesShipStorage"
Case 11
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject =
"sfrmProfilesLocationIDsSupplierIDs"
Case 12
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesAssociations"
Case 13
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesAttachments"
End Select

End Sub

I ran it through the paces and the reports that crapped out are now
popping open. I won't scream, "Victory!" just yet. I'm sure I'm
overlooking something however it appears to be bug-free at the moment.
Click to expand...

Looks OK to me, although I think I would move the
Me.sfrmCtl.Visible = True
up before the Select Case so I wouldn't have to repeat in
all (except the first case) the cases. If that makes the
screen flicker, then leave the Visible lines where they are.
My most immediate question is regarding the Source Object of sfrmCtl.
Do I just leave this blank?
Click to expand...

Yes, as long as you don't want the subform to be loaded when
the main form is opened.
 
J

johnlute

Hi, Marsh!
Don't use those archaic MenuItem things.  They are only
there to maintain backward compatibility with Access 2.  For
several versions now, the preferred way to save a record has
been:
        If Me.Dirty Then Me.Dirty = False
or just
        Me.Dirty = False
may do the same thing.
Click to expand...

I actually do use the "new" language but must've slipped into a time
warp momentarily.
Looks OK to me, although I think I would move the
        Me.sfrmCtl.Visible = True
up before the Select Case so I wouldn't have to repeat in
all (except the first case) the cases.  If that makes the
screen flicker, then leave the Visible lines where they are.
Click to expand...

I tried that. No screen flicker but the control was blank. I found
that I need to leave it in. It's now grown to the following final
code. Wow. I wish I would've explored this years ago. It's an enormous
improvement. I'm actually able to eliminate 20 forms.

Now if I could also drop 20 pounds...

Thanks for all of your help!!!

Happy Holidays!
 
Top