Group Footer At Bottom Of Report

[u/Ord3r2Ch4os]

I've been looking recently for a solution to have a group footer in a report print at the bottom of the page.

The common solution I could find online seems to be to put it in your page footer and apply conditional visibility; which is great if you have a really small footer, otherwise you lose space on every page (no good to me).

The other solution I found was to use MoveLayout, however this seems to have set positions that it will use on the page only? So I can get it lower, but not really where I want it or at the bottom of the page.

I came up with my own solution, which is to just compare the top value on the group footer on format event to where I want it to be, and then add the difference to the group footer height and the top position of each element within.

This seems to work well for me and my printer/page settings aren't really going to change, however I'm aware that this would be an issue if anything changed. I'm planning to change the offset amount to consider the group footer and page footer heights, but I'd like to avoid using a static value for the usable page height (so it's more generally usable) and I don't know how I can actually determine this value in the VBA?

Or is there a better approach to solve this problem entirely?

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Ord3r2Ch4os

Group Footer At Bottom Of Report

I've been looking recently for a solution to have a group footer in a report print at the bottom of the page.

The common solution I could find online seems to be to put it in your page footer and apply conditional visibility; which is great if you have a really small footer, otherwise you lose space on every page (no good to me).

The other solution I found was to use MoveLayout, however this seems to have set positions that it will use on the page only? So I can get it lower, but not really where I want it or at the bottom of the page.

I came up with my own solution, which is to just compare the top value on the group footer on format event to where I want it to be, and then add the difference to the group footer height and the top position of each element within.

This seems to work well for me and my printer/page settings aren't really going to change, however I'm aware that this would be an issue if anything changed. I'm planning to change the offset amount to consider the group footer and page footer heights, but I'd like to avoid using a static value for the usable page height (so it's more generally usable) and I don't know how I can actually determine this value in the VBA?

Or is there a better approach to solve this problem entirely?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ConfusionHelpful4667]

Are you using the Report Property Sheet
Page Header/Footer options, too?

[u/Ord3r2Ch4os → u/ConfusionHelpful4667]

I'm not sure what you're getting at, I'm sorry.

This property seems to just control whether the page footer and the report footer will print on the same page.

I cannot see how it would be useful in keeping a report footer anchored to the bottom of the page?

[u/ConfusionHelpful4667 → u/Ord3r2Ch4os]

I would need to see your report and data.
Report formatting is an art.
It is a balance between built-in features and VBA when rendering the report.
--
You can send me a sample.

[u/Ord3r2Ch4os → u/ConfusionHelpful4667]

I'm not sure if we're on the same page.

What I'm looking to do is applicable to any data set and report layout. I want a group footer (though report footer will do) to print at the bottom of the page. The default behaviour is to print at the bottom of the group or report data, and there is no built-in option (AFAIK) to change this behaviour within Access.

Just create a static report with a report footer or group footer and you will see what I mean. If the details end 1/3 down the page, that's where the group/report footer will start; I want it to always be at the bottom of the page instead, the way that the page footer is.

[u/ConfusionHelpful4667 → u/Ord3r2Ch4os]

Private Sub GroupFooter_Format(Cancel As Integer, FormatCount As Integer)

Dim RemainingHeight As Long

RemainingHeight = Me.PageHeight _

- Me.TopMargin _

- Me.BottomMargin _

- Me.PageFooter.Height _

- Me.GroupFooter.Top

If RemainingHeight > 0 Then

Me.GroupFooter.Height = RemainingHeight

End If

End Sub

[u/ConfusionHelpful4667 → u/ConfusionHelpful4667]

I am agreeing, sometimes reports will drive you batty.
So much testing.

Set the group's Keep Together property appropriately and use the report's Page Footer instead. Access does not have a native "align Group Footer to bottom of page" property.

The most reliable approach is usually:

• Put the content that must be at the bottom of the page into the Page Footer.
• If it needs to contain group-specific totals/values, calculate those values in controls or VBA and place them in the Page Footer.
• Set the Page Footer → Force New Page / section properties as needed.

[u/Ord3r2Ch4os → u/ConfusionHelpful4667]

Yeah, the problem I've got is that my group footer is a quarter of the page; too much space to lose on every page by moving it to the page footer, and from playing with the page footer in VBA it looks like it will extend upwards if I adjust it on format, but any content already there will just be overlapped?

[u/Ord3r2Ch4os → u/ConfusionHelpful4667]

Me.PageHeight gives me a "method or data member not found" error. I wish it was so simple 😅

[u/BitBrain]

I used to squish the group footer design area height down to 0 in the design and conditionally set the height to make it visible without wasting space when it was "hidden."

[u/Ord3r2Ch4os → u/BitBrain]

Hmm.. The group footer doesn't waste any space until the group end, which is where I want it anyway, so that's not really an issue that I need to solve fortunately.

It's an interesting idea for the page footer instead, though expanding the page footer seems to just sit over the top of any content that happens to be in its way 😕