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

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
LVL 1
stephenlecomptejrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
stephenlecomptejrAuthor Commented:
No I need the main form holding the subform.  That solution will not work for me.
Dale FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark EdwardsChief Technology OfficerCommented:
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 OfficerCommented:
Looks like Dale and I have the same experience.....  NICE!
stephenlecomptejrAuthor Commented:
Thank you sincerely for the helpful replies.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.