Autocondition Subform size

[blueraincoat]

Hi All, is there any way to let subform autocondition its's size due to the entries of the records? I don't like the scroll bar very much and want to make the subform looks more neat.


Thanks in advanced.

[ajetrumpet]

have you set the autosize property of the form object? I believe that will do, but not sure 100%

[blueraincoat]

Unfortunately, it did not work, I left that property as default that is YES.
I think the subform size is decided when you put it in the main form and set the size,
if I want to set the size of subform due to the details, I must add some code...
I search it on Internet and got a page that maybe the solution(http://www.databasejournal.com/featu...s-Subforms.htm), but it's a little bit complicated for me.
I would carry on with my project keep it for further study...

[pkstormy]

I usually just put this code: docmd.movesize ,200,,10500 in the Onload event of the form. I'll play with the 10500 value until I get it sized like I want it to.

You peaked my curiosity though on writing code to size according to the # of records. If I can figure out how, I'll post it in the code repository.

[blueraincoat]

I think it maybe very great that a subform could autocondition its size according to the number of the records and the detail, it may look much more professional.

[pkstormy]

You could try something like this in the OnLoad event of the form:

Private Sub Form_Load()
'For a continous popup form
Dim DetailH As Variant 'Form's current detail height
Dim NumRecs As Variant 'Does a dlookup to get total record count
Dim NewHeight As Variant 'NewHeight for docmd.movesize code

DetailH = Me.Section(acDetail).Height 'current height of detail section. add Me.Section(acHeader).Height to get height for header section if used.
NumRecs = DLookup("[CC]", "LookupCount") + 2 'Create a query called: LookupCount with an Expression (CC) that is summed (based on the form's recordsource). Add 2 so the <new record> shows on the form.
NewHeight = (DetailH * NumRecs) 'ie. detail section height * number of records
Me.ScrollBars = 0 'Don't show scroll

If NewHeight > 11000 Then 'or whatever you think is the maximum height for the form on the screen
NewHeight = 11000 'Set maximum form height
Me.ScrollBars = 2 'Show Vertical scroll because more records than NewHeight
End If

DoCmd.MoveSize , 200, , NewHeight 'Note: 200 is used as the top starting point

End Sub


You'll want to play with the values such as 200 or adding +2 to the NumRecs or the maximum 11000 for NewHeight.

[pkstormy]

Note: The above code is for a POPUP Continous form. Not a subform embedded into a main form (this would be a no-no).

[blueraincoat]

Hey pkstormy, thank you for your code. I'm a rookie,and i need some help with your code.
NumRecs = DLookup("[CC]", "LookupCount") + 2 'Create a query called: LookupCount with an Expression (CC) that is summed (based on the form's recordsource).
Do you mean I should creat a sum query call LookupCount, and there should be a CC expression which is summed?
I did it but it gave an error
I tried to use Dcount instead of DLookup and failed to get it too.
Can you give further suggestion?

[blueraincoat]

just see your post after my post
lol
Thanks anyway, i'll try it in a popup form. And still it's very fanatic, it should be work and can autocondition the form's size.

[pkstormy]

Hey pkstormy, thank you for your code. I'm a rookie,and i need some help with your code.
NumRecs = DLookup("[CC]", "LookupCount") + 2 'Create a query called: LookupCount with an Expression (CC) that is summed (based on the form's recordsource).
Do you mean I should creat a sum query call LookupCount, and there should be a CC expression which is summed?
I did it but it gave an error
I tried to use Dcount instead of DLookup and failed to get it too.
Can you give further suggestion?

Yes. You would create a query with an expression - CC: sum(1) and call it LookupCount.

[blueraincoat]

hey pikstormy
do you have any new idea about making a subform which is embeded in a main form autosize itself ?