code to create a PDF bookmark when printing a report to PDF

[davefwelch]

I've done several Google searches on this topic, this morning, and have not found much useful that didn't cost hundreds of dollars. And I'm quite sure that my program is not going to fork over the money for a costly solution that will only save me an hour or so.

I have an Access application that I use every spring to generate a 3-4 page report (fillable PDF) for 378 clients that compiles lots of their data from a database that my program maintains. This being my second year using the application, I have been able to add a few bells and whistles that save me lots of time. This last whistle is proving to be my stumbling block, and I'd REALLY like to figure it out because I have another project just around the corner for which I'd like to apply this solution! Here are my steps:

1) Print entire 1,176 page report to a PDF.
2) Open the PDF in Adobe Acrobat 8 Professional, and methodically insert a bookmark at the beginning of each 3-4 page report, 378 times. (This is the step I'd LOVE TO eliminate, because my next project has me doing this bookmark insert over 2,000 times!)
3) Once the bookmark insertion is done, I run Form Field Recognition, which places a fillable field at every blank line in the PDF (ranges from 10 to 20+ blanks per report).
4) Next, I save the PDF by Enabling Usage Rights in Adobe Acrobat. My assumption is that this allows the user to save the form with their filled-in fields.
5) My final step is to use a piece of freeware I found last week called PDF Split And Merge (pdfsam) that runs through the PDF and splits out the 3-4 page reports into separate PDFs, nicely naming each one after the bookmark, each of which is the unique identifier for each of my clients.

Now, I'm no dummy when it comes to VB, although I would not classify myself as a pro! But any help would be greatly appreciated!

 

[pbaldy]

I'd adapt this and the linked looping code to create individual PDF's for each client:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[davefwelch]

Thanks for the response, Paul. But surely the time required to open each of the 378 resulting PDFs to run form field recognition would be greater than the time I'm currently spending creating the 378 bookmarks in the one PDF.

A year ago when I was developing this process the first time around, I had considered many options, and this was one of them. But I found that it was nearly impossible to have Access creating the fillable fields as it was generating the PDFs . . .

Actually, now that I run another Google search, I find this post that I made to this forum about a year ago! LOL

 

[pbaldy]

You could be right. I've never really used Adobe itself, so that was an unknown to me. Is this code any use?

http://answers.microsoft.com/en-us/...pdf-will/7f8d45a8-503a-476a-b43d-c4aa4cbd5e67

 

[davefwelch]

Thanks again, Paul.

That code looks like it might be useful, but I can't find the point in any of the code where the function AddBookmarks is called.

 

[pbaldy]

Bob is also active here, so I'll PM him and see if he can help.

 

[spikepl]

Foxit also sell some DLL /Active X SDK -google their site. Presumably at much better price then Adobe.

(I have kicked Adobe Reader ages ago because of bloat, incessant failing updates etc., and since that time happily use Foxit Reader)

 

[davefwelch]

Thanks Paul. I envisioned that some code would go into the Detail's OnFormat event, but now I' not sure . . .

 

[davefwelch]

So after a little more research, and deciding to shift gears a tad, I found this page that talks about using Adobe Acrobat 8 to insert bookmarks based on the xml structure in the PDF. So, this begs the question:

How to I get my Access Report, when printed to a PDF, to contain any xml structure, because it doesn't have any by default

 

[Pat Hartman]

If the bookmarks are only used to split the PDF into individual files, you don't need them if you use Access to create the individual PDF files.
What are the fillable fields used for? You may be able to get Access to automate that part after it creates each file. You just need to figure out the correct Adobe API to call.

 

[davefwelch]

Thanks Pat.

That's exactly the purpose of the bookmarks, and I considered the solution to which you refer, but determined that implementing it was slightly over my head

 

[Pat Hartman]

The code to create individual files is a lot easier than the code to open the pdf and add bookmarks.

Public Sub TestExport()
    Dim db As DAO.Database
    Dim rs As DAO.Database
    Dim qd As DAO.QueryDef
    Dim FileCount As Integer
    Dim strFileName As String
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs!yourqueryname
    Set rs = qd.OpenRecordset
    FileCount = 0
    Do Until rs.EOF = True
        strFileName = "hardcodedpartofname" & rs!somethingunique & ".pdf"
        DoCmd.OutputTo acOutputReport, "yourreportname", acFormatPDF, strFileName
        FileCount = FileCount + 1
        rs.MoveNext
    Loop
    rs.Close
    MsgBox FileCount & " files printed.", vbOKOnly
End Sub

 

[davefwelch]

Oh My Goodness!!!

I just realized that I was making this more difficult than necessary.

I was thinking that I would apply the solution to two different annual projects, one I just completed and the second I'm undertaking today. But the SECOND project has no need for fillable fields, so for that project at least, your solution Pat will be perfect! And THAT was the project that's going to have about 2,000 separate PDFs! The first one has under 400, definitely managable to hand insert the bookmarks

Thanks so much!!!

 

[davefwelch]

Now that I've had time to try to put the solution to work, I've encountered a stumbling block. Here's my code currently.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim FileCount As Integer
    Dim strFileName As String
    Dim strRptName As String
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs!RequiredTestsReportCpdf_qry
    Set rs = qd.OpenRecordset
    strRptName = "RequiredTestSheetsPDF_rpt"
    FileCount = 0
    Do Until rs.EOF = True
        strFileName = rs!PWSID & "RTS.pdf"
        DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, strFileName
        FileCount = FileCount + 1
        rs.MoveNext
    Loop
    rs.Close
    MsgBox FileCount & " files printed.", vbOKOnly

So the problem now is that I don't see anywhere in the DoCmd.OutputTo call where I can specify the query/report filter, because the report has many pages, but I only want one page to print for each iteration through the Do Loop.

I find this code from a previous post on my thread that says "you can not pass a filter or where clause as you would in a standard OpenReport command in VBA when outputting the report to a file." Rather, "you must change the filter or where clause yourself in the reports OnOpen event." I think I need a few pointers to implement that

 

[pbaldy]

That code sets a filter in the report's open event:

Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]

so in your loop you would have something along the lines of:

Forms![Select Load List]![LoadID] = rs!FieldName

to put the current value from the recordset into that form control where the report code can get it.

 

[davefwelch]

Ahhhhh, that makes perfect sense, thanks!

PS. John Galt Rocks!!!

 

[pbaldy]

Happy to help!

I think we need John now.

 

[davefwelch]

I'm still wrestling with implementing your final suggestion, Paul. First, I tried to implement exactly what you suggested. I entered the following code into the OnOpen event of the Report:

Me.Filter = "PWSID = '" & Forms![RequiredTestSheets_frm]![txtPWSID] & "'"

Yes, PWSID is the name of a field in the underlying query for this report (with the name of it's textbox on the report being txtPWSID). Of course, as a test prior to running the PDF creation loop, I try opening the report when I have a PWSID in that form textbox that I know represents one of the records in the query. And when the report opens, it's still showing me all the records in that query. The filtering does not take place.

So then, I tried modifying the code of the PDF creation loop like this:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim FileCount As Integer
    Dim strFileName As String
    Dim strRptName As String
    
    Set db = CurrentDb()
    Set qd = db.QueryDefs!RequiredTestsReportCpdf_qry
    qd![Forms!RequiredTestSheets_frm!txtPWSID] = Forms![RequiredTestSheets_frm]![txtPWSID]
    Set rs = qd.OpenRecordset
    strRptName = "RequiredTestSheetsPDF_rpt"
    FileCount = 0
    Do Until rs.EOF = True
        Forms!RequiredTestSheets_frm!txtPWSID = rs!PWSID
        strFileName = "D:\" & rs!PWSID & "RTS.pdf"
        DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, strFileName
        FileCount = FileCount + 1
        rs.MoveNext
    Loop
    rs.Close
    MsgBox FileCount & " files printed.", vbOKOnly

Where I basically tried changing the report's underlying query to a parameter query, which required me to add this line:

qd![Forms!RequiredTestSheets_frm!txtPWSID] = Forms![RequiredTestSheets_frm]![txtPWSID]

to eliminate the "Too few parameters, Expected 1" error that occurs because VBA does not know how to look at form fields before it runs a query.

I was hopeful with this solution, but alas, it's complicating the issue too much, because now the recordset only ever contains one record, so how am I supposed to conduct the loop? It's seeming like I should go back to your suggestion, but I guess I don't know why it'snot working . . .

 

[davefwelch]

I think I was just able to implement your suggestion, Paul.

I decided that rather than setting the Report filter in the OnOpen event, I would set the RecordSource, and include in it a WHERE clause. I think it might have worked!!! More to come . . .

 

[pbaldy]

Did you include the other line from the link?

Me.FilterOn = True

As to filtering the query, it doesn't sound like you want that in there for this.