How to Publish and Save each Record Report in Separate File? Access VBA Code Below

[ekryez]

Trying to make this code work, don't know how to filter as it prints identical all reports.
Private Sub cmdExportPDF_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyPath As String
MyPath = "C:\Reports"

If Len(Dir(MyPath, vbDirectory)) < 1 Then
MkDir (MyPath)
End If

Dim sProjectNumb As String
Dim sProjectName As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT ProjectNumb, ProjectName FROM Master", dbOpenSnapshot)

If rs.RecordCount = 0 Then Exit Sub
Do While Not rs.EOF
sProjectNumb = rs![ProjectNumb]
sProjectName = rs![ProjectName]
MyFileName = sProjectNumb & " " & sProjectName & ".PDF"
DoCmd.OpenReport "Total Report", acViewDesign, , "[ProjectNumb]=" & temp
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFileName
DoCmd.Close acReport, "Total Report"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[June7]

Where is this code - in button Click event? What is temp - a field on form?

[ekryez]

Please see attached
db1.zip

[ItsMe]

So you do not have any idea what temp is?
"[ProjectNumb]=" & temp

[ekryez]

So you do not have any idea what temp is?
"[ProjectNumb]=" & temp

That was a mistake.

Private Sub PrintAll_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyPath As String
Dim MyFileName As String
Dim MyProjectNumber As String
Dim MyProjectName As String
MyPath = "C:\Reports\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT ProjectNumber,ProjectName FROM Master", dbOpenDynaset)
With rs
Do While Not .EOF
MyProjectNumber = !ProjectNumber
MyProjectName = !ProjectName
MyFileName = MyProjectNumber & " " & MyProjectName & ".PDF"
'DoCmd.OpenReport "Total Report" 'You don't HAVE to open the report
DoCmd.OutputTo acOutputReport, "Total Report", acFormatPDF, MyPath & MyFileName
'DoCmd.Close acReport, "Total Report"
.MoveNext
Loop
End With
Set rs = Nothing
Set db = Nothing
End Sub

[June7]

You do have to open the report if you want to apply filter criteria using the WHERE CONDITION of OpenReport method.

DoCmd.OpenReport "Total Report", acViewPreview, , "ProjectNumber='" & !ProjectNumber & "'"

[ekryez]

You do have to open the report if you want to apply filter criteria using the WHERE CONDITION of OpenReport method.

DoCmd.OpenReport "Total Report", acViewPreview, , "ProjectNumber='" & !ProjectNumber & "'"

Already done that

[June7]

Then what is the issue? I tested your procedure after editing that line and enabling the two commented lines and it works.

[ekryez]

I'm trying to print all records separately in one shot, instead of printing one by one, if you go on C:\Reports(my specified path) you see that when print all inside instead of only one record are all of them

[June7]

I don't understand. If you want a separate PDF for each ProjectNumber then you must open the report filtered to ProjectNumber, output report, close report, repeat for next. This is exactly what happens with the Print All button. The Print Project button creates PDF for the current record on form.

[ekryez]

If you want a separate PDF for each ProjectNumber then you must open the report filtered to ProjectNumber, output report, close report, repeat for next..

Can you please show me how to do this? Automatically?

[June7]

You already have the code doing that.

[ekryez]

Did you get the DB?

[June7]

I downloaded your db, fixed the code as described, and it works.

[ekryez]

where did you uploaded please?