locked
Report Generates 1000's of pages RRS feed

  • Question

  • Hi,

     

    I have a simple 1 page report in an Access 2007 application, which 90% of the time generates with no problem. However, every now and again when the user clicks on print button from a preview window, the printing dioalog comes up and counts up thousands of pages (1 of xxxx and so on). The user then has to click the cancel button (obviously not wanting to send 1000's of pages to the printer), quit the application completely and restart it. Once they restart, the report will work correctly.

    I have uninstalled the printer & reinstalled it, deleted the report & reimported it from a backup copy. Because it happens so infrequently it's very hard to establish if a change has worked until it happens again. There is no code behind the report, and it is very simple based on a query which uses a order number to filter the recordset down.

    Any help would be much appreciated.

     

    Jon.

    Friday, May 13, 2011 8:58 AM

Answers

  • The expected amount of records are retrieved by the query. I don't think it's anything to do with the query - the print preview is correct. When I hit print from the preview, it's as though I have asked it for a thousand+ copies; it send the single page thousands of times to the printer. It only happens on this report in the DB, so I'm pretty sure it's not a printer driver issue (which I've removed & reinstalled anyway). It is so inconsistant I doubt I can arrive at a set of conditions that produce the behaviour all the time. After seeing Alphonse has encountered the same problem but in a different version with even less consistency, I think I shall rebuild the report from the ground up and see if it is some form of corruption in the report.

    Thanks to everyone for their comments & suggestions.

     

    Jon

    • Proposed as answer by Bruce Song Friday, June 3, 2011 1:18 PM
    • Marked as answer by Joneboy Friday, June 3, 2011 1:38 PM
    Sunday, May 22, 2011 5:57 AM

All replies

  • Hi Jon,

     

    Can you post the SQL of the Query of your Report and also the code to open the Report?

     

    Thanks,

    Daniel


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, May 13, 2011 1:53 PM
  • Here's the underlying query

    SELECT Sales_Orders.JGOrderNo, Format([OrderDate],"dd/mm/yy") AS ODate, Format([RequiredBy],"dd/mm/yy") AS RDate, Min(GetWorkingDays([OrderDate],[RequiredBy])) AS LeadTime, ActualCustomer.CompanyName, OrderedBy.CompanyName, Sales_Orders.CustomerOrderNo, Sum(Sales_Order_Lines.CasesOrdered) AS SumOfCasesOrdered, Sum([CasesOrdered]*[CasePrice]) AS OrderValue, Sum(Sales_Order_Lines.CasesDelivered) AS SumOfCasesDelivered, Sum([CasesDelivered]*[CasePrice]) AS DeliveredValue, (Sum([CasesDelivered])/Sum([CasesOrdered]))*100 AS PercentOfCases, Sales_Order_Lines.DateDelivered, Sales_Order_Lines.Status, IIf([OrderType]='Consignment','Consignment',[InvoiceNumber]) AS Invoice, Sales_Orders.OrderDate, Sales_Orders.DateCreated
    FROM ((Sales_Orders LEFT JOIN Address AS OrderedBy ON Sales_Orders.CustomerId = OrderedBy.ID) LEFT JOIN Address AS ActualCustomer ON Sales_Orders.ActualCustomerId = ActualCustomer.ID) INNER JOIN Sales_Order_Lines ON Sales_Orders.ID = Sales_Order_Lines.SalesOrderID
    GROUP BY Sales_Orders.JGOrderNo, Format([OrderDate],"dd/mmm/yy"), Format([RequiredBy],"dd/mmm/yy"), ActualCustomer.CompanyName, OrderedBy.CompanyName, Sales_Orders.CustomerOrderNo, Sales_Order_Lines.DateDelivered, Sales_Order_Lines.Status, IIf([OrderType]='Consignment','Consignment',[InvoiceNumber]), Sales_Orders.OrderDate, Sales_Orders.DateCreated;

    Here's the call to open it

    DoCmd.OpenReport "Sales_order_Summary", acViewPreview

    It open fine in the preview window, displaying just one or two pages depending on the amount of data pulled back by the query. The problem occurs when the user hits 'Print' on the ribbon, it sometimes runs away reporting it will generate 1000's of pages.

     

    Thanks,

     

    Jon.

    Friday, May 13, 2011 2:08 PM
  • Hi Jon,

    I noticed no Where statement, so are you sure when you open this query, only one record is shown?

     

    Other then that I have no clue why else it will print 1000 pages...

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, May 13, 2011 4:23 PM
  • Thanks for the reply. It's not the data that's the problem; in the preview window I get a one page report, with the correct data. Sometimes, and there is no consistency to it, when the user hits print, the report behaves as though they have asked it to print 1000's of copies. If you don't cancel the report generation and let it get to the printer, the same page comes out over and over again.

    The problem with trying to solve the issue I have, is the lack of consistency. It only happens maybe 10% of the time.

    Saturday, May 14, 2011 7:54 AM
  • Hi Joneboy,

    I tried to reproduce your problem but failed. Could it be the cause of the print settings, such as the Number of Copies was set to be 1000?

    It will be nicer if you can share the demo file with us on the skydrive: http://www.skydrive.com so that we can do further research about this problem.

    Hope this helps.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, May 18, 2011 8:05 AM
  • Hi Bruce, thanks for the response.

    Number of copies is definately set to only 1, and the problem is completely inconsistent. It will work for weeks printing one page (this report gets generated maybe 20 times a day, so many times it works), and then this problem manifests. Quitting the app (which is running under the runtime not full access) and restarting it clears the problem and on 1 page will print again.

    If I can find time to strip the file back down I will post a sample, at the moment the user just will have to continue to be quick on the cancel button.

     

    Regards,

     

    Jon.

    Friday, May 20, 2011 10:27 AM
  • You can hit this type of problem if you have a keep together setting that forces a new page and the data just overflows a page.  Access keeps starting new pages in an attempt to print the data together. 
    Saturday, May 21, 2011 5:49 AM
  • Thanks saberman, that makes sense.

    I've taken the keep together setting off and I'll monitor it to see if this solves the problem

     

    Jon.

    Saturday, May 21, 2011 6:46 AM
  • That does make some sense, but why would it be isolated to printing and not preview?

    BTW, I have hit the same problem in Access 97 and 2000, with even greater inconsistancy and could not explicitly duplicate or solve.

    Saturday, May 21, 2011 12:42 PM
  • What happens when you run the SQL Statement as a stand alone SELECT query? How many records are returned?
    David H
    Saturday, May 21, 2011 1:27 PM
  • The expected amount of records are retrieved by the query. I don't think it's anything to do with the query - the print preview is correct. When I hit print from the preview, it's as though I have asked it for a thousand+ copies; it send the single page thousands of times to the printer. It only happens on this report in the DB, so I'm pretty sure it's not a printer driver issue (which I've removed & reinstalled anyway). It is so inconsistant I doubt I can arrive at a set of conditions that produce the behaviour all the time. After seeing Alphonse has encountered the same problem but in a different version with even less consistency, I think I shall rebuild the report from the ground up and see if it is some form of corruption in the report.

    Thanks to everyone for their comments & suggestions.

     

    Jon

    • Proposed as answer by Bruce Song Friday, June 3, 2011 1:18 PM
    • Marked as answer by Joneboy Friday, June 3, 2011 1:38 PM
    Sunday, May 22, 2011 5:57 AM
  • Hi Jon

    I am having a similar problem even without previews, sending the report to the printer directly.

    Since this post is a few months old, could you tell me if this setting stopped this problem from reoccuring?

    Thanks.

    Thursday, November 17, 2011 1:38 PM
  • Hi Badri,

     

    Sorry I never did solve this problem. 95% of the time, everything worked just fine and every now & again, sending to ptint from print preview would just count up thousands of pages. I had to just tell the users to cancel quickly & turn off the printer, which is pretty much the least professional soultion I have ever come up with!

    Jon.

    Thursday, November 17, 2011 1:48 PM
  • After trying different options, this is the code I use to control such a behaviour (this is just a band-aid solution) assuming that my report will not go beyond 3 pages. The one obvious problem is I am using acIcon because acHidden is not working and still the report view opens & closes.

     

    DoCmd.OpenReport stDocName, acViewPreview, , , acIcon, stLinkCriteria
    Reports(stDocName).Visible = False
    DoCmd.SelectObject acReport, stDocName
    DoCmd.PrintOut acPages, 1, 3
    DoCmd.Close acReport, stDocName
    

    If someone finds a better solution or find this may not work, please let me know.

     

    Saturday, November 19, 2011 5:01 AM