I have an invoice print that uses a sub-report for the invoice details. I created a text box in in the Report Footer section with following control source =[SalesInvoiceLineSubreport].[Report]![TotalAmount]. That works fine but I want it to print at THE bottom of the page along with some text that is contained within Labels (ie standard text about late charges and other legal stuff).
I tried moving all of that to the Page Footer Section and that worked great, however, if the invoice details ran past the end of page 1 to page 2, the Invoice total and labels print at the bottom of both page 1 and 2. Any ideas how I can get everything (Invoice total and legal mumbo jumbo) on the bottom of the LAST page ONLY
Kevin Yip
@Reply 4 years ago
In your subreport you need to do a VBA trick. Check if the last record of the subreport has just been printed. If not, set the main report's footer to blank. If yes, set the footer's value. In your subreport's Detail_Format event, write something like this:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Parent!footerinfo = IIf(Me!somefield = ValueInLastRecord, "Footer to be printed", "")
End Sub
"Parent" is an Access keyword that refers to the parent object of the subreport, which is the main report in this case. "footinfo" is the name of your main report's footer control.
"somefield" is a field on your subreport, could be an ID, invoice#, whatever. It needs to be something that can help you locate the last record of the subreport. If you choose "invoice date" as the field, but all records have the same invoice date, then naturally it won't help you. I suggest you choose a unique index field such as the primary key. Use the MoveLast command on your subreport's Recordset to check what value it has in the last record.
Edward MastoloniOP
@Reply 4 years ago
What does "Footer to be printed" mean?
The control name of my report footer is "ReportFooter"
Edward MastoloniOP
@Reply 4 years ago
Kevin Yip
@Reply 4 years ago
"Footer to be printed" is just a placeholder for whatever you want to print or not want to print in your footer section.
"ReportFooter" is *not* your footer's control name. The names of those textboxes in the footer section are. Let me rewrite my code in the nomenclature you can relate to:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Parent!TextboxNameInYourFooter = IIf(Me!somefield = ValueInLastRecord, PutYourSubformTotalHere, "")
End Sub
Edward MastoloniOP
@Reply 4 years ago
Got it! Thanks so much
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
TechHelp.
