469,330 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

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

18
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....Thank you!
Sep 10 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. If (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Null")) Then
  2.  MsgBox "No audit has been selected"
  3.  ElseIf (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Not Null")) Then
  4.  On Error Resume Next
  5. DoCmd.OpenReport "FirstReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
  6. DoCmd.OutputTo acOutputReport, "FirstReportName", acFormatPDF, , True
  7. DoCmd.Close
  8. DoCmd.OpenReport "SecondReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
  9. DoCmd.OutputTo acOutputReport, "SecondReportName", acFormatPDF, , True
  10. DoCmd.Close
  11. End If
Change names where applicable & add the number of reports you want, and you are done.

Regards,
Ali

25 17876
liimra
119 100+
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
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.OutputTo acReport, "ReportName", acFormatPDF, , True 
Note: "True" will open the Pdf file after completing.

Hope this is what you need,

Regards,
Ali
Sep 10 '10 #2
Otter7
18
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'....
Sep 10 '10 #3
Otter7
18
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, "rpt1aAuditorinfo", acFormatPDF, , True

End Sub
-----

How do I code it so pdf is only the selected audit records?
Sep 10 '10 #4
liimra
119 100+
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:

Expand|Select|Wrap|Line Numbers
  1.  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.

Expand|Select|Wrap|Line Numbers
  1. If ComboName Is Null Then
  2. MsgBox "No audit has been selected", vbOKOnly, "No Selection"
  3. ElseIf Combo Is Not Null Then
  4. DoCmd.OutputTo acReport, "rpt1aAuditorinfo", acFormatPDF, , True
  5. End If
  6.  
Hope this helps,

Regards,
Ali
Sep 10 '10 #5
Otter7
18
Ok...I'll give it a try and respond later this evening, California time...off work right now...Thanks and I will respond back...
Sep 10 '10 #6
Otter7
18
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, "rpt1aAuditorinfo", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt1ProjectBldgInfo", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt2Construction", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt3Bldgoccupancy", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt4CoolingPlant", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt5HVACSystem", acFormatPDF, , "Audit_dte_id=" & Audit_Dte_ID, , True
DoCmd.OutputTo acReport, "rpt6ComfortHeating", 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...
Sep 11 '10 #7
liimra
119 100+
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:

Expand|Select|Wrap|Line Numbers
  1. If (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Null")) Then
  2.  MsgBox "No audit has been selected"
  3.  ElseIf (Eval("[Forms]![FormName]![Audit_Dte_ID] Is Not Null")) Then
  4.  On Error Resume Next
  5. DoCmd.OpenReport "FirstReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
  6. DoCmd.OutputTo acOutputReport, "FirstReportName", acFormatPDF, , True
  7. DoCmd.Close
  8. DoCmd.OpenReport "SecondReportName", acViewPreview, , "[Audit_Dte_ID]= " & Forms!FormName!Audit_Dte_ID
  9. DoCmd.OutputTo acOutputReport, "SecondReportName", acFormatPDF, , True
  10. DoCmd.Close
  11. End If
Change names where applicable & add the number of reports you want, and you are done.

Regards,
Ali
Sep 11 '10 #8
Otter7
18
For the "FormName" I enter the name of the form but for [Forms] I do not enter the form name, correct?
Sep 11 '10 #9
liimra
119 100+
Correct

Regards,
Ali
Sep 11 '10 #10
Otter7
18
I'm not following exactly as I enter the form name MainForm which is a switchboard with control buttons to select certain forms to enter data after the user selects an audit from the drop down: there are 37 forms...the Print button works which loads all the reports into a single file to be printed...I thought I could do this with saving as a pdf. Perhaps I could email you my Db through my personal email to your personal email?
Sep 11 '10 #11
liimra
119 100+
I will create an example of what I am talking about and attach it once finished.

Regards,
Ali
Sep 12 '10 #12
liimra
119 100+
Kindly note what I stated before in the attached database.

Regards,
Ali
Attached Files
File Type: zip PDFReport.zip (34.8 KB, 660 views)
Sep 12 '10 #13
Otter7
18
Had a grid power failure...I will send another response tomorrow after reviewing your pdf sample...thank you for your assistance!.
Sep 13 '10 #14
Otter7
18
The sample you submitted worked except, the reports are saving as individula pdf files...I'm trying to save all the reports for the selected record into 1 pdf file....is there an operator that stops the single pdf page from saving and holds it until the next cmd to save a page is added, etc. then the entire report is in 1 saved pdf file?
Sep 13 '10 #15
liimra
119 100+
Glad we achieved something. For saving all into one file,
How about creating one main unbound report with all the reports inside it as sub reports? This will work.

Regards,
Ali
Sep 13 '10 #16
Otter7
18
I will give it a try and reply back tomorrow....Thanks for checking in and assisting!
Sep 13 '10 #17
Otter7
18
Unsuccessful, could not get the unbound report/subreports to work either...VBA must have some type of operator? to cause ongoing pagnation when saving multiple reports to the same pdf....I'll keep trying different variations of all that you submitted...any other coding you can think of? Thank you!
Sep 14 '10 #18
Otter7
18
How do I create an unbound main report with all the existing reports being the subreports plus when clicking on this report opens only 1 record from the If combobox = 0; MsgBox "No Audit has been selected"?

I am still trying to save 1 selected record after selecting through a combox listing, the 23 reports into 1 pdf file...
Sep 18 '10 #19
liimra
119 100+
Create one main report (make it your 1/23 report) and put all the other 22 reports inside it (so they become subreports). You have to note however, that unless you put each report in seperate section, you will get alittle mess with all reports above each others. To overcome this, create groups and set the expression value to =true for each one. For the msgbox, you can use the same as stated before "Is Null" or ="" although they are not exactly the same.

Last but not least make sure you link all the subreports to the main report through "ID".

Please report here if you have any problems and I will search tonight when I get back for a ready example over the net or create it.

Regards,
Ali
Sep 18 '10 #20
liimra
119 100+
Did you get it working?

Regards,
Ali
Sep 18 '10 #21
Otter7
18
Thank you for responding...I was out of town on the weekend. I will follow your instructions today and respond wtih the results this afternoon.
Sep 20 '10 #22
Otter7
18
Still need your assitance...I seem to be missing something as the subreports are not showing as separate sections and not sure how to do a grouping with subreports...would you mind posting a sample..or please post as a private message...Thank you!
Sep 22 '10 #23
liimra
119 100+
Please find in the attached database the example I am talking about. Unfortunately the maximum number of groups is 10 so The main report has 10 reports. Of course you can add two reports to each group and then two reports in the form itself so you can reach 22 reports. Without any doubt there must be a way to add 23 reports but I will be traveling in the next two days so I won't be able to assist further. Anyways, here is how it works:

First you create report based on the table you are getting the records from, then you empty it (you just use it as source so you can create Master-Child Link for the subreports in order to filter). Next, you create groups (Group&Sort --> add group --> expression --> =true). Now you add report to each group (or two) and you are done.

Regards,
Ali
Attached Files
File Type: zip PDFReport2.zip (206.9 KB, 602 views)
Sep 23 '10 #24
Otter7
18
Thank you for the sample...it helps to use a visual...I should be good from here...hope your travel is relaxing!
Sep 23 '10 #25
Otter7
18
Ali, I am still working on this and need assistance. Here is what I am trying to create as you stated - 1 report with groups with subreports totaling 23 reports with 1 record selection and forcing a page break each report:

main report
Group - subrpt 1; subrpt2
Group - subrpt 3, subrpt4
Group - subrpt 4; subrpt 5
etc. to last has 4 subrpts

This is advanced for me...step by step would be helpful, if you have some time....Thank you!
Sep 29 '10 #26

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Sigurd Bruteig | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.