Report not printing correctly (1 Viewer)

sprijohn · Oct 11, 2018

I have a report that I am trying to print that adds blank lines if the number of records on a page is less than 24. The report work perfectly on screen but when I send it to the printer it prints thousands of extra pages. (I test it be printing to a pdf printer which I cancel once I see the page count start to climb.) I am using some code I found online that was written by Danny Lessandrini and I have begun to make modifications to fit my needs.

Why is it not printing correctly? I can't find where the code is getting stuck in a loop. This one has me stumped.:banghead:

CJ_London · Oct 11, 2018

be helpful if you identified which report you are talking about and which bit of code. Also provide a link to the original code (or repeat here) so we can identify what has been changed

sprijohn · Oct 11, 2018

The report in question is rptPTSB. The original code is in rptRenewals_Multiple_Pages. I changed the variable sDocName in frmTestReports from "rptRenewals_Multiple_Pages" to "rptPTSB" to point to rptPTSB and set the default from 6 to 24 in the private sub cmdPreviewMultiple_Click(). Also, I am developing this in Access 2013, if that affects it anything.

June7 · Oct 11, 2018

Here is code I use to print lines. This code is in a general module:

Code:

Option Compare Database   'Use database order for string comparisons
Option Explicit

Global TotCount As Integer

' Textbox named TotGrp in report header with expression: =Count(*)
' Call the SetCount() function from the group or report header section's
' OnPrint property using expression: =SetCount([Report])
' Call the PrintLines() function from the detail section's OnPrint
' property using expression: =PrintLines([Report],[TotGrp]).

Function PrintLines(r As Report, TotGrp)
   TotCount = TotCount + 1
   If TotCount = TotGrp Then
        r.NextRecord = False
   ElseIf TotCount > TotGrp And TotCount < 50 Then
        r.NextRecord = False
        r.ID.Visible = False
   End If
End Function
 
Function SetCount(r As Report)
   TotCount = 0
   r.ID.Visible = True
End Function

sprijohn · Nov 30, 2018

Sorry I Haven't responded sooner but work got extremely busy and I was not able to work on this until now. I created a new report called rptPTSBAWF to test the code. I removed the TotGrp field in the Header and hard coded a 24 in every instance of that field in the VBA code. When I ran the report I got 25 pages with a single record on each. I need to generate Pages that contain 24 records on each. Ultimately I need it to pad the final page with blank lines if it has fewer that 24 records on it. What am I doing wrong in the coding of the report?

June7 · Nov 30, 2018

I removed code from the report and it still prints 25 pages. So it's not the code that is issue. Unfortunately, cannot find any reason for this.

Sometimes weirdness is corruption that cannot be fixed.

Why use this code? Doesn't report rptPTSB accomplish what you want?

sprijohn · Nov 30, 2018

rtpPTSB does work and creates reports exactly like I want in print preview. However, when you send it to a printer (or print to pdf) it runs the page count up as if it is stuck in a loop. I sent it to a printer once and it amassed 400 pages before I could stop the printer. If someone could help me determine why this is happening I would be greatly appreciative. This one has been stumping me for over 4 months.

June7 · Nov 30, 2018

I just found a Page Break control in the detail section. This will cause a page for each record.

Removed it but still cannot get my code to work in your report.

It works perfectly in my db, even direct to printer.

However, if you can get code to work will need to add horizontal Line control to define the blank rows and vertical ones to define columns.

sprijohn · Dec 1, 2018

I'll look into that. The data table only has 25 records. Would the page break in details, which is suppose to only activate when 24 records have been printed on a given page, explain why it is printing hundreds and even thousands (I let it run once while printing to pdf) of pages?

sprijohn · Dec 1, 2018

June7,

I found the page break on the report and removed it. Multiple records are now printing on a page. And it is printing multiple pages. Thanks.

I now need to change the data so that there are no duplicates. That will allow me to see which records are printing. Any suggestions on how to pad the second sheet with empty files so that each page has exactly 24 lines?

sprijohn · Dec 1, 2018

Date is flowing correctly into each page.

Just need to pad the last page with blank lines to get 24 lines on it.

June7 · Dec 1, 2018

