I have a main form ExpenseType with a subform Expense. How can I automatically expand the height of the subform based on how much date is in it
Right now, I have 4 records in my subform but only 3 are showing.
I've created my subform with dialog border style and neither scroll bars are showing.
Any idea please?
The border style only allows resizing for the top-most form, not the subform.
Try setting the 'Can Grow' flags for both the Details section on the main form and the subform CONTROL on the main form to TRUE.
Scroll bars have their own setting; for the subform's scroll bars to show, you'll need to set the ScrollBars setting in the subform's form properties to either 'Vertical' (for vertical only) or 'Both'.
this assumes your subform has a header (section1), a footer (section2) and is a continuous form (section0 is the detail section). Adjust the calculation accordingly
You will also need code to ensure the subform does not oversize with too many records and other issues around no records.
If your subform is a datasheet, it becomes much more complicated - sections do not apply and the datasheet row height is dependent on font and fontsize - easiest is trial and error - start with around 1/2cm (288 twips) and adjust as required
Having controls change size can be disconcerting for the user so the alternative is to use the main form resize event to resize based on the size of the main form
Here is an overview that might work and might not. The "might not" is because the trick is to know how much to expand the sub-form.
To expand the sub-form you must first expand the form or otherwise assure that there are no "blockers" beneath the sub-form on the main form. Because you cannot expand the sub unless there is room for it on the main. Then you must expand the sub-form control because it merely contains the actual sub-form. Then you can expand the sub-form.
To expand the main form, you must change its "Height" property by the desired amount. If the desired amount is X then it could be as simple as
Code:
Me.Height = Me.Height + X
OK, if the sub-form control was called ctlSubF, you next would use
Code:
Me.ctlSubF.Height = Me.ctlSubF.Height + X
Then you must expand the sub-form.
Code:
Me.ctlSubF.Form.Height = Me.ctlSubF.Form.Height + X
Or something similar to that. On older versions of Access, you might need Me! rather than Me. as the lead-off for that reference.
Now, the comment about knowing how much to expand it? You could just decide to make your sub-form have fixed sizes for the things you would display. Like, regardless of the font, always make the controls 0.25 inches or 360 twips. Then you could choose to say "make everything 0.25 inches taller" - doing it in the order of form, sub-form control, form contained in the sub-form.
If you were trying to squeeze it to make it fit, that becomes a problem because Access does not have a good algorithm handy to compute the actual height/width of a string in a given font face/size/weight combination. You can do it for printed reports but not for displayed stuff. Go figure.
There are other issues. Dynamically resizing can be done but not indefinitely. At some point you would force scroll bars to kick in on the form itself (or worse, you will WISH that they had kicked in) because the expanded form might overflow screen space. And with modern "desktop managers" there is no telling how much space you actually have to play with.
I would take the approach of making the form, control, and sub-form as big as you can and just live with it. Particularly if you ever want to restore that form/sub-form combination back to a smaller size later.
One last note: You can do what I said in the proper order because you are lucky that you talked about the bottom of the form. As long as you leave the .Top property alone, you can expand controls by diddling with their .Height property. The "anchor" to a control or form position is its .Top property. But if there were any controls further away from the top (i.e. below the sub-form control), you would have to worry about moving them out of the way by a similar amount as you were using to expand the sub-form.
