Export Report to Single PDF Pages

[02kev02]

Hi,

I'm very new to access and VBA.
I'm hoping someone has a solution or can provide a little bit of guidance.

I would like to be able to export my reports so that each record is exported to a seperate PDF file and titled by the unique identifier for example.
In Access, I only have the option of exporting ranges of pages, or the whole report combined.


However, this makes it difficult when there are over 500 records that need to be seperated.
Is there some sort of script that can automize this process?

Thanks to anyone who can help!!

Kevin

[June7]

My approach would be a VBA procedure that opens a recordset of the unique identifier and loops through the recordset and output PDF of report filtered by the identifier from the recordset. I recently did something quite similar to output PDFs of about 70,000 reports from legacy database. An extract of code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "SELECT LabNum FROM Submit;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
      DoCmd.OpenReport "LabReport", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
      DoCmd.OutputTo acOutputReport, "", acFormatPDF, "drive:\path\" & rs!LABNUM & ".pdf", True
      DoCmd.Close acReport, "LabReport", acSaveNo
      'kill Adobe Acrobat so I don't have thousands of open pdf documents
      Dim objWMIService As Object, objProcList As Object, objProcess As Object
      Set objWMIService = GetObject("winmgmts:")
      Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
      For Each objProcess In objProcList
           objProcess.Terminate (0)
      Next
      rs.MoveNext
Wend

I couldn't actually output all 70,000 reports in one batch run. Had to do it in groups of about 5,000, otherwise Access would crash.

EDIT: I have since discovered the 'kill' code is not need if the True parameter of OutputTo is changed to False. The 6 lines of code following the 'kill' comment are then not needed.

[02kev02]

Thank you so much for your quick reply!!
I will have to dive into VBA and try to implement this