SoccerPro
asked on
Unable to export to PDF from Access 2007
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("tblFina
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("FedByE
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
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("tblFina
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("FedByE
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
do you have the SaveAsPDF add-in installed on your new computer?
http://www.microsoft.com/download/en/details.aspx?id=7
http://www.microsoft.com/download/en/details.aspx?id=7
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"
(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"
ASKER
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
It appears to just disappear and quit without any warnings.
Jeff
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?
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?
ASKER
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???
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.
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.
ASKER
Yes,
I checked and tested it... Worked fine.
I checked and tested it... Worked fine.
ASKER
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
The PDF test worked on a different report.
ManualExportPDFmsg.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jeff, I'm gonna leave this one to you. No sense in both of us trying to trouble shoot this one.
Dale
Dale
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
"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
ASKER
Ok,
I created the simple test report and it worked! So, where do we go from here, thanks?
Jeff
I created the simple test report and it worked! So, where do we go from here, thanks?
Jeff
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
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
ASKER
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.
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.
<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
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
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...
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...
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