Stacie2410 wrote:
>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.
--
Marsh
MVP [MS Access]
|
Similar Threads