Did some more experimenting with my db. The code fails if there are more records than can fit on 1 page. This is what is happening in your db.

I actually don't have this code deployed in a production db. Attempted and abandoned, probably because of the above issue.

I found the website providing your code. Apparently have to create an account to download the file. Did you change the code at all? Code in your db does look different from the tutorial.

Another approach attempted and abandoned https://www.pcreview.co.uk/threads/add-draw-blank-lines-to-access-report.2929347/

Most reliable solution may be code that builds 'dummy' records. https://stackoverflow.com/questions...an-access-report-so-that-it-matches-a-printed

JHB · Dec 1, 2018

The "problem" is that the report first go into "Print Preview" and then you send it to the printer and when you do that some of the format events isn't trigged like "PageHeader_Format" and therefore the Static variable iLine isn't set to 0 (as it need to be).
The solution is to look at the "Me.CurrentRecord", if it is 1, then the report is just started to print either to the screen ("Print Preview") or to the printer.
Database attached, use the frmTestReports.

isladogs · Dec 1, 2018

Hi JHB
Well done.
As with all these things, the solution was obvious with the benefit of hindsight
But I couldn't see it myself

JHB · Dec 1, 2018

isladogs said:
..
As with all these things, the solution was obvious with the benefit of hindsight
..

You're right, but some (most) times it's hard to find out where to find the solution, even if it's just in front of one's nose, (afterwards you think, why he.. did I spend so much time to find it)!

isladogs · Dec 1, 2018

I usually find the solutions to odd problems like this come to me in a flash of inspiration when I'm away from the computer e.g. walking the dog or just as I'm falling asleep (which of course wakes me up again....)

Anyway. I'm sure the OP will be suitably grateful.... it will save a fortune on ink when printing

June7 · Dec 1, 2018

I think qryData should probably be RIGHT instead of INNER joins. This will result in 29 records in query.

OP said requirement was for 24 lines per page. Report generates infinite pages if I input 24 in the combobox. First page has 23 records, second has 5 records, 1 record is lost.

rptPTSB design won't allow 24 records per page. 23 will work. Need to reduce Detail section height just a little, as in rptPTSBAWF, which does allow 24 records per page. Set PageBreak control Top to 0.25 and the Detail section Height to 0.25 as well.

I am annoyed that I cannot get Lesandrini's original code from the web page to work.

JHB · Dec 2, 2018

June7 said:
..
I am annoyed that I cannot get Lesandrini's original code from the web page to work.

Is it the code in post #4 you mean?
If yes, then the code is missing the part where the last record has been reached and the page is not yet fully filled.
Because when the last record is reached, the printing stops, (report finish).

June7 · Dec 2, 2018

No, post 4 is different code. It works as long as there is only 1 page full or less of records. I tested on a report that allows 50 lines.

Here is the code OP adapted https://www.vb123.com.au/200310_dl_pad.htm

sprijohn · Dec 5, 2018

Thank You, Thank You, Thank You, JHB!!!

Your Change in the code at the beginning of the Detail_Format Section got it to finally print (At least to a PDF).

I did discover that Print Preview was allowing the report to never exit the module. Clicking on the next page icon created another new page which did not contain data. I changed the last if statement in the datail_format subroutine from "If iLine <> iRptLines Then Me.NextRecord = False" to "If iLine < iRptLines Then Me.NextRecord = False" to force the subroutine to exit once the number of lines printed equaled or exceeded the number of lines needed to give 24 line per page.

The only problem left is that the report is now printing one extra blank line for each page printed in the report. Since these are on the last page it could work. That is unless there are a lot of names to be printed.

I stripped down the DB to focus on the print routine. I also cleaned up the report and the test data to more closely match what I am attempting to accomplish in the production DB. Please see attached file.

This is very close to being finished. Any suggestion on how to control the extra lines would be greatly appreciated.

Report not printing correctly (1 Viewer)

Registered User.

Attachments

Super Moderator

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Have been here a while

Attachments

MVP / VIP

Have been here a while

MVP / VIP

AWF VIP

Have been here a while

AWF VIP

Registered User.

Attachments

Similar threads

Users who are viewing this thread