Collate two Reports to printer

[dfishfan]

Ok, been looking for a week and am at wits end.

Ihave two reports, rpt1 is form letter with employee name and address, rpt2 is report with header and footer calculations of time worked. I need to be able to print these reports together, so wont have to hand collate after printing. Normally prints about 100 reports each month.

Please help!!

 

[Peter Reid]

Is adding one of the reports to the other using subreports not an option?

If not, you could use code to print out the (filtered) reports in the order you require.

eg
For each employee
Print rpt1 (filtered for single employee)
Print rpt2 (filtered for single employee)
Next

 

[dfishfan]

Subreports does not work because of header and footer actions in rpt2.

Any help on vb code to set print order would be greatly appreciated

 

[Peter Reid]

Assuming your employees are held in a table called tblEmployees and are uniquely identified in a field called EmpID

Sub PrintReportsByEmployee()

Dim sSQL As String
Dim rs As Recordset

sSQL = "SELECT EmpID FROM tblEmployees;"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

While Not rs.EOF
    DoCmd.OpenReport "rpt1", acViewPreview, , "[EmpID]=" & rs(0)
    DoCmd.PrintOut acPrintAll
    DoCmd.Close acReport, "rpt1"
    DoCmd.OpenReport "rpt2", acViewPreview, , "[EmpID]=" & rs(0)
    DoCmd.PrintOut acPrintAll
    DoCmd.Close acReport, "rpt2"
    rs.MoveNext
Wend
Set rs = Nothing

End Sub

 

[bastardonline]

A bit old post I know. But the code is just what I was looking for. Is the an option to print those reports without opening? I tried to replace acViewPreview with acViewNormal, but in this case starts to print not only selected pages but also to form itself.

Is there any way to fix this?

 

[sxschech]

While your situation is different, part of this might work. This code allows the use of a where clause when saving a report to pdf, perhaps you will only need to add achidden to your existing code. I substituted the values from your example into the code.

'In order to run code contained within the report (hiding price values) need
                    'to open the report hidden before saving to PDF
                    'https://bytes.com/topic/access/answers/930030-formatted-report-shows-different-when-exporting-pdf
                    '20160429
                    DoCmd.OpenReport "rpt1", acViewPreview, , "[EmpID]=" & rs(0), acHidden
                    DoCmd.OutputTo acOutputReport, "rpt1", "PDFFormat(*.pdf)", stExportFileName, ShowPdf, "", 0, acExportQualityPrint
                    DoCmd.Close acReport, "rpt1", acSaveNo

 

[bastardonline]

I did manage to find solution. Works like a charm:

Private Sub Ukaz43_Click()

Dim sSQL As String
Dim rs As DAO.Recordset

sSQL = "SELECT ID FROM 006_Stampa where id between 1 and 3 and natisnjeno = No;"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

While Not rs.EOF
DoCmd.OpenReport "Katalog printer1-stran1", acViewNormal, , "[ID]=" & rs(0)
'DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "Katalog printer1-stran1"
DoCmd.OpenReport "Katalog printer1-stran2", acViewNormal, , "[ID]=" & rs(0)
'DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "Katalog printer1-stran2"
rs.MoveNext


Wend
Set rs = Nothing

End Sub