Access Report to Multiple PDFs based on Field Data Changes

Question

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.

Answer 1

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