ANOTHER Loop, Report to PDF....

[Rastro_Jetson]

Dear all, newbie to vba.

I've spend hours cobbling this together from various sites on line. I'll gladly take the chastisement if someone can correct my mistakes. :^)

Table with looped value is "tbl_Carrier_IDs". Looped field is "Carrier_ID". When I run the code I get a "Enter Parameter Value" [Carrier_ID], so I know that is wrong. If I enter valid values there, it runs the report (for all clients in one report) and then gives me the Run Time 2501 error, "OutpuTo action was cancelled".

Please let me know if you can see any glaring mistakes that would turn this boat anchor into a Concorde. Thanks in advance. :^))

*****
Public Sub ExportToPDF()
Const Folder = "C:\Users\Me\Desktop\Access Loop"
Const Domain = "tbl_Carrier_IDs"
Const LoopedField = "Carrier_ID"
Const ReportName = "rpt_Example"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)
Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".PDF"
FullPath = Folder & FileName
strWhere = LoopedField & " = " & LoopedFieldValue
Debug.Print FullPath
Debug.Print strWhere
DoCmd.OpenReport ReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
DoCmd.Close acReport, rpt_Example
rs.MoveNext
Loop
End Sub

 

[sneuberg]

I suggest checking the spelling of Carrier_ID. I'd open tbl_Carrier_IDs in design view and copy and paste it from there. If Carrier_ID is doesn't have the underscore, i.e., "Carrier ID" then that's the way it needs to be here.

 

[sneuberg]

You can get "Run Time 2501 error, "OutpuTo action was cancelled". if the Cancel button was clicked. Sometimes you just need to trap this error and do nothing in the error handler.

 

[Rastro_Jetson]

Thank you so much for the reply. I did some checking, it sure looks like the field name and table name are correct. I will add a few screen shots of the objects and errors, maybe that will make something obvious. Thanks again for your time and efforts.

 

[sneuberg]

Here's a couple of things that need fixing:

The strWhere concatenated value needs to be in single quotes like:

  strWhere = LoopedField & " =[COLOR="red"] '[/COLOR]" & LoopedFieldValue [COLOR="Red"]& "'"[/COLOR]

The line

DoCmd.Close acReport, rpt_Example

should be

 DoCmd.Close acReport, ReportName

Since rpt_Example is not a literal, variable or constant this second error would have shown up during compilation if you had

Option Explicit

specified at the top of the code. To have Access put this in for you in the future in the code view menu click Tools, Options, Editor Tab, check Require Variable Declaration.

 

[sneuberg]

Oh and you probably want to add a backslash in the full path like:

FullPath = Folder & [COLOR="Red"]"\" &[/COLOR] FileName

 

[Rastro_Jetson]

Ok, I've incorporated all the fixes, checked the field names, etc. and still no joy. :^O

Here is the code. My steps and the outcome is below the code. Thanks so much for any input.

Public Sub ExportToPDF()
Const Folder = "\\stlfs1\profiles$\me\Desktop\Access Loop"
Const Domain = "tbl_Carrier_IDs"
Const LoopedField = "Carrier_ID"
Const ReportName = "rpt_Example"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)

Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".PDF"
FullPath = Folder & "" & FileName
strWhere = LoopedField & " = '" & LoopedFieldValue & "'"
Debug.Print FullPath
Debug.Print strWhere
DoCmd.OpenReport ReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
DoCmd.Close acReport, ReportName
rs.MoveNext
Loop
End Sub

1. I open the Module and run it.
2. The code pops up “Enter Parameter Value”. [CARRIER_ID] I enter the first value in my table of looped values. Pretty sure I shouldn't need to do this.
3. It runs the report in background for all Carrier_IDs, not just the first one. Wondering if there is some criteria I need to put in the query that feeds the report for [Carrier_ID].
4. Then it gives me, “Run-time error’2501’: The OutputTo action was cancelled.”
5. I didn’t touch or cancel anything.
6. If I click Debug, it takes me to the line below, highlighted in yellow.
7. DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath

 

[sneuberg]

You didn't get the backslash in

FullPath = Folder & "" & FileName

It should be

FullPath = Folder & "[COLOR="Red"]\[/COLOR]" & FileName

but that would just put the output in the wrong place. I can't see what's wrong with your code now. I've uploaded the database where I tested and corrected the code before. It runs on my system if you change:

Const Folder = "C:\Users\sneuberg\Desktop"

to a valid folder on your system it should run. If you can get that working maybe you can figure out how yours is different.

 

[sneuberg]

Can zip your database and upload it?

 

[Rastro_Jetson]

Alright. Thank you so much for the reply. I'll do some work on my end and maybe it doesn't like the path or something. Thank you for the advice and time.

 

[Cronk]

After this line
strWhere = LoopedField & " = " & LoopedFieldValue

add
Debug.print strWhere

and I think you will see the problem.

You probably need to have
strWhere = chr(34) & LoopedField & " = '" & chr(34) & LoopedFieldValue & "'"

 

[Rastro_Jetson]

Sneuberg, you had the answer. :^) I had a reference in my report that was missing the underscore in Carrier_ID. I thought that reference was to my table where it was getting the looped value, which was correct. Once I fixed that and added a path that the VB liked, it ran without issue. Thank you for the guidance in newbie land. :^))