Avatar of fostermom45
fostermom45 asked on

Limiting Access Report Detail Sections to a Specific Number per Page

I need to limit my details on each page of my report with out losing my Group footer.  When I used the suggested solution that was out there.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If txtrowcount Mod 14 = 0 Then
        Detail.ForceNewPage = 2   ' after section
    Else
        Detail.ForceNewPage = 0   ' none
    End If
End Sub

Using the running sum property and the textbox =1.  It worked for the details but I lost my sum that was in the Group footer.  I then tried putting my Sum in the page footer (the page footer still shows up) but the Sum in the page footer does not work.    Do you have any suggestions?
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
peter57r

Why is your group footer not appearing any more?
Have you deleted the grouping?
Jeffrey Coachman

fostermom45,

If you set the report to print 14 records per page and there are more than 14 records in the group (let's say 17), then the Group footer totals will be on the next page.
Perhaps previously(before the 14 record code was added) you only had a max or 17 records in a group, the group footer would be on the bottom of each page.
If you want to limit the records to 14, then any more than 14 records in the group will force the group footer to the next page.
(Is this what you mean by: "I lost my sum that was in the Group footer."?)

So when you say: "the Sum in the page footer does not work."
Please explain exactly what you mean:
-The Group footer totals do not show up on *every* page anymore, like they used to?
Or
-The Label that says "Sum" appears, but the total does not?
Or
-The group Footer seems to have disappeared altogether?

Is the group footer section still visible in design view?
If yes, then make sure you did not, accidentally, set the Visible property of this section to NO.

If the group footer section is NOT still visible in design view then you may have deleted it.
In that case you will have to use the Sorting/Grouping dialog box to add it back in.

JeffCoachman
ASKER
fostermom45

Sorry, I didn't explain myself well.  I am printing on preprinted form and the totals have to be in a specified place.  I could maybe live with the total of each page not being in the total box at the bottom of each page, but if the next page only has 2 items then the total is printed right under that last item, and this is not correct for the pre-printed form.   My ideal solution would be a way to print the total in the same place on the form no matter how many items that there are.  A field called Total is used in the detail section,  When I use Sum([total]) in the page footer this does not work, it give me an Error.  The formula will work in the group footer, but then it shows up like I described above.  Also I have the Vendor name and address in the page footer, so I believe I have to have a page footer so it will show up in the correct place on the form.   If this has to be written in code, that would be fine, just need a little assistance.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

fostermom45,

So,

Basically you want the Group footer Summaries to be at the same location on the page for all groups.
Please confirm.

JeffCoachman
ASKER
fostermom45

Yes.  Sometimes we have many details on one page and sometimes the page is full.  The total needs to be in the same place all the time.   Also it could be one page or it could be several pages long.  The total needs to apply to that page.   If this is not possible, then the total should only appear on the last page in the correct spot and total for the entire report.  This won' t work in report totals either because it still puts it immediately after the last detail.
Jeffrey Coachman

fostermom45,

I have seen solutions here that "Pad" the report sections with whitespace to solve these types of requests.

Something like:
If 3 records are left on the page, Pad with .25
If 2 records are left on the page, Pad with .50
If 1 records are left on the page, Pad with .75

If peter57r, is still here perhaps he has some ideas as well.

In the mean time, I'll play around with this for a while.

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

OK,

I'm back.

Are you still interested in this?

I can get you the Page total for every page.
And yes, it will be in the same physical location for every page
Is this what you were after?

JeffCoachman
ASKER
fostermom45

I got it figured out.  Thank you!
Jeffrey Coachman

fostermom45,

Congratulations!
;-)

If you post your own solution you can actually "Accept" your own post.

This will allow others searching for this issue to see your solution.

Jeff
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
fostermom45

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

Yes!

That is exactly what I was going to do!

Congratulations on solving you own issue!
;-)

JeffCoachman