Unable to export to PDF from Access 2007

SoccerPro
SoccerPro used Ask the Experts™
on
Hi,

I had an older laptop with XP & Office 2003, now I have a new laptop with XP & Office 2007 (work computer, thus XP).

Anyway, I've had no trouble outputting the following code to PDF in the past, now it executes each line of code but doesn't execute the DoCmd.OutputTo section.

I added Msgbox's to provide me the troubleshooting necessary to identify where it fails.  Every msgbox pops up except the "Output Successful!".  No error messages, just nothing.

I've tried messing with the references and installing the Window for Office PDF addin.  I might have messed something up when I converted the original database from 2000 format to 2007?

Can you please help?

' Define variables
   Dim ReportName As String
   Dim ExportName As String
   Dim ExportLoc As String
   
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   
   Dim sqlQryDef
   Dim intRecordID As Integer
   Dim x As Integer
   
   Dim qdfNew As DAO.QueryDef
   
   Set dbs = CurrentDb
   
   ' Open recordset
   Set rst = dbs.OpenRecordset("tblFinalTCS2")
   
   MsgBox rst.RecordCount 'used to test record count is accurate
   
   With rst
      .MoveFirst
     
      ' Define loop to go thru every record until end
      Do While Not .EOF
         
         MsgBox "intRecordID = " & intRecordID   'Test
         ' Set integer to autonumber in recordset
         intRecordID = !ID
         MsgBox "rst!ID = " & !ID  'Test
            ' Create QueryDef.
            sqlQryDef = "SELECT * FROM tblFinalTCS2 WHERE ID = " & intRecordID
            Set qdfNew = dbs.CreateQueryDef("FedByEmpIDQueryDef", sqlQryDef)
           
            MsgBox sqlQryDef  'Test
            ' set strings
            ReportName = "rptTCS_FinalFormat2"
            MsgBox ReportName  'Test
            ExportLoc = "c:\TEMP2\"
            MsgBox ExportLoc  'Test
            ExportName = rst("EMPID") & "-2011-TC"
            MsgBox ExportName  'Test
           
            ' Output PDF w/ExportName to ExportLoc
            DoCmd.OutputTo acOutputReport, ReportName, "PDFFormat(*.pdf)", ExportLoc & "\" + ExportName & ".pdf", False, "", 0, acExportQualityPrint
            MsgBox "Output Successful!"  'Test
           
            ' Delete QueryDef
            dbs.QueryDefs.Delete qdfNew.Name
           
         .MoveNext
      Loop
   End With
   
   Set dbs = Nothing

Thank you, Jeff
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
For Access 207 you need to install the PDF add in:
http://www.microsoft.com/download/en/details.aspx?id=9943

Also try it like this:
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, ExportLoc & "\" + ExportName & ".pdf", False, "", 0, acExportQualityPrint
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
do you have the SaveAsPDF add-in installed on your new computer?

http://www.microsoft.com/download/en/details.aspx?id=7
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Or like this:
(Replacing the "+" with "&"

DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, ExportLoc & "\" & ExportName & ".pdf", False, "", 0, acExportQualityPrint


Or just like this, just to see if it even works:
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, ExportLoc & "\" & ExportName & ".pdf"

Author

Commented:
Thank you boag200, but I've tried both of those solutions and still no results.  It appears to be outputting but doesn't produce the PDF in the folder, I even have to manually delete the QueryDef because it will not even step thru the DoCmd line and complete the loop.

It appears to just disappear and quit without any warnings.

Jeff
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Also do this before the Output line to verify the correct Full path and file name:

Msgbox ExportLoc & "\" & ExportName & ".pdf"

(Since ExportLoc is defined as: "c:\TEMP2\"
Perhaps you don't need to concatenate the: "\"

Just curious...
Is the PDF file being created?

Author

Commented:
Good catch on that, I did correct the output to reflect that change but getting the same results ---  nothing happens...  I just don't understand???
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
did you confirm that you actually have the SaveAsPDF add-in installed?

Open your database.  Go to the Nav Pane, left click on the report you want to save, then click the Export option on the popup.

Do you see an option for "PDF or XPS"?  If not, you need to install the add-in I referenced above.

Author

Commented:
Yes,

I checked and tested it...  Worked fine.

Author

Commented:
Actually, I received this message (see attached) when I tried to output the same report from the code in this discussion.  

The PDF test worked on a different report.
ManualExportPDFmsg.jpg
MIS Liason
Most Valuable Expert 2012
Commented:
OK

...Let's keep this simple.

Create a report base on a "table".
Name the report: TestReport
Then run this code (from a button on a form) and tell us what happens:
    DoCmd.OutputTo acOutputReport, "TestReport", acFormatPDF, "C:\TestReport.pdf"
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Jeff, I'm gonna leave this one to you.  No sense in both of us trying to trouble shoot this one.

Dale
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Dale, as you always say...
"The more the merrier"
;-)

I was just going back to the most basic syntax, ...just to see if it works.

There is a lot else going on in that code that you might be better at helping with...

I tend to take a more of a "hammer" approach to some questions, while your style is a bit more "surgical"

So please, fell free to hang around...
I may have to leave abruptly (I'm at work too)
;-)

Neither one of is wrapped up in "Points" anyway...

;-)

Jeff

Author

Commented:
Ok,

I created the simple test report and it worked!  So, where do we go from here, thanks?

Jeff
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
It sounds like you have somehow turned off your error messages and instead of generating an error your code is somehow dropping out of the loop without a message.

At the top of your code module add:

docmd.setwarnings TRUE
On Error Goto ProcError

Then, change the end of your module from:

 Set dbs = Nothing

to

ProcExit:
    if not dbs is nothing then  Set dbs = Nothing
    msgbox "Done!"
    exit sub

ProcError:
    msgbox err.number & vbcrlf  & err.description, vbOk, "Error in Module name"
    debug.print "error in module name", err.number, err.description
    Resume ProcExit

End Sub

Author

Commented:
boag200,

I figured it out, but this allowed me to simplify my troubleshooting and keep me on track. By this working it told me I needed to check the actual report and not the function I wrote.

Bottom line, I had added some formatting procedures in my report, and when I went to update it I realized I had forgotten to edit the text box and labels being impacted by these changes.  DUH!!!

I was able to then correct this and the function ran just fine, looping thru each record and outputting it to PDF in the Temp2 folder.

Thank you for your quick responses and help in understanding the problem.

Jeff

Sorry Dale, I didn't get a chance to look at your final thought as I had already resolved the issue.  I look forward to our next encounter.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<So, where do we go from here, thanks?>
back to the beginning....
Harcode the values, then add in all your "Other Stuff" (variables, recodesets, ..etc)
...until the problem surfaces

^ along those same lines, ...you seem to be wanting the "Successful" message after each *Record*
Is this what you want?

I just do it after the loop.
(You are also not closing the DBs  or the recordset.
Also set the recordset to nothing...:

DoCmd.OutputTo acOutputReport, ReportName, "PDFFormat(*.pdf)", ExportLoc & "\" + ExportName & ".pdf", False, "", 0, acExportQualityPrint
           
            ' Delete QueryDef
            dbs.QueryDefs.Delete qdfNew.Name
           
         .MoveNext
      Loop
   End With
   
MsgBox "Output Successful!"  'Test

rst.close  
Set rst=Nothing
dbs.close
Set dbs = Nothing
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
ummm...

OK,  so problem solved?

if so, then you can request that your post be accepted instead of mine, or request a points split...

Either way, it is worth noting what you discovered, our post were just exploratory...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial