Exporting each site from a report, as a seperate PDF

[UZI_Berlin]

Hello,

i hope this thread does not appear multiple times, but everytime I push the button it disappears.
I have a data base where i prepare donation confirmation
I use access to prepare these.
I use the date of signature (Unterzeichner_Datum) to create the report.
sometimes it is more than one page.
I would like to be able to export them with a specific name (Date of Signature and donator: 202506024_ABC GmbH.pdf)


I have a database with the following data table (Spenden)


I use the following query to create my report:

SELECT Spenden.Spender, Spenden.Adresse, Spenden.Hausnummer, Spenden.PLZ, Spenden.Stadt, Spenden.Spende_Datum, Spenden.Betrag_EUR, Spenden.Betrag_Wort, Spenden.Unterzeichner_Datum, Spenden.Unterzeichner, Spenden.Unterzeichner_Position
FROM Spenden
WHERE (((Spenden.Unterzeichner_Datum)=[Ente Date of Signature]) AND ((Spenden.Art)='Geldspende'));

and this is the vba I came up with so far with youtube-channel etc.
I am not really an expert on vba.



Sub PDF_Click()
Dim strReportName As String
Dim strPath As String
Dim i As Integer
Dim lngPageCount As Long


' Name of the Report
strReportName = "Geldzuwendungen"


' Path, files shall be saved to
strPath = "C:\Users\Downloads"


' Check amount of pages
lngPageCount = Reports(strReportName).Pages


' Loop for each page
For i = 1 To lngPageCount
' Generate Name (z.B. Bericht_Seite1.pdf, Bericht_Seite2.pdf, etc.)
Dim strFileName As String
strFileName = Unterzeichner_datum & spender & ".pdf"


' open report if not already open
DoCmd.OpenReport strReportName, acViewNormal


' set report to current site
DoCmd.GoToRecord acReport, strReportName, acGoTo, i


' export to PDF
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName, False


' close report


DoCmd.Close acReport, strReportName, acSaveNo


Next i


MsgBox "All pages have been exported"
End Sub

when I use this, the documents get all saved into one pdf and it is not automaticially saved to the folder named.
where did I go wrong.




thank you

[Welshgasman]

You need to add criteria to the open report statement.

Start using Debug.Print statements or walk your code to see what you actually have, and not what you think you have.

Private Sub cmdDate_Click()
On Error GoTo Err_cmdDate_Click


    Dim stRptName As String, stParam As String, stLinkCriteria As String, stDateRev As String, stDBpath As String, stFTPpath As String
    Dim iPreview As Integer, iDialog As Integer, iIssue As Integer
    iPreview = 0
    iDialog = 0
    stRptName = "Main_By_Date"
'    If Me.cboDate.Column(1) > 43 Then
'        stRptName = "rptMain_By_Date"
'    End If


    
    stDBpath = CurrentProject.Path & "\"
    stFTPpath = stDBpath & "Gazette\"
    
    iPreview = acViewPreview
    iDialog = 0
    If Me.ChkPreview Then
       ' iPreview = 2
        iDialog = acWindowNormal
    Else
        iDialog = acHidden
    End If
    
    stParam = Format(Me.cboDate, "ddmmyyyy")
    'stDateRev = "#" & Mid(stParam, 3, 2) & "/" & Mid(stParam, 1, 2) & "/" & Right(stParam, 4) & "#"
    stDateRev = Format(Me.cboDate, strcJetDate)
    stLinkCriteria = "[On_Date] = " & stDateRev
    'Now need to pass in issue number as we need to change report for crew after issue 43
    iIssue = Me.cboDate.Column(1)
    
'    stLinkCriteria = "[On_Date] = #05/01/1967#"
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog, iIssue
    If Not Me.ChkPreview Then
        DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
        DoCmd.Close acReport, stRptName
    End If
    'DoCmd.DeleteObject acReport, stParam


Exit_cmdDate_Click:
    Exit Sub


Err_cmdDate_Click:
    MsgBox Err.Description
    Resume Exit_cmdDate_Click
End Sub

[UZI_Berlin]

Sorry, I cannot follow your post.
I tried to copy your code in my database, but it did not work.
what did you exactly mean by the "what you actually have, ...."
I am sorry I am an absolute beginner with VBA.

where do you see the mistakes in my vba code?

[Welshgasman]

You cannot just copy my code.
That is just for my requirements.
What I was trying to show was the logic.

When I want to do what you want to do, I pass the criteria to the report. The criteria changes with each record that is in the recordset that I am using.
You need to start learning VBA. Non VBA Access is not that useful.

Look at some videos to get the picture

What you actually have means see what the values are for your variables/fields.
So inspect strFileName and the like and see what they actually contain. If strFilename contains folders that do not yet exist, you have to create them. It could be as simple as a missing "", which happens a lot.

[davegri]

See if you can work with this:

UZI_B-PDFGen-davegri-v01.zip