Help please on Report Outputto

[Pablos]

Good afternoon fellow Access devotees

I've been trying for a while now to get my code going..Feeling bit like my friend to my right..... :banghead:

Allow me to explain. I have a Report that has a 'Division' as a Header.

A Division can be one of five variables ("CPD","SND","PSD","BPD","PPD")

Ideally want to output the report as a type PDF for each Division. Here, have 5 reports in a folder. I am only getting one. I know that the missing reports is not null. That is they have records.
Here is the code I have cobbled together...

Private Sub Print1_Click()
Dim PathNm As String
Dim RptNm As String
Dim strFileNm As String
Dim Sql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

On Error GoTo ErrorHandler
strPathNm = "D:\Stock\"
strRptNm = "BCSP Summary Report"

Sql = "SELECT DISTINCT R_Label1 FROM Ring Report"
Set db = CurrentDb()
Set rs = db.OpenRecordset("BCSP Report")

OwnNm = rs!R_label1
strFileNm = strPathNm & "BCSP_Summary" & "_" & OwnNm & ".pdf"

DoCmd.OpenReport strRptNm, acViewPreview, , "R_Label1 = '" & "CPD" & "' "
DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm
DoCmd.Close acReport, strRptNm

DoCmd.OpenReport strRptNm, acViewPreview, , "R_Label1 = '" & "SND" & "' "
DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm
DoCmd.Close acReport, strRptNm

DoCmd.OpenReport strRptNm, acViewPreview, , "R_Label1 = '" & "PSD" & "' "
DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm
DoCmd.Close acReport, strRptNm

DoCmd.OpenReport strRptNm, acViewPreview, , "R_Label1 = '" & "BPD" & "' "
DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm
DoCmd.Close acReport, strRptNm
DoCmd.OpenReport strRptNm, acViewPreview, , "R_Label1 = '" & "PPD" & "' "
DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm
DoCmd.Close acReport, strRptNm
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & Err.Description
End Sub

As I run the VBA I can see the all the Reports in preview so I believe that the filter is working. The Output to is falling down.

I accept that my code could work better. I tried writing code as a loop. It did work but as there are only five variables and over 1000 results in the query it took forever to loop through. I tried a Select Case Statement but couldn't get that going.

I would really value some guidance on how to do this.

Thanks in advance
Paul

 

[pbaldy]

You use the same file name for the output, so it's likely overwriting itself.

 

[Sketchin]

I do something similiar to this but I open the filtered report, and on that report, click a button to save as a pdf (for example).

Doing the "Docmd.outputto" is not going to output a filtered report, it will probably be outputting a report with all of the data 5 times over.

 

[Sketchin]

Maybe you can put the Docmd.Output to on the on open event of the report?

 

[pbaldy]

Since the report is open filtered, the OutputTo should create a filtered PDF.

 

[Pablos]

Firstly thanks for the interest in this. The Output to that is visible in the folder has the name BCSP_Summary_CPD and is filtered showing only CPD. So it does create a filtered PDF. Just stops at the first hurdle..
Somehow I need to work out how to get Access to move to the next variable...I am taking on board your comments and still striving for a fix. Paul

 

[Sketchin]

Seems to me that you will have to have a line such as:
strfilename = strPathNm & "BCSP_Summary" & "_" & OwnNm & ".pdf"
after each Outputto

Currently you are setting StrFileName once at the top and using that value for each Output to instance.

 

[pbaldy]

Like I said, you need to change the file name for each output. You currently use the same name for all five, so obviously you'll end up with one file, presumably the last one created.

 

[Pablos]

Thanks pbaldy (had to google who is John Galt !) & Sketchin - FIXED IT

 

[Pablos]

For anyone interested. I changed the output line as suggested by my knowledgeable friends

DoCmd.OutputTo acOutputReport, StrRptNm, acFormatPDF, strPathNm & "BCSP_Summary" & "_" & "CPD" & ".pdf"

Also for SND, PPD,BPD & PSD

 

[Pablos]

One last thing - how do I close this post?

 

[Sketchin]

Just leave it.

Glad to help, I know Pbaldy has got me out of a ton of tight spots!