CODEWITHSUNDEEP
ms-access-2016
CodingIsTough
CodingIsTough

Reputation: 1

Export Multiple named PDF's from MS Access

Code:

Private Sub Command455_Click()
Dim filename As String
Dim filepath As String

filename = Me.LAST_NAME & "," & " " & Me.FIRST_NAME & "," & " " & Me.STUDENT_ID
filepath = "C:\Users\ddennis1\Desktop\" & filename & ".pdf"
DoCmd.OpenReport "Humanities_MiniDips", acViewPreview, , "[Student_ID]=Forms!DIPLOMAS_EXPRESS!STUDENT_ID"
DoCmd.OutputTo acOutputReport, "Humanities_MiniDips", acFormatPDF, filepath
MsgBox "Graduate exported", vbInformation, "Save confirmed"

End sub

The above coding works great, but only exports one PDf at a time base on the student ID displayed on the form.

I would like to export multiple named PDF's from the database based on the above coding with another command button.

The report is called "Humanities_Minidips" and the record source is called "Humanites"

I reckon there is some sort of looping mechanism required to accomplish this task.

Anyone has any ideas on how to move forward on this?

Upvotes: 0

Views: 607

Answers (1)

BankBuilder
BankBuilder

Reputation: 521

You are correct that you need a loop to implement the behavior you need. You also need a table containing student names and IDs that you can query to provide a list to loop through.

The code below wraps most of your code in a simple loop that takes the records in a table named "STUDENTS" and outputs a PDF for each student.

' change NewButton to the name of your new button for printing all students
Private Sub NewButton_Click()
    Dim filename As String
    Dim filepath As String
    Dim db as Dao.Database
    Set db = CurrentDb
    Dim rs as Dao.Recordset
    ' change the name of the table to match your student table           vvvvvvvv
    Set rs = db.OpenRecordset("SELECT FIRST_NAME, LAST_NAME, STUDENT_ID FROM STUDENTS", dbOpenSnapshot)

    Do Until rs.EOF
        ' reference the desired field names in the current row
        filename = rs("LAST_NAME") & "," & " " & rs("FIRST_NAME") & "," & " " & rs("STUDENT_ID")
        filepath = "C:\Users\ddennis1\Desktop\" & filename & ".pdf"
        DoCmd.OpenReport "Humanities_MiniDips", acViewPreview, , " 
        [Student_ID]=" & rs("STUDENT_ID")
        DoCmd.OutputTo acOutputReport, "Humanities_MiniDips", acFormatPDF, filepath
        DoCmd.CloseReport acReport, "Humanities_MiniDips", acSaveNo
        ' This line logs the export for each student, this will help you with 
        ' troubleshooting for specific students if necessary. The log can be found
        ' by pressing Ctrl + G while in the VBA editor in Access.
        Debug.Print "PDF for " & rs("FIRST_NAME") & " " & rs("LAST_NAME") & " exported"
        ' move to the next record, CRITICAL or else you will loop forever
        rs.MoveNext
    Loop
    ' indicate that export is finished
    MsgBox "Graduates exported", vbInformation, "Save confirmed"
End sub

Upvotes: 2

Related Questions