Export one record per PDF (1 Viewer)

[mnevitt]

I have a database that has one table with almost all of my data (it's imported from SharePoint). I am trying to export each record as a single PDF but am having some issues. I found the below code on this site but can't seem to get it to work. Every time I run it, it gives me a PDF names with the ID that includes all of the records. It will then increment the ID and give me a new PDF with all of the records. The ID i'm using is the autonumber ID for the Local_COVID_Tracker table and the table is a local copy of the linked SharePoint table (or list as they call it). I've tried everything I know and can't figure it out. And I apologize, it has been about 20 years since I have had to do anything like this...my day to day job is in law enforcement, not databases or programming. Haha.

Also at some point I would like to make the file names the names of the patients and not the ID. That might be a whole other post since the names in Local_COVID_Tracker are numbers linked to another table with user info.

Thanks in advance - Mike

Public Sub ExportToPDF()

Dim ReportName As String
Dim OutputFolder As String
Dim rs As DAO.Recordset

ReportName = "PrintOutReport"     
OutputFolder = "C:\Users\mrnevitt\test"  

Set rs = CurrentDb.OpenRecordset("SELECT ID FROM Local_COVID_Tracker")
Do While Not rs.EOF
    DoCmd.OpenReport ReportName, acViewReport, "ID =  '" & rs!ID & "'", , acHidden
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, OutputFolder & "\" & rs!ID & ".PDF"
    DoCmd.Close acReport, ReportName
    rs.MoveNext
Loop
rs.Close

End Sub

 

[pbaldy]

I think you have the wherecondition in the wrong position.

 

[pbaldy]

Here's code that works, so you do have the wherecondition in the wrong place:

DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

 

[Uncle Gizmo]

You might find my blog on creating multiple PDFs of interest.

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

 

[mnevitt]

@pbaldy I fixed the location of the where statement and no luck. Now I'm getting a data type mismatch on that same line.

 

[pbaldy]

See here for the syntax adjustment for a numeric field:

Open a second form to the record

 

[mnevitt]

So I was able to get it to work. I created a query to populate my report instead of just using the table after realizing/remembering my table was linked to another. Below is what ended up working. Now I need to build it into a form that lets you select the save location and if you want to print all, a range or a single report....back to re-learning VBA/Access. Thanks for the help.

Public Sub ExportToPDF()

Dim ReportName As String
Dim OutputFolder As String
Dim rs As DAO.Recordset

ReportName = "PrintOutReport" 
OutputFolder = "C:\Users\mrnevitt\Print out" 

Set rs = CurrentDb.OpenRecordset("SELECT * FROM PDFReportQuery_SharePoint")
Do While Not rs.EOF
    DoCmd.OpenReport ReportName, acViewReport, , "[Patient #] =  " & rs![Patient #] & "", acHidden
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, OutputFolder & "\" & rs![Patient #] & "_" & rs![Name] & ".PDF"
    DoCmd.Close acReport, ReportName
    rs.MoveNext
Loop
rs.Close

End Sub

 

[pbaldy]

You can use this to have the user pick a folder:

Application.FileDialog property (Access)

Office VBA reference topic

docs.microsoft.com

You could populate a multiselect listbox with your list of patients and let the user pick which to export. Here's the basic loop of the listbox:

Multi-Select Listbox

but instead of building a string you'd open/output the report.

 

[mnevitt]

Awesome. Thanks Paul. I will take a look at these and see what I can come up with.

 

[pbaldy]

No problem Mike, post back if you get stuck.