How do i show totals on the last page of my Report?

[hardrock]

Hi all. I've created a simple purchase order report, that includes a header and footer graphic (jpeg). The report works fine but i want to be able to display my total price and tax figures on the last page of the Purchase order report .At the moment i've entered the two fields in my footer (two texboxes), so the total price and tax amount appears on every page of my report. How do i fix this? any help appreciated. Thanks

 

[KenHigg]

Place it in the report footer section instead of the page footer section.

 

[hardrock]

Yes! it works ken, thanks, but i need to make the totals show at the bottom, as close to the footer as possible. At the moment it shows under the last item on the PO, which could be anywhere. Any ideas?

 

[KenHigg]

Yes! it works ken, thanks, but i need to make the totals show at the bottom, as close to the footer as possible. At the moment it shows under the last item on the PO, which could be anywhere. Any ideas?

In my opinion you're splitting hairs. I'm sure one could do some code to figure out how to make it show up towards the bottom of the last page -

I'd just put a double line or a single heavy line over the total to designate the end of the report to make it clear it is the end of the data/report.

 

[gemma-the-husky]

what a report does, is fills in the page header and footer, and any space left over becomes part of the detail

totals are therefore part of the detail, and other than adding some blank space in the report footer, above the total line, i dont think there is an easy way of achieving this

i suppose you could try and look for some smart stuff to "print" blank rows with the report (totals) footer to move that section down towards the bottom - there must be a way to do it - there is most things - there are some grouping options, like force new page etc, but I dont think these are quite right for what you want

perhaps you could but a thin blank textbox or label above the report footer total and resize that in the report footer format event to the height necessary to format the total where you need it - not exactly sure how to control report pagination though without more research

 

[AggieLJ]

Yes! it works ken, thanks, but i need to make the totals show at the bottom, as close to the footer as possible. At the moment it shows under the last item on the PO, which could be anywhere.

I have the same problem, although mine is an invoicing challenge. It just looks unprofessional to have the report footer (which is the invoice's send-in-your-payment stub) end up at the top or middle of the page... I have them in a sub-report, so if there is VBA code or something to align the sub-report at the bottom of the page, I (as well as everyone else who has this issue) would be grateful for the information.

 

[Xx_TownDawg_xX]

page break?.. maybe set up a special "last page" ?

 

[rapsr59]

Hi,


In Texas, where I do business, I am required to send in a report showing the employees that are employed by me each quarter. The report requires the Social Security #, Employee name, and amount of gross wages each employee received for the quarter. My payroll program is the source for the report.

The detail section displays, and prints, the data like this:

(Because of the limitations of the Forum I am unable to show you the necessary spaces that are required between the data. The SS# data is close to left side of the form, the Name is in the center and the Gross Wages are very close to the right side of the form.)

444-44-4444 -------------U R CRAZY ------------- $5,000.00
555-55-5555 -------------I M MADD ---------------$4,638.00
...

At the bottom of the state form, on each page, in a specific area, that is in line with Gross wages and that can not change position on the report, the total (sum of) gross wages per page must be printed.

I have to admit I thought I would blow my brain on this one.

I used the following method.

In the Declarations Section I created a Private variable and named it curPageTotal as a currency type.

[B][FONT=Times New Roman][COLOR=#333399][SIZE=3]Option Compare Database[/SIZE][/COLOR][/FONT][/B]
[B][FONT=Times New Roman][COLOR=#333399][SIZE=3]Option Explicit[/SIZE][/COLOR][/FONT][/B]
 
[B][FONT=Times New Roman][SIZE=3][COLOR=#333399]Private[/COLOR] curPageTotal [COLOR=#333399]As Currency[/COLOR][/SIZE][/FONT][/B]

The field name, text control name, and control source in the detail section that displays the Gross income for each employee for the quarter, is QtrGross. (This is the $5,000.00, $4,638.00, etc...) I then placed the following code in the On Print event in the Detail Section of the report.

[B][FONT=Times New Roman][SIZE=3][COLOR=#333399]Private Sub[/COLOR] Detail_Print(Cancel [COLOR=#333399]As Integer[/COLOR], PrintCount [COLOR=#333399]As Integer[/COLOR])[/SIZE][/FONT][/B]
 
[B][FONT=Times New Roman][COLOR=green][SIZE=3]'This code accumulates and sums up the QtrGross for each employee, [/SIZE][/COLOR][/FONT][/B]
[B][FONT=Times New Roman][COLOR=green][SIZE=3]'in the detail section, into the Public currency variable "curPageTotal"[/SIZE][/COLOR][/FONT][/B]
[B][FONT=Times New Roman][SIZE=3]curPageTotal = curPageTotal + QtrGross[/SIZE][/FONT][/B]
 
[B][FONT=Times New Roman][COLOR=#333399][SIZE=3]End Sub[/SIZE][/COLOR][/FONT][/B]

On the Page Footer Section I created a text box control in a position to match the vertical and horizontal position on the states form and name it txtSumQtrGrossPerPage
I then place the following code in the On Format Event in Page Footer Section.

[B][FONT=Times New Roman][SIZE=3][COLOR=#333399]Private Sub [/COLOR]PageFooterSection_Format(Cancel As [COLOR=#333399]Integer[/COLOR], FormatCount As [COLOR=#333399]Integer[/COLOR])[/SIZE][/FONT][/B]
 
[B][FONT=Times New Roman][SIZE=3][COLOR=green]'This sub places the value computed in the detail print event[/COLOR][/SIZE][/FONT][/B]
[B][FONT=Times New Roman][COLOR=green][SIZE=3]'into the txtSumQtrGross control in the Page Footer section[/SIZE][/COLOR][/FONT][/B]
[B][FONT=Times New Roman][SIZE=3]txtSumQtrGrossPerPage = curTotal[/SIZE][/FONT][/B]
[B][FONT=Times New Roman][SIZE=3][COLOR=green]'Reset for the next page[/COLOR][/SIZE][/FONT][/B]
[B][FONT=Times New Roman][SIZE=3]curTotal = 0[/SIZE][/FONT][/B]
 
[B][FONT=Times New Roman][COLOR=navy][SIZE=3]End Sub [/SIZE][/COLOR][/FONT][/B]

The form is a three page report that prints the total of each page in a set position. I hope this helps you with what you are trying to accomplish with your report.


Richard

PS You can place the calculation on any report sections below the detail section!

 

[ansentry]

If you want a simple method to put the "Grand Total" in the "Last Page Footer" then have a look at my sample.

1 >You must have the

="Page " & [Page] & " of " & [Pages]

in the page footer, I have made mine visible = false.

2> Put an unbound text box in the report footer (see report in sample) make it visible=false

3> Put an unbound text box in the page footer (see report in sample)

4> Look at code in PageFooter / OnFormat.

The code could be a bit more elegant.

 

[DBL]

Just used this example very successfully. Thanks, John.

DBL

 

[ansentry]

Glad it worked for you.

 

[AggieLJ]

You have made my invoices look PERFECT! Another big THANKS to John from the other side of the world !!
AggieLJ

 

[ansentry]

AggieLJ,

Always glad to help anyone from Texas.