Variable Length SubReports

[Graham]

Access 2002

I have a report (unbound) with 2 subreports - more subreports later.

The subreports report Age Group counts, and their record source is a query
which in each case returns only one record which could comprise details for 0
- 10 Age Groups. The two subreports are for Adults and Children.

The following code hides unused controls and works fine.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim iCnt As Integer

For iCnt = 1 To 10
If IsNull(Me.Controls("GrpLabel" & iCnt)) _
Then
Me.Controls("GrpLabel" & iCnt).Visible = False
Me.Controls("SumOfGrpCountF" & iCnt).Visible = False
Me.Controls("SumOfGrpCountM" & iCnt).Visible = False
End If
Next iCnt
End Sub

In the detail section of each subreport CanShrink = Yes and in the report
each subreport CanShrink = Yes.

Is it possible to have the report only use the space that is necessary for
the Age Groups that are populated?

 

[Rick Brandt]

Access 2002

I have a report (unbound) with 2 subreports - more subreports later.

The subreports report Age Group counts, and their record source is a
query which in each case returns only one record which could comprise
details for 0 - 10 Age Groups. The two subreports are for Adults and
Children.

The following code hides unused controls and works fine.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim iCnt As Integer

For iCnt = 1 To 10
If IsNull(Me.Controls("GrpLabel" & iCnt)) _
Then
Me.Controls("GrpLabel" & iCnt).Visible = False
Me.Controls("SumOfGrpCountF" & iCnt).Visible = False
Me.Controls("SumOfGrpCountM" & iCnt).Visible = False
End If
Next iCnt
End Sub

In the detail section of each subreport CanShrink = Yes and in the
report each subreport CanShrink = Yes.

Is it possible to have the report only use the space that is
necessary for the Age Groups that are populated?

Instead of hiding the controls Cancel the format event by setting Cancel = true.
Then the section is not even constructed and no vertical space is consumed.

 

[Graham]

Hi Rick

Just tried that - what it achieves is to zap the whole subreport if there
are less than 10 values to display.

Whereas, what if would like to achieve is to tailor the length of the
subreport to the values to be displayed.

By setting Visible = false, I achieve the non-display objective but still
left with white space.

 

[Graham]

Hi Rick

Forgot to mention that once a GrpLabel control = null, then the remainder of
the GrpLabel controls will also be null.