I merged the example from the link in my post #4 with the code provided by kd2017, then created a table vaguely like what I hope is your table design. I added 3 names and a total of 8 records.
Running the following code I had 3 separate PDF files.
Code:
'=======================================
' Author: kd2017
' accessforums.net
' 3 Feb,2021
' Modified: ssanfu 4 Feb 2021
'=======================================
Private Sub Command331_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim my_report As String
Dim criteria As String
' mypath = "C:\Forum\" 'path for my testing
mypath = "C:\Users\#####\OneDrive - ConstructMobile\Documents\FAW Small Asset Tracker\WASP REPORT CONNECTOR\Holder Reports\Asset Report\"
my_report = "Assets By Holder"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Distinct [Holder] FROM [Assets Extended]", dbOpenSnapshot)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
' "FAW Small Assets Inventory Report, [Holder], Today()"
MyFileName = mypath & "FAW Small Assets Inventory Report-" & rs!Holder & "-" & Format(Date, " mmm d yyyy") & ".PDF"
' Debug.Print MyFileName
criteria = "[Holder]='" & rs!Holder & "'"
DoCmd.OpenReport my_report, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acOutputReport, my_report, acFormatPDF, MyFileName, , , , acExportQualityPrint
DoCmd.Close acReport, my_report
DoEvents
rs.MoveNext
Loop
End If
ExitHandler:
On Error Resume Next
DoCmd.Close acReport, my_report
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Done"
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub
These are the names of the PDF files that were generated:
Test-steve- Feb 4 2021.PDF
Test-rob- Feb 4 2021.PDF
Test-sam- Feb 4 2021.PDF