CODEWITHSUNDEEP
ms-accesspdfprintingreport
Fabrizio Di Mattei
Fabrizio Di Mattei

Reputation: 11

(VBA) create a file "pdf" for each record in a table

Help me, this code I wrote, creates multiples files but every file contains only the values from the first record: I want a file pdf for each record in the table.

Option Compare Database 
Option Explicit

Private Declare PtrSafe Sub 
Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Private Sub Creazione_Click()
Dim rs As DAO.Recordset
Dim rpt As Access.Report
Dim sFolder As String
Dim sFile As String
Const sReportName = "Mandato"
sFolder = "C:\Users\Famiglia\Desktop\Mandati\"

Set rs = Me.RecordsetClone
With rs
If .RecordCount <> 0 Then
'Open the Report
DoCmd.OpenReport sReportName, acViewPreview, , , acHidden
'Define a report object so we can manipulate it below
Set rpt = Reports(sReportName).Report
.MoveFirst
Do While Not .EOF
'Build the PDF filename we are going to use to save the PDF with
sFile = Nz(![id], "") & " " & Nz(![COGNOME], "") & ".pdf"
sFile = sFolder & sFile
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint
Call Sleep(1000)
DoEvents
.MoveNext
Loop
DoCmd.Close acReport, sReportName
End If
End With
End Sub

Upvotes: 1

Views: 117

Answers (1)

June7
June7

Reputation: 21370

Open, output, close report filtered to record within loop.

Do While Not .EOF
    DoCmd.OpenReport sReportName, acViewPreview, , "ID=" & rs!ID, acHidden
    ...
    DoCmd.Close acReport, sReportName
    .MoveNext
Loop

A report object variable is not necessary. Your code doesn't even use the variable.

Upvotes: 2

Related Questions