Sub-reports that size and resize dynamically

[ShovelinFishHeads]

I am attempting to use several sub-forms to display the record sets returned by a group of queries. This is just for purposes of displaying entered data.

Naturally, it would be really nice to make the sub-forms so that they size vertically automatically, tall or short, depending on the number of records to be displayed.

After trying to use "can grow" and "can shrink" to accomplish this, I am at a total loss to get this to work.

What am I missing? We can't make sub-forms behave this way in 2016? Seems kinda basic to me.

Any help with this is greatly appreciated.

Thanks!

 

[sneuberg]

I think you have to do this yourself in code. Here's a subroutine that adjusts the height base on the number of records and the maximum height of the controls. You can find this in the attached database where you can try it out. It is only called in the form load event. You will probably want to put it in the after update and delete events too.

Sub ResizeForm()

Dim MaxHeight As Long
Dim ctrl As Control
Dim RecordCount As Long
Dim rst As Object
Set rst = Me.TestData_Form.Form.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
RecordCount = rst.RecordCount

For Each ctrl In Me.TestData_Form.Form
    If MaxHeight < ctrl.Height + ctrl.Top Then
        MaxHeight = ctrl.Height + ctrl.Top
    End If
Next ctrl
Me.TestData_Form.Height = MaxHeight * (RecordCount + 2)

End Sub

You could make this faster by just putting in some fixed value for the MaxHeight rather the have it goes through all of the controls to establish it.

 

[ShovelinFishHeads]

thanks sneuberg for contributing to this thread. I'll check out the code you posted and the db and see how it goes.

I am just curious as to why there are can-grow and can-shrink form properties when they don't seem to do anything. Or, do they actually do something?

Also, might have a try at using reports to deal with this issue.

 

[sneuberg]

I am just curious as to why there are can-grow and can-shrink form properties when they don't seem to do anything. Or, do they actually do something?.

A subreport obviously does grow or it wouldn't display the data. Scrollbars on a subreport would be silly but with a form it doesn't seem to be so simple.
I don't know why but I guess I should. I'll see what I can find out. Probably some other forum member knows. Maybe they'll post an answer.

 

[CJ_London]

the cangrow/shrink properties is only applied when a form is printed.

So for example you have a continuous subform containing an address control sized to display 5 lines of address and both subform and address control cangrow/shrink properties set to true

the first address record has 3 lines, the next has 6 and another has one

On the screen, no difference, but print the form and you will see the first and last address controls have shrunk, the second grown and the subform has also adjusted its height to show all records.

 

[sneuberg]

Also, might have a try at using reports to deal with this issue.

If the user doesn't need to edit the data that would seem to be the way to go.

 

[ShovelinFishHeads]

thanks, CJ London

I appreciate the input