question

kcamp17-8852 avatar image
0 Votes"
kcamp17-8852 asked thedbguy answered

Access Report to Multiple PDFs based on Field Data Changes

I'm trying to automate a PDF report in Access so that it splits itself in to multiple pdf files based on one data field. I've never used VBA and extremely new to Access, so this has been fun!

Important Info
- Database: 2021_S3_DS
- Field Name: Teacher Short
- Report Name: 2021_S3

Basically, I want it to create a new PDF report each time the Teacher Short field changes. Here's the code I have so far based off of another thread I've found:

 Private Sub cmd_GenPDFs_Click()
     Dim rs                    As DAO.Recordset
     Dim sFolder               As String
     Dim sFile                 As String
    
     On Error GoTo Error_Handler
    
     sFolder = Application.CurrentProject.Path & "\"
    
     Set rs = CurrentDb.OpenRecordset("2021_S3_DS", dbOpenSnapshot)
     With rs
         .MoveFirst
         Do While Not .EOF
             DoCmd.OpenReport "2021_S3", acViewPreview, "[Teacher Short]" & ![Teacher Short], acHidden
             sFile = Nz(![Teacher Short], "") & ".pdf"
             sFile = sFolder & sFile
             DoCmd.OutputTo acOutputReport, "2021_S3", acFormatPDF, sFile, , , , acExportQualityPrint
             'If you wanted to create an e-mail and include an individual report, you would do so now
             DoCmd.Close acReport, "2021_S3"
             .MoveNext
         Loop
     End With
    
     Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files
    
 Error_Handler_Exit:
     On Error Resume Next
     If Not rs Is Nothing Then
         rs.Close
         Set rs = Nothing
     End If
     Exit Sub
    
 Error_Handler:
     If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!
         MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                "Error Number: " & Err.Number & vbCrLf & _
                "Error Source: cmd_GenPDFs_Click" & vbCrLf & _
                "Error Description: " & Err.Description & _
                Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                , vbOKOnly + vbCritical, "An Error has Occured!"
     End If
     Resume Error_Handler_Exit
 End Sub

Since I know nothing about VBA, I'm not sure about a lot of the syntax and have just been plugging in names where I thought they should go. So far, this runs, but it seems like it's going to be an infinite loop since I got up to over 1300 pages in one file when they should probably max out around 10-15.

Any hand-holding would be appreciated! I have experience with JS and Python, but no VBA.




office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. It might go quicker if you could share a sample copy of your db with us.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.