In Access, How to Convert Multiple Reports to 1 PDF file?

[Otter7]

I want to add a control button to convert all reports for 1 selected record to print in page order as 1 pdf file. There is a control button to print 1 full report per selected record but some users will want to pdf the report to email to clients. What would the VBA coding be for the On Click Event Procedure for the PDF Report button? Or would a macro be better? I am a beginner with VBA & macros so elementary explanations/coding would be helpful....Than k you!

[liimra]

You can use either but I would go for VBA.

If you want to use macro, use the "OutputTo" action. Choose report as Object Type and select the report name.

If you want to use VB

 DoCmd.OutputTo acReport, "ReportName", acFormatPDF, , True 

Note: "True" will open the Pdf file after completing.

Hope this is what you need,

Regards,
Ali

[Otter7]

Yes, but do I need to list all the report names? There are 23 individual reports per 1 recored to be a single pdf file in page order...so the user can save the pdf ie as 'One Montgomery Audit'....

[Otter7]

Wooh! That worked except the pdf file is showing all the records and I need just 1 record selected from a drop down list. The user cannot enter data, preview or print or pdf unless an audit is selected...This is the code I have but wiill add all the rpt nams...

Private Sub btnPDF_Click()
If Audit_Dte_ID = 0 Then
MsgBox "No audidt has been selected"
Exit Sub
End If
DoCmd.OutputTo acReport, "rpt1aAuditorin fo", acFormatPDF, , True

End Sub
-----

How do I code it so pdf is only the selected audit records?

[liimra]

I have one solution and of course there are others.
When the user chooses the record he/she wants, we search for that record (using the combobox After Update event).

The "Drop Down List" After Update event will be:

 DoCmd.SearchForRecord , "", , "[Audit_Dte_ID]=" & ComboName.Column(0)

Where Audit_Dte_ID is the first column in the record source of the combo box.

Now, we have the record we want so we just add the code you stated to the Export button.

If ComboName Is Null Then
MsgBox "No audit has been selected", vbOKOnly, "No Selection"
ElseIf Combo Is Not Null Then
DoCmd.OutputTo acReport, "rpt1aAuditorinfo", acFormatPDF, , True
End If
 

Hope this helps,

Regards,
Ali

[Otter7]

Ok...I'll give it a try and respond later this evening, California time...off work right now...Thanks and I will respond back...

[Otter7]

This is the code I entered but it does not work, comes up as error End If:

Private Sub btnPDF_Click()
If Audit_Dte_ID = 0 Then
MsgBox "No audit has been selected"
ElseIf Me.Combo66 Is Not Null Then
End If
DoCmd.OutputTo acReport, "rpt1aAuditorin fo", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt1ProjectBld gInfo", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt2Constructi on", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt3Bldgoccupa ncy", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt4CoolingPla nt", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt5HVACSystem ", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt6ComfortHea ting", acFormatPDF, , "Audit_dte_ id=" & Audit_Dte_ID, , True

Continues with all the report names and last line is End Sub..I'm still missing the loop somewhere...

[liimra]

There are two problems with your code, The "If Statement" and the fact that you can't assign criteria to the OutputTo Function.

In order to overcome the problem you are having and output the selected record only, you open the report first in preview mode then you Output it to PDF. Once this is done, you close back the report preview. Suppose the user cancels the out operation, then he/she will get "cancel" error --> that is why you Resume Next on Error.

So the code will be:

If (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Null")) Then
 MsgBox "No audit has been selected"
 ElseIf (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Not Null")) Then
 On Error Resume Next
DoCmd.OpenReport "FirstReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
DoCmd.OutputTo acOutputReport, "FirstReportName", acFormatPDF, , True
DoCmd.Close
DoCmd.OpenReport "SecondReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
DoCmd.OutputTo acOutputReport, "SecondReportName", acFormatPDF, , True
DoCmd.Close
End If

Change names where applicable & add the number of reports you want, and you are done.

Regards,
Ali

[Otter7]

For the "FormName" I enter the name of the form but for [Forms] I do not enter the form name, correct?

[liimra]

Correct

Regards,
Ali