Export each page of the report to a seperate PDF file

[naeemahmad]

Hi Experts,

I got another question can seem to figure out.. I need to have the script produce a seperate .pdf file to each page within the report. These are not seperate report, but rather the same report with many pages.

example: a report with 5 pages, will produce 5 seperate pdf files.

Below the the code I am currently working with (currently on a command button for testing) and need help to modify to include the above. any help is much appericiated!

Private Sub Command42_Click()

Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
MyFilter = ""
MyPath = "Y:\signal\SignalsDB\W\NA_PDF_TEST\"
MyFilename = "NA_PDF_TEST.pdf"
DoCmd.OpenReport "rptMAINT_LOG", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False


DoCmd.Close acReport, "NA_PDF_TEST"

'Clean up!
Set fs = Nothing
End Sub


Thanks guys!

Naeem~

[June7]

Will there always be 5 pages? Why are there multiple pages and why do they need to be individual PDFs?

Options:

1. Set destination printer as AdobePDF, if you have the driver for it, and use PrintOut method to print specified pages. The AdobePDF printer dialog should open so can enter file name. I don't know if this part can be automated.
For i = 1 to 5
DoCmd.PrintOut acPages, i, i
Next

2. Print all 5 pages to a single multi-page PDF and use code to break up the PDF.

[naeemahmad]

Hi June7

Will there always be 5 pages?
No. the number pages will vary due to the number of field sites a field technician can visit in one day. for every site a technician visits a record will be stored in a table. each site has it own unique ID

Why are there multiple pages and why do they need to be individual PDFs?
I built one report which get its data from a query for a specific technician, so if that technician visits many sites, they will have many records (or pages), therefore generating many pages to the 1 report.

The generation of the report (or pdf) does not occur until the field technician has synchronized with the back-end, One thought just came to mind is generating a report (pdf) as soon as he saves the record, and then push all the pdfs in their proper location for linking on the back-end. I would like to avoid this kind of data storage on the field laptops.

Synopsis:
When the technician returns to the office, they will synchronize the collected field data into the back-end for office use. Since each record is a different location, I will be linking the respective .pdf with the correct records via unique IDs. The only reason I was thinking of pdf, is for security so that document may not be altered once generated.

There may be an alternate approach to this, which I am open to, but I am trying to build on what I have already completed.

Can I use your suggested For/Next loop with the .output acFormatPDF property?

Options:

1. Set destination printer as AdobePDF, if you have the driver for it, and use PrintOut method to print specified pages. The AdobePDF printer dialog should open so can enter file name. I don't know if this part can be automated.
For i = 1 to 5
DoCmd.PrintOut acPages, i, i
Next

2. Print all 5 pages to a single multi-page PDF and use code to break up the PDF.
This option will not work for me since the one multi-page PDF will include different site for different records unique IDs

I hope im describing it coherently..lol. sometimes, I over-complicate my own thinking


Any thoughts are greatly appreciated!

Naeem~

[June7]

OutputTo does not allow for specifying page numbers, only PrintOut has that.

Another approach is to open a recordset of the technician and their sites, loop through the recordset, open report filtered for the technician and the site, OutputTo PDF, close report, move to next site record, repeat.

[naeemahmad]

OK. You have giving me ideas.. I'm thinking I may use the .printout method to loop through the pages and as you stated earlier, just print out to a pdf file. let me write up a script and show you what I come up with.

Ultimately, I will automate the sending of each pdf to a specific folder location, so the office staff can view each (pdf) report related with its specific generated field records.

Thanks a bunch for the guidance.. I will keep this thread posted with the final script.

Naeem~

[June7]

Problem with the looping PrintOut method is you don't know how many pages the report will have.

[naeemahmad]

How about if I incorporate a 'Do until' loop into the script with the printout method along with a SET sql'd rs? Im just thinking outside the box... theres always some way to touch something within the system..lol.. or am i just CrAzY.. 8^/

set REPORTrst = SQL STATEMENT HERE

With rstForm
.MoveLast
.MoveFirst

Do Until .EOF

[June7]

I don't know if you will have any success printing to AdobePDF with PrintOut. I am not certain what is required as far as printer driver.

If you want to use looping recordset then I recommend it incorporate opening filtered report and using OutputTo method.

[naeemahmad]

Understood. if .pdf may be an issue, I can even just generate word document word doc or possibly a word doc with 'form-fields' to populate the data needed for staff. the documents dont have to be .pdf just a preference to maintain some sort of security..

[June7]

Not sure what you mean by security. If you backup the db isn't that as good as output to Word or PDF? The report should be reproducible whenever needed as long as the data is in db.

[naeemahmad]

Not sure what you mean by security. If you backup the db isn't that as good as output to Word or PDF? The report should be reproducible whenever needed as long as the data is in db.

you are correct that the database will maintain its data integrity, but the document itself may be altered outside the database if not in a 'secured' format.

Once a PDF is generated, it is stored in a folder that is accessible to anyone on the network, if someone happens to navigate to its location, which is not hidden, I dont want unauthorized staff to have the ability to take the document and alter it.

These documents are printed and emailed to various department and travel through engineering, billing, other gov city agencies, etc. and need to be secure.

I should have something scripted up today... hopefully.. lol. Thanks again!

Regards,
Naeem~

[ItsMe]

I should have something scripted up today... hopefully.. lol. Thanks again!

Regards,
Naeem~

I would like to see an example or at least the technique to set the security settings in a PDF file.

[naeemahmad]

Quick Update:

Sorry guys, have been on another project. Will be working on this thread subject today.. will keep the thread posted.

[naeemahmad]

Ok .. so this is how I approached the issue.. it seems to be working as intended at least for my function... hope this helps with some insight for anyone looking to attempt to achieve a similar issue..

I know I dont 'wrap' my code.. it easier to annotate the script to help highlight without them.. plus, im kind of an 'ol school'er..

'---------------------------------------------
Private Sub Command17_Click()
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
Dim CUser As String
Dim LNumofRecs As Long 'FOR THE NUMBER OF RECS IN THE QRY 12_3_13NA HOME
Dim IDNum As String
CUser = Me.Text12
Udate = Me.Combo2
'---------------------SETS UP PATHS FOR PDF OUTPUT -------------------------
MyFilter = ""
MyPath = "C:\Users\Ahmad\Desktop\signal_db_rs\PDF\" <-------------- test path
'--------------OPEN REPORT - ------------------------------------
DoCmd.OpenReport "rptMAINT_LOG", acViewPreview
'------------------------------- SETUPS UP RECORDSET ---------------
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("081 qryMAIN_LOG_RPT")
qd.Parameters("[Forms]![frmTIMESHEET_REPORTS]![Combo2]") = #12/3/2013#
qd.Parameters("[Forms]![frmTIMESHEET_REPORTS]![Text12]") = "nahmad"
Set rs = qd.OpenRecordset
If rs.RecordCount = 0 Then MsgBox ("No Recs")
'COUNT NUMBER OF REC (PAGES) IN QUERY
LNumofRecs = DCount("LOG_DATE", "081 qryMAIN_LOG_RPT", "USER_DATETIME=#" & Udate & "#")

While Not rs.EOF
For i = 1 To LNumofRecs 'iterate through the number of pages in the table - 12_3_13 na

IDNum = rs(22) <-------- Column 22 is the desired index key I needed to build the relationship to link to the back-end
MyFilename = IDNum & ".pdf"
DoCmd.PrintOut acPages, i, i <-------- SUBSTITUTE THIS LINE WITH DESIRED PRINT DRIVER AS NEEDED. (I chose PDF print drivers)
rs.MoveNext
Next
Wend
Set rs = Nothing
End Sub
'-------------------------------------------

it is now working as intended.. just have to do some minor adjustments and done..a little tricky but works for my needs..

Thank all for your guidance and patience..

Regards,
Naeem~
App Dev - County of Riverside, CA USA

[ItsMe]

Does using the PDF printer driver create secure PDF files?