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?
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?
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
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
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.
fostermom45,
So,
Basically you want the Group footer Summaries to be at the same location on the page for all groups.
Please confirm.
JeffCoachman
So,
Basically you want the Group footer Summaries to be at the same location on the page for all groups.
Please confirm.
JeffCoachman
ASKER
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.
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
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
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
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
I got it figured out. Thank you!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes!
That is exactly what I was going to do!
Congratulations on solving you own issue!
;-)
JeffCoachman
That is exactly what I was going to do!
Congratulations on solving you own issue!
;-)
JeffCoachman
Have you deleted the grouping?