Automatically resize subform height based on number of returned re

[Stacie2410]

I have a main form that has a subform on it. The subform is displayed in
Continuous Forms view. In the footer of the subform, I have a field that
gives a sum of one of the columns on the form, for the records returned. It's
set up to kinda look like a table, with the bottom line being the total of
column C. This all works just fine. However, my problem is that sometimes
there may only be one record returned, and sometimes there could be 10
records. The subform size doesn't change if there's 1 record or if there's
10 records...the footer stays in the same place, but say there's only 1
record returned, there will be a large gap of space between the 1 record, and
my "total line".

Is there a way to automatically resize the entire subform depending on how
many records are returned, so that the footer (which has my totals line), is
always right underneath the records which are returned?

Another problem with this, is that there's more data on the form that's
further down after the subform...If there are many records, you don't notice
the gap, but if there aren't many records on the subform, the next section
looks like it's way down on the form, with a large blank area in between it
and the subform above it.

Is there a way to change this?

 

[Marshall Barton]

I have a main form that has a subform on it. The subform is displayed in
Continuous Forms view. In the footer of the subform, I have a field that
gives a sum of one of the columns on the form, for the records returned. It's
set up to kinda look like a table, with the bottom line being the total of
column C. This all works just fine. However, my problem is that sometimes
there may only be one record returned, and sometimes there could be 10
records. The subform size doesn't change if there's 1 record or if there's
10 records...the footer stays in the same place, but say there's only 1
record returned, there will be a large gap of space between the 1 record, and
my "total line".

Is there a way to automatically resize the entire subform depending on how
many records are returned, so that the footer (which has my totals line), is
always right underneath the records which are returned?

Another problem with this, is that there's more data on the form that's
further down after the subform...If there are many records, you don't notice
the gap, but if there aren't many records on the subform, the next section
looks like it's way down on the form, with a large blank area in between it
and the subform above it.

In the subform's Load event (or later or in the main form),
you can use code along these lines:

Const MaxRecs As Integer = 10
Dim NumRecs As Integer
With Me 'code in subform
'With Me.subformcontrol.Form 'code in main form
.RecordsetClone.MoveLast
NumRecs = .RecordsetClone.RecordCount
If NumRecs > MaxRecs Then NumRecs = MaxRecs
.InsideHeight = .Section(1).Height + .Section(2).Height _
+ NumRecs * .Section(0).Height
End With

Make sure the main form section with the subform is tall
enough for the MaxRecs size of the subform or will get an
error.