Do.cmd OutputTo Error 2501

[Fuzz_cozens]

Hi

Newbie here. I keep getting the 2501 error and cannot seem to find the error. When a click the button the report I need does open. I am not sure whether the fact I have a subreport built into the main report affects the below code or does the subreport also have to be in the code

Please could someone have a look at my code to see where the problem is:

Private Sub BCWeeklyProductionReports_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim mypath As String

mypath = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\BC Weekly Production\Weekly Stats Per Region Per BC - "
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT distinct [Region] FROM [bc statistics vS Targets]", dbOpenSnapshot)
Do While Not rs.EOF
temp = rs("region")
MyFileName = rs("region") & ".PDF"
DoCmd.OpenReport "Regional Production Stats", acViewReport, , "[region]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Regional Production Stats Order By region"
DoEvents
rs.MoveNext
Loop

rs.Close
Set rs = Nothing


Set db = Nothing
End Sub

Thanks in advance

Richard

[pbaldy]

You didn't specify the report in OutputTo, you close a different report than you open, and I'm not sure the path is coming out as you expect.

[rpeare]

have you tried debug.printing your filter statement to see if it's constructing it correctly

have you set the FILTER ON LOAD to YES
is your REGION field a text value or the PK (numeric) indicating the region?

Is this code from some other location, I haven't ever tried to apply filters to a report, I prefer have dynamic criteria on the query driving the report instead, but I do not know if the DOCMD.OUTPUTTO will work without the report name supplied (you currently have "" and I don't know if that will work) have you tried:


DoCmd.OutputTo acOutputReport, , acFormatPDF, mypath & MyFileName

what I'm reading is that you have to leave the report name blank (not a blank string)

I ran a test where I modified your code slightly:

Dim db As Database
Dim rst As Recordset
Dim sExportPath

On Error GoTo ERRHANDLER

Set db = CurrentDb
Set rst = db.OpenRecordset("tblemployees")

Do While rst.EOF <> True
    Debug.Print rst("ee_id")
    DoCmd.OpenReport "rptTest", acViewReport, , "[EE_ID] = '" & rst("ee_id") & "'"
    sExportPath = CurrentProject.Path & "\" & rst("ee_id") & ".pdf"
    DoCmd.OutputTo acOutputReport, , acFormatPDF, sExportPath
    DoCmd.Close acReport, "rptTest", acSaveNo
    rst.MoveNext
Loop
rst.Close
Set db = Nothing
Exit Sub

ERRHANDLER:
Debug.Print Err.Number & "  " & Err.Description

and it exported everything properly filtered.

[Fuzz_cozens]

have you tried debug.printing your filter statement to see if it's constructing it correctly

have you set the FILTER ON LOAD to YES
is your REGION field a text value or the PK (numeric) indicating the region?

Is this code from some other location, I haven't ever tried to apply filters to a report, I prefer have dynamic criteria on the query driving the report instead, but I do not know if the DOCMD.OUTPUTTO will work without the report name supplied (you currently have "" and I don't know if that will work) have you tried:


DoCmd.OutputTo acOutputReport, , acFormatPDF, mypath & MyFileName

what I'm reading is that you have to leave the report name blank (not a blank string)

I ran a test where I modified your code slightly:

Dim db As Database
Dim rst As Recordset
Dim sExportPath

On Error GoTo ERRHANDLER

Set db = CurrentDb
Set rst = db.OpenRecordset("tblemployees")

Do While rst.EOF <> True
    Debug.Print rst("ee_id")
    DoCmd.OpenReport "rptTest", acViewReport, , "[EE_ID] = '" & rst("ee_id") & "'"
    sExportPath = CurrentProject.Path & "\" & rst("ee_id") & ".pdf"
    DoCmd.OutputTo acOutputReport, , acFormatPDF, sExportPath
    DoCmd.Close acReport, "rptTest", acSaveNo
    rst.MoveNext
Loop
rst.Close
Set db = Nothing
Exit Sub

ERRHANDLER:
Debug.Print Err.Number & "  " & Err.Description

and it exported everything properly filtered.

Thanks for your thorough response.

I changed the filter on load to YES as it was No

The Region field is a Text filed and contains values such as EC/1 or EC/2

[Fuzz_cozens]

I looked now and not sure if the following will have anything to do with the
error.



The Main report is called "Regional Production Stats" and in the main report
I have a subreport called "Regional Production Stats Subreport"



Would the subreport be causing the issue as they both obtain their data from
the query called "bc
statistics vS Targets"



Thanks


Richard

[rpeare]

if you have / values in your field names or descriptions you'll have to change it. I don't believe you can have file names with slashes (/) in them. Try replacing the / with an underscore or something.

replace(rst("ee_id"), "/", "_")

[Fuzz_cozens]

Thanks a lot rpeare. I changed the field names from EC/1 to EC1 for example and works perfectly.

Just one last question. Is there a way to code that in the output the Mypath and Myfilename are reversed as I would basically like the output to display the [Region] field first. In other words currently the outputto is displaying mypath = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\BC Weekly Production\Weekly Stats Per Region Per BC - " and then the [Region] and I would like the [Region] displayed first then the "Weekly Stats Per Region Per BC"

[rpeare]

sExportPath = CurrentProject.Path & "\" & rst("ee_id") & " Weekly Stats per Region per BC.pdf"