ANOTHER Loop, Report to PDF.... (1 Viewer)

Rastro_Jetson

Registered User
Joined
Mar 1, 2017
Messages
12
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

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
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

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
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

Registered User
Joined
Mar 1, 2017
Messages
12
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.
 

Attachments

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
Here's a couple of things that need fixing:

The strWhere concatenated value needs to be in single quotes like:
Code:
  strWhere = LoopedField & " =[COLOR="red"] '[/COLOR]" & LoopedFieldValue [COLOR="Red"]& "'"[/COLOR]
The line

Code:
DoCmd.Close acReport, rpt_Example
should be

Code:
 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
Code:
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

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
Oh and you probably want to add a backslash in the full path like:

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

Rastro_Jetson

Registered User
Joined
Mar 1, 2017
Messages
12
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

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
You didn't get the backslash in

Code:
FullPath = Folder & "" & FileName
It should be

Code:
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:
Code:
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.
 

Attachments

Rastro_Jetson

Registered User
Joined
Mar 1, 2017
Messages
12
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

Registered User
Joined
Jul 4, 2013
Messages
2,230
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

Registered User
Joined
Mar 1, 2017
Messages
12
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. :^))
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom