How to Publish and Save each Record Report in Separate File?

[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

 

[pbaldy]

Try opening in Preview instead of Design.

 

[ekryez]

Dear Paul you jocking or testing me? that wont do anything

 

[pbaldy]

You didn't try it? I doubt it filters in design view. It doesn't look like you set temp either.

 

[ekryez]

You didn't try it? I doubt it filters in design view. It doesn't look like you set temp either.

same results

 

[ekryez]

You didn't try it? I doubt it filters in design view. It doesn't look like you set temp either.

Hi Paul, What I'm trying to do is print all the reports in separate files, at C:\Reports.
Print each record with Project Numb + Project Name Done.
Problem instead of printing only one record inside the report, is printing all of them.

 

[pbaldy]

What is the code now? Are you setting temp?

 

[ekryez]

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"
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

 

[pbaldy]

Now you're printing, not previewing, and no longer including the wherecondition. Try

DoCmd.OpenReport "Total Report", acViewPreview, , "[ProjectNumb]=" & MyProjectNumber

 

[ekryez]

Hi, Paul thnx I have already done that, how can I send you the db?

 

[pbaldy]

No code posted combined those. You should be able yo attach the db here, after a compact/repair and zipping.

 

[ekryez]

Please see attached

 

[ekryez]

No code posted combined those. You should be able yo attach the db here, after a compact/repair and zipping.

Please see attached

 

[pbaldy]

Odd that you didn't get the errors I got since your field is text, not number, and the field name is spelled wrong. This appears to work:

DoCmd.OpenReport "Total Report", acViewPreview, , "[ProjectNumber]='" & MyProjectNumber & "'"

Your commented line:

'DoCmd.OpenReport "Total Report" 'You don't HAVE to open the report

is not accurate, unless you use some other method to filter the report.

 

[ekryez]

Hi Paul,
How to filter the Report please?

 

[ekryez]

Odd that you didn't get the errors I got since your field is text, not number, and the field name is spelled wrong. This appears to work:

DoCmd.OpenReport "Total Report", acViewPreview, , "[ProjectNumber]='" & MyProjectNumber & "'"

Your commented line:

'DoCmd.OpenReport "Total Report" 'You don't HAVE to open the report

is not accurate, unless you use some other method to filter the report.

Hoe to Filter the Report Paul?

 

[ekryez]

Solution Found.
DoCmd.OpenReport "Total Report", acViewPreview, , "ProjectNumber='" & !ProjectNumber & "'"
DoCmd.OutputTo acOutputReport, "Total Report", acFormatPDF, MyPath & MyFileName
DoCmd.Close acReport, "Total Report"

 

[pbaldy]

Huh? The line I posted should filter the report.

 

[ekryez]

Huh? The line I posted should filter the report.

I did thank you twice
and added reputation