Do.cmd OutputTo Error 2501

[the_fuzz]

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

 

[pr2-eugin]

You have not specified which report to export. Try using the name of the report.

 

[the_fuzz]

You have not specified which report to export. Try using the name of the report.

Thanks for the reply. I inserted the report name as below but still get the error "OutputTo action was canceled" error 2501

DoCmd.OutputTo acOutputReport, "Regional Production Stats", acFormatPDF, mypath & MyFileName

Can you perhaps see what else might be incorrect with the code

Thanks

Richard

 

[JHB]

The path looks wrong to me, I would expect a "\" as last character!

mypath = "C:\Users\Richard\Desktop\1Life Broker Services\Automatic Reports\BC Weekly Production\Weekly Stats Per Region Per BC - "

 

[the_fuzz]

The path looks wrong to me, I would expect a "\" as last character!

Thanks. I inserted the \ but no difference. I use the same Path format for my other reports which work perfectly

 

[the_fuzz]

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

 

[JHB]

Thanks. I inserted the \ but no difference. I use the same Path format for my other reports which work perfectly

The show path format (or even better, the code), for one of the other working reports.

 

[the_fuzz]

The show path format (or even better, the code), for one of the other working reports.

Great. Here is the code that is working on another report of mine

Private Sub AutoCommSummary_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\Commission Summaries\Commission Summary - "
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT distinct [Brokerage] FROM [Commission Summary - Ring-Fenced]", dbOpenSnapshot)
Do While Not rs.EOF
temp = rs("brokerage")
MyFileName = rs("Brokerage") & ".PDF"
DoCmd.OpenReport "Commission Summary - 1Life July 2014", acViewReport, , "[Brokerage]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Commission Summary - 1Life July 2014 Order By brokerage"
DoEvents
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[pr2-eugin]

Okay as JHB suggests the path might be the problem ! Windows naming convention does not allow some special characters like `, /,# etc. See if the file name contains some funky chars.

 

[the_fuzz]

I checked the characters and besides the - in the file name there are no other special characters. The - in the other report that works does work perfectly like that.

What I did now was to change the unique filed from "Region" to Broker Consultant" and it works and saves perfectly.

The only problem now still with the outputTo is that it only creates 7 of the 15 files so after creating 7 PDF files the OutputTo is canceled with error 2501.

At least there is movement forward. Below is the new code:

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 [Broker Consultant] FROM [bc statistics vS Targets]", dbOpenSnapshot)

Do While Not rs.EOF

temp = rs("Broker Consultant")
MyFileName = rs("Broker Consultant") & ".PDF"

DoCmd.OpenReport "Regional Production Stats", acViewReport, , "[Broker Consultant]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Regional Production Stats Order By Broker Consultant"
DoEvents

rs.MoveNext
Loop


rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[pr2-eugin]

Try this code, and copy what is generated in the Immediate window (http://www.baldyweb.com/ImmediateWindow.htm) on here.. That is up until the point the error is thrown..

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 [Broker Consultant] FROM [bc statistics vS Targets]", dbOpenSnapshot)

    Do While Not rs.EOF
        temp = rs("Broker Consultant")
        MyFileName = rs("Broker Consultant") & ".PDF"

        [COLOR=Red][B]Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName[/B][/COLOR]
        
        DoCmd.OpenReport "Regional Production Stats", acViewReport, , "[Broker Consultant]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
        DoCmd.Close acReport, "Regional Production Stats"
        DoEvents
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[the_fuzz]

Ok. The error didn't occur but then I saw that the report generation stopped when the next Broker Consultant is called "HQ/WC" then I remembered you mentioned special characters. I excluded "HQ/WC" from the query and then the report exported in full after that.

Thanks very much for all your input.

Is there a way in the OutputTo cmd to allow special characters like"HQ/WC" so I can also send them their stats

Thanks again

 

[vbaInet]

OutputTo will open and send a fresh report so the criteria you passed via OpenReport won't affect the output.

In the following link there's (post #10) it explains you can accomplish this:
http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

Another would be to use a hidden textbox and pass the parameters from there.