How to make Microsoft Access subform adjust it's height and bring footer info closer to last record?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
How to make Microsoft Access subform adjust it's height and bring footer info closer to last record?

Please note the following pics - that shows a timesheet where one sample image shows only two records and another shows multiple records where you have to scroll vertically.
I want to adjust the height of the subform so that the footer info always shows directly below the last record:
Now I can manually do this by grabbing the height of the subform if there is only one record, two record, etc... but that would be a lot of work.  And look at the total number of records then adjust the subform accordingly but is there not an easier way?

Thank you in advance for pointing me in the right direction!
ee-sample1.PNG
ee-sample2.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
From what I understand, you have a main form (non continuous) holding a data grid subform.

I suggest you get rid of the subform, make the main form continuous, put the column's name in the form's header, and the buttons in the form's footer.

Author

Commented:
No I need the main form holding the subform.  That solution will not work for me.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I use a technique that checks to count the number of records in the subform, and then adjust the height of the subform accordingly.  I generally do this immediately after I either filter or load the subform, so it would be in code on the main form.

Dim intRows as int
intRows = me.subformControlName.Form.Recordsetclone.Recordcount
if intRows >= 10 then
    me.subformControlname.height = 4 * 1440   '<= Adjust 3 as appropriate for your subform
Else
    'In the following, the .75 value represents the height of the header in the subform (inches)
    'the .5 value represents the height of the footer in the subform
    'the .20 value represents the height of the detail section.
    me.subformcontrolname.height = (.75 + .5 + intRows * .20) * 1440
endif

Open in new window


HTH
Dale
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark EdwardsChief Technology Officer
Commented:
Hey Steph:   Since you can't redesign your app, let's talk about adding code to your process that can get the number of records in the subform and change the height of the subform control to bring the subform's footer closer to the last record.  Not an easy thing to do.

It involves getting the height of the fixed sections of your subform (header and footer sections) in pixels (or whatever the unit of measure is - it ain't inches...), getting the number of records, and calculating the height of the records based on their design row height * recordcount.  Once you have all that, you'll need to reset your subform control's Height property.  I've tried to do it before, and it's not perfect, but it will get you in the ball park.

Your detail section is Section(0).  Your Header and Footer sections are sections 1 & 2 (not sure which is what number).  Get the height of them like so:
HeaderHeight = section(1).height
FooterHeight = section (2).height
Get the recordcount of the subform however you want to get it.
Calculate the records section height by multiplying the number of records by the height of the design row height in the detail section.  If the height is .3 inches, then (.3*1440) will convert to pixels (or twips, whatever) and then multiply by recordcount.
CalculatedHeight = HeaderHeight + FooterHeight + RecordsHeight
Now add them all together and reset your subform control height to that value (subform.height = CalculatedHeight)

You'll have to tweak it in a few areas to your satisfaction, but you can get there.
Mark EdwardsChief Technology Officer

Commented:
Looks like Dale and I have the same experience.....  NICE!

Author

Commented:
Thank you sincerely for the helpful replies.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial