How to print two copies of each page of the report

[aman]

I am writing the following line to print a report:

DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport

but as I want to print two copies of each page that are printed out so I have changed the code into the following:

DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
 
DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport

Basically I had written one line twice to print two copies. It is now printing two copies but e.g there are 4 pages so it will be printed out in the following order:

page1,page2,page3,page4,page1,page2,page3,page4

so it is difficult to combine same pages together if there are about suppose 100 pages in total of that report . I want it to print in the following format:

page1,page1,page2,page2,page3,page3,page4,page4.....

Can anyone please tell me hows it possible.

Thanks
Aman

 

[Scooterbug]

Check the printer settings. There should be an option to Collate. Make sure that is checked as a default setting.

 

[aman]

Hi

Well I don't want to do this manually by clicking on collate in printing option as the project is used is by 100's of people at different locations. So I want to print two copies of each page using vba code or by setting any property .

I hope you understand my problem.

Thanks
Aman

 

[Endre]

This should work:

Create a new table and add 2 entries of data only (or 3 entries if you want your report tripling). Open the existing query you are running your report from and include the new table, but do not link the table to your existing query in any way. Now when you run the query you should see your data doubled, tripled, or for how ever many entries you have in the table. The only problem you may get is if the existing query is very complex, and the introduction of the unlinked table produces a message saying something like "Query is too complex to be calculated...."

I sometimes use a variation on this for xTab queries to ensure I get all my values in.

 

[aman]

no i don't want to add two entries of data in the table. It will rather make my table more complicated. And also there are 1000s of records already present in the table and if the user want to create report for the old data then again the same problem.

I am looking for some other solution that will collate same copies together.

Thanks
Aman

 

[Scooterbug]

The other option is to copy the report exactly but give it a different name. Then print the first one, then the second one.

DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
 
DoCmd.OpenReport [COLOR=red]stDocName2[/COLOR], acViewNormal, , mysCriteria, acDialog, topLabelOnReport

If you want to print anything but 2 copies, however, will require some creative coding

 

[aman]

If I do the way you told me then again it will not collate the same pages together. Again the same problem I discussed at first.

Thanks
Aman

 

[Scooterbug]

No, it will because it's technically two different reports. The reason why it prints the way it is now is because it's the same report. By sending the two reports to the printer, the print spool will process each report on it's own. So say your reports are named Report1 and Report2. When you send them to the printer, the spooler prints one copy of Report1. Once that job is finished, it goes ahead and prints Report2.

 

[Endre]

Let me rephrase. Create a brand new table. This has nothing to do with the data or anything else in your database. Call it t_1, and a single field f_1. Now add 2 records to this table. Add any data you like, it doesn't matter. Just make sure there are only 2 records there in your new Table. If you want you can have the first record as "Report1", and the second as "Report2".

Now add this table into your query for your main report / form as described earlier. Your returned dataset will be doubled and will print your output as you requested. You will not be doubling your data in your database - only the output will be doubled. Your original query may have output 10,000 records. This new query will have 10,000 from your SQL + 2 from the new table. BUT because you are not linking them, your output will now be 20,000 records. If you want, you can bind a header or footer control to your new field so you can see which page refers to "Report1" or "Report2". Your page numbering will still be sequential though - but we can VBA that also if required.

It will work - I don't post solutions that don't work (My first Full App with Access was using v1.1)

 

[aman]

Hi Scooterburg,Sorry for replying late. I did the thing you told me but it didn't work .

Thanks
Aman

 

[aman]

Hi All

I have almost solved my problem but just one thing I couldn't figure it out is "how can we count the total number of pages of the report". I used the following code:

 For MyPageNum = 1 To [B][COLOR=green]2[/COLOR]
[/B]        'NumPages is the number of pages to print.
        DoCmd.SelectObject acReport, "Students Information Report", True
        DoCmd.PrintOut acPages, MyPageNum, MyPageNum
        DoCmd.SelectObject acReport, "Students Information Report", True
        DoCmd.PrintOut acPages, MyPageNum, MyPageNum
    Next MyPageNum

In the above code the total number of pages of the report are 2(marked in green in above code) so it works fine. But how can I find the total number of pages of the report in actual.

Thanks
Aman

 

[Scooterbug]

Why do you need to count the number of pages? Based on your code, you did not try to make a copy of the orgininal report and give it a different name. I thought I was clear when I suggested that you copy the report exactly but give it a different name.

DoCmd.SelectObject acReport, "Students Information Report", True
DoCmd.SelectObject acReport, "Students Information Report2", True

Did you try the code like above?

 

[aman]

Hi Scooterburg

I tried what you told me but its not working the way I want. I want to collate same pages while printing but its not doing that.
Following is the code I tried but no use:

 DoCmd.SelectObject acReport, "Students Information Report", True        DoCmd.PrintOut
DoCmd.SelectObject acReport, "a1", True
 DoCmd.PrintOut

I tried the following as well but again no use:

 stDocName2 = "Students Information Report"
 stDocName3 = "a1"
DoCmd.OpenReport stDocName2, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
DoCmd.OpenReport stDocName3, acViewNormal, , mysCriteria, acDialog, topLabelOnReport

Could you please suggest me anything else. or if you tell me how to count the number of pages in a report before printing like my earlier post then my problem will be resolved.

Thanks
Aman

 

[Endre]

Hi Aman, the only other thing I can suggest is that you allow your loop to fail when it reaches the end of your print-page-by-page job i.e

For MyPageNum = 1 To 999999
And set an OnError to exit the printing when it fails to print LastPage+1

Could you please suggest me anything else

HOWEVER - I really do not understand why you have not implemented my solution. I redescribed it to you differently in a subsequent posting, and currently you are making things a) more difficult, and b) your print job will run slowly by sending each page to the printer one by one. Please re-read my posts carefully as it will solve your problem for you. You do not have to double your data at all.

 

[aman]

Hi Endre

How can I set an OnError to exit the printing when it fails to print LastPage+1. Could you please give me the code for this.

Sorry,but really don't know how to use your other method as I am beginner to access programming but now I am able to solve that problem except the lastpage number thing.

Thanks
Aman

 

[Endre]

Hi Aman, here is my solution as an attached zipped mdb file which I hope will be clear from this example.

There are 2 reports in the example - the first shows your "current report" using a query to filter some data and display in the report.

The second shows "NEW report" which uses a different query. It uses the same query as for your normal report + I have included the table REPS in the query. There is no linking or common fields between this new table and the current query for the report. And there shouldn't be either. This new table contains 4 entries as in my example I want 4 copies of my report. Have a look at this table. Now have a look at the query I used for the New Report. (I also have created another query that counts the number of reports and includes this because I want to calculate my page numbers correctly and print them.)

Look at the formula I have used for page numbering in the new report. It looks complicated, but in fact it is quite simple and cycles around the number of reports you have.

If you want me to do this for your report, you will have to send me your mdb (without the 1000's of data of course) so I can set this up for you - but it is not that hard for you to do.

PLEASE NOTE: The page numbering will only work this way if there is 1 pages worth of data for each record of output. If the output per record crosses over to many pages we will have to find a different way to number your pages.

PLEASE NOTE: It is also important that the LAST ordering, or sequencing in your report is from the REPS table. Else you will have your current problem and that you will print report by report, rather than record by record. That is why I have sequenced the Sort order in the report to be Project ID first, and then Report ID LAST.

I really would prefer you to try this option (note there is NO VBA involved here) as it really is the way you should go. If we struggle too hard to make this work, I will give you the "OnError" solution.

 

[Scooterbug]

Just for the record....

I tried the method I gave you earlier...and I had no problem printing out 2 copies of an identical report. All pages were corallated as well.

 

[DCrake]

Right, heres what to do.

Step 1

Declare a new public variable in a standard module

Public bFlagLastPage As Boolean

Step 2

Add the following function to the same module

Function MultiPrint(nCopies As Integer,strReportName As String)

Dim cPage As Integer


DoCmd.SelectObject acReport, sttrReportName, True


For cPage = 1 To 100
    If bFlagLastPage = True Then Exit For
    DoCmd.PrintOut acPages, cPage, cPage, , nCopies, True
Next cPage

End Function

Step 3

Design the form that will be calling the report and enter the following code on the OnClick of the command button

Call MultiPrint(nn,"ReportName")

Where nn equals the number of copies you want
Where "ReportName" is the name of the report you want to print

Step 4

Design the report and then click on the page footer section of the report and select build event

Then enter the following

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
[B]If Me.Page = Me.Pages Then
    bFlagLastPage = True
End If[/B]

End Sub

Save the report

The last bit of code is telling the function that when it prints the last page set the variable to True which inturn causes the code to exit the for next loop. In that sense you do not need to know how many pages the report contains.

The issue you had previously was that you was selecting the object each loop, whereas you only need to select it once outside of the loop.

Hope that this helps.

David

 

[aman]

Thanks a lot DCrake. Its working perfectly.

Cheers

 

[aman]

Hi Dcrake

Sorry to bother you again but when I applied your method in the sample database then it was working gr8 but now when I am trying to do the same thing in my actual project then it is printing the form where command button is clicked and its not printing the report. I don't know why its happening. On the form click event I have written the following code.

 Call MultiPrint(2, "FrontSheet Report")

The module contains the following:

Public bFlagLastPage As Boolean
Function MultiPrint(nCopies As Integer, strReportName As String)
Dim cPage As Integer

DoCmd.SelectObject acReport, strReportName, True

For cPage = 1 To 10
    If bFlagLastPage = True Then Exit For
    DoCmd.PrintOut acPages, cPage, cPage, , nCopies, True
Next cPage
End Function

So why its printing the form and no the report. Please guide me in this.

Thanks
Aman