Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Why does my Access Report not get saved as a PDF file?
I am developing an Access 2007 "project" (.ADP) as a front-end to a SQL Server 2005 Express database.
Several of my Access Reports get printed then successfully saved as a PDF file using the following VBA code :
DoCmd.OpenReport strReportName, acViewNormal, , , , strOpenArgs
DoEvents
If Not genAllBlanks(strArchiveFil
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
End If
DoCmd.Close acReport, strReportName
Howver, I have been stumped all day on this with a new Report that I am creating! My report "prints" OK, but the PDF file just does not get created. In fact, I don't think the Sub Procedure in which this happens is being completed because I do not get a "Message Box" as it is exitted.
This Report is a very simple, single-page report and this type of code works OK with other more compicated reports that involve various fonts and graphics.
I read on one web-site that the PDF export function doesn't like too much VBA code in the Report, so I stripped all that out (there was a couple of calls to some SQL Server Stored Procedures) but that did not solve it. I have also changed the path name for my strArchiveFile variable but with no success.
Can anyone suggest why this technique which has worked for me over several years on much larger reports, be causing this problem?
Many thanks. Colin.
Several of my Access Reports get printed then successfully saved as a PDF file using the following VBA code :
DoCmd.OpenReport strReportName, acViewNormal, , , , strOpenArgs
DoEvents
If Not genAllBlanks(strArchiveFil
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
End If
DoCmd.Close acReport, strReportName
Howver, I have been stumped all day on this with a new Report that I am creating! My report "prints" OK, but the PDF file just does not get created. In fact, I don't think the Sub Procedure in which this happens is being completed because I do not get a "Message Box" as it is exitted.
This Report is a very simple, single-page report and this type of code works OK with other more compicated reports that involve various fonts and graphics.
I read on one web-site that the PDF export function doesn't like too much VBA code in the Report, so I stripped all that out (there was a couple of calls to some SQL Server Stored Procedures) but that did not solve it. I have also changed the path name for my strArchiveFile variable but with no success.
Can anyone suggest why this technique which has worked for me over several years on much larger reports, be causing this problem?
Many thanks. Colin.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Are you absolutely sure this line resolves to TRUE:
If Not genAllBlanks(strArchiveFil
If not, your report would never get to the OutputTo line. Try setting a breakpoint in your code, and then run the function that executes that code. You should be able to step through the code to determine exactly what is happening.
Another thing you can try is to export the report using the interface - open the report, and then use the Ribbon to export it to PDF. You'll often get errors there that may not surface in VBA.
If Not genAllBlanks(strArchiveFil
If not, your report would never get to the OutputTo line. Try setting a breakpoint in your code, and then run the function that executes that code. You should be able to step through the code to determine exactly what is happening.
Another thing you can try is to export the report using the interface - open the report, and then use the Ribbon to export it to PDF. You'll often get errors there that may not surface in VBA.
Thanks for the fast response.
Yes, I'm pretty sure my "if" condition is valid; in fact I even see the usual (Access - generated?) Message Box pop-up, telling me that the report is being saved, even showing the full "strArchiveFile" name.
I will try your other suggestion.
Regards.
Yes, I'm pretty sure my "if" condition is valid; in fact I even see the usual (Access - generated?) Message Box pop-up, telling me that the report is being saved, even showing the full "strArchiveFile" name.
I will try your other suggestion.
Regards.
If Not genAllBlanks(strArchiveFil
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
End If
There's a bunch of way for that to go wrong
What should this return?
genAllBlanks(strArchiveFil
Because if it returns NULL, unexpected things are going to happen.
I take it you are expecting it to return false.
What is this supposed to return?
strArchiveFile
One expects that it should be a fully qualified path ie c:\tempPDF\myfile.pdf
Is it a full path?
If it is just a filename you can get silent failures
Does the folder exist on the target machine -- because you can get silent failure if it does not.
Does the logged-in user have the proper permissions?
You may get silent failures if the user does not.
Does the file already exist?
You may get silent failures for that reason.
Is the file in existence and open?
That may fail, too.
Here you have OpenArgs
DoCmd.OpenReport strReportName, acViewNormal, , , , strOpenArgs
Here you don't
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
Does your report actually have any data without OpenArgs?
I'd go this way anyhow
DoCmd.OpenReport strReportName, acViewPreview, , , , strOpenArgs
DoCmd.SelectObject acReport, strReportName
DoCmd.PrintOut acPrintAll, , , , 1, True
DoEvents
If Not genAllBlanks(strArchiveFil
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
End If
DoCmd.Close acReport, strReportName
Open it
Print it
Output It
Close it
It only has to render once this way
acNormal opens, prints and closes as does OutputTo
I would not recommend developing new ADP applications given that the ADP has been deprecated. If you haven't gone too far, you might consider changing to an .accdb instead and using linked tables and bound forms. You'll need to learn to control the recordsources for your forms to limit the rows they show since this is handled behind the scenes with the ADP. But, the reason that the ADP was deprecated was that it didn't produce a significant enough performance improvement over a properly developed .accdb and so very few developers actually used them.
@PatHartman
Don't let @acperkins or other really accomplished ADP experts hear you say that. :)
ADP's performed really well --especially with lots of users and on slower networks because the server did ALL of the lifting, but they were a fairly steep learning curve compared to .mdb.
And the Access dev had to have full access to the SQL Server.
Therefore they had a smaller user base.
The MS-given reason for the deprecation of ADP was Azure
https://msdn.microsoft.com/en-us/library/office/jj618413.aspx
Basically, MS dropped OLEDB support from SQL Server, and that meant that ADP's were going to run into the day when no SQL Server would support them anymore -- so they were deprecated.
A2010 could not create or edit ADP's
A2013 can't touch them at all
SQL Server 2008 R2 was the last server with full OLEDB support.
There is much cursing, and then much abandonment of Access when ADP's got whacked.
Don't let @acperkins or other really accomplished ADP experts hear you say that. :)
ADP's performed really well --especially with lots of users and on slower networks because the server did ALL of the lifting, but they were a fairly steep learning curve compared to .mdb.
And the Access dev had to have full access to the SQL Server.
Therefore they had a smaller user base.
The MS-given reason for the deprecation of ADP was Azure
https://msdn.microsoft.com/en-us/library/office/jj618413.aspx
SQL Azure, the cloud version of SQL Server, does not support features that Access Data Projects (ADPs) rely on to operate. To support ADPs on SQL Azure, Access would require significant changes.
For these reasons, Access does not support ADPs. ADPs have been a great tool for working with SQL Server in an on-premise environment, however much has changed since ADPs were introduced in Access 2000. Users now expect their database to be available wherever they are.
Basically, MS dropped OLEDB support from SQL Server, and that meant that ADP's were going to run into the day when no SQL Server would support them anymore -- so they were deprecated.
A2010 could not create or edit ADP's
A2013 can't touch them at all
SQL Server 2008 R2 was the last server with full OLEDB support.
There is much cursing, and then much abandonment of Access when ADP's got whacked.
Thanks for the further responses.
Regarding my specific problem :
Here is my own Sub Function "genAllBlanks"
Public Function genAllBlanks(strTestStr As String) As Boolean
genAllBlanks = Trim(Nz(strTestStr, "")) = ""
End Function ' genAllBlanks
I use it throughout my application to test that a string variable actually has a value before I try to work with it.
In this particular instance I am 99.99% sure "strArchiveFile" has a valid value because I construct it just a few lines above my attempt to produce the report. I use "genAllBlanks" as a "belt an braces" check before I attempt the PDF creation.
I have also put a tracer "Message Box" in my code to check the value of "strArchiveFile" before my attempt to open the report and create the PDF file. Yes, it is a full <PATH><FILENAME<.PDF> string, pointing to a folder that exists (I tried changing the "PATH" to several different folders in my various tests yesterday).
Yes, I send in an "Open Arguments" string to my report, but I do not see where I would specify an "Open Argument" parameter with the "DoCmd.OutputTo" call. Also, this save strategy has worked with many other similar situations in my application.
The unusual thing about this particular "Report" is that it has no real "Data Source" - all the data controls are "Unbound" and filled in various of the Report's "OnFormat" sub procedures.
This morning I have stripped out all of the data field filling and the "empty" Report prints OK AND I SUCCESSFULLY GET MY PDF FILE TOO!!!
So what I will do now is start to fill out the data inside the Report and discover at what stage it fails.
Regarding the "Project / ADP" discussion :
Yes, I am aware that Access "projects" are no longer supported. This current application was originally written 5 or 6 years ago but my client is always wanting enhancements made to it. I think he is about to upgrade his Server and install Windows Server 2012. I plan to install the 64-bit SQL Server 2008 Express on his new server and think that my Access 2005 (.ADP) front-end should still connect to it OK.
I understand that if I want to bring my Access version beyond 2010 I will need to change my connection strategy, but I am leaving that for another day.
Thanks again for your comments and I will let you know how I get on, but it might be into next week. The main thing for my client is getting the new report printed, My attempt to create a PDF copy as an "archive" document was an extra feature I often add, but is not essential.
Regarding my specific problem :
Here is my own Sub Function "genAllBlanks"
Public Function genAllBlanks(strTestStr As String) As Boolean
genAllBlanks = Trim(Nz(strTestStr, "")) = ""
End Function ' genAllBlanks
I use it throughout my application to test that a string variable actually has a value before I try to work with it.
In this particular instance I am 99.99% sure "strArchiveFile" has a valid value because I construct it just a few lines above my attempt to produce the report. I use "genAllBlanks" as a "belt an braces" check before I attempt the PDF creation.
I have also put a tracer "Message Box" in my code to check the value of "strArchiveFile" before my attempt to open the report and create the PDF file. Yes, it is a full <PATH><FILENAME<.PDF> string, pointing to a folder that exists (I tried changing the "PATH" to several different folders in my various tests yesterday).
Yes, I send in an "Open Arguments" string to my report, but I do not see where I would specify an "Open Argument" parameter with the "DoCmd.OutputTo" call. Also, this save strategy has worked with many other similar situations in my application.
The unusual thing about this particular "Report" is that it has no real "Data Source" - all the data controls are "Unbound" and filled in various of the Report's "OnFormat" sub procedures.
This morning I have stripped out all of the data field filling and the "empty" Report prints OK AND I SUCCESSFULLY GET MY PDF FILE TOO!!!
So what I will do now is start to fill out the data inside the Report and discover at what stage it fails.
Regarding the "Project / ADP" discussion :
Yes, I am aware that Access "projects" are no longer supported. This current application was originally written 5 or 6 years ago but my client is always wanting enhancements made to it. I think he is about to upgrade his Server and install Windows Server 2012. I plan to install the 64-bit SQL Server 2008 Express on his new server and think that my Access 2005 (.ADP) front-end should still connect to it OK.
I understand that if I want to bring my Access version beyond 2010 I will need to change my connection strategy, but I am leaving that for another day.
Thanks again for your comments and I will let you know how I get on, but it might be into next week. The main thing for my client is getting the new report printed, My attempt to create a PDF copy as an "archive" document was an extra feature I often add, but is not essential.
I managed to look at this a little more before finishing for the week-end.
I stripped down my report to bare minimum and re-built it step by step. It has "Report Header", "Page Header", "Detail" and "Page Footer" sections, all of which contain 0nly "Unbound" controls which I fill out in their various "On Format" event sub procedures.
The problem appears to start as soon as I try to assign values (fed in as separated strings in the single Report's "Open Arguments" string) in the "ReportHeader_Format" sub procedure (the first section I tried to fill). The values "print" OK, but the PDF version of the report does not get saved. Without my attempts to populate the controls in "Report Header_Format", the "empty" report prints OK and gets saved as a PDF OK as well. I have tried using the "ReportHeader_Print" sub procedure instead, with the same result : prints OK but no PDF saved.
I am using an array variable "arrParts as String" which I fill in "Report_Open" using the instruction "arrParts = Split(Nz(me.OpenArgs, ""), "¦")".
A tracer Message Box confirms that "arrParts" is being filled properly with the values in "OpenArgs" (which are also displayed in the successful printouts).
I am really puzzled because as far as I am aware, this is a much simpler Report than most of the others in my application that generate a PDF file without a problem.
Any further thoughts?
I stripped down my report to bare minimum and re-built it step by step. It has "Report Header", "Page Header", "Detail" and "Page Footer" sections, all of which contain 0nly "Unbound" controls which I fill out in their various "On Format" event sub procedures.
The problem appears to start as soon as I try to assign values (fed in as separated strings in the single Report's "Open Arguments" string) in the "ReportHeader_Format" sub procedure (the first section I tried to fill). The values "print" OK, but the PDF version of the report does not get saved. Without my attempts to populate the controls in "Report Header_Format", the "empty" report prints OK and gets saved as a PDF OK as well. I have tried using the "ReportHeader_Print" sub procedure instead, with the same result : prints OK but no PDF saved.
I am using an array variable "arrParts as String" which I fill in "Report_Open" using the instruction "arrParts = Split(Nz(me.OpenArgs, ""), "¦")".
A tracer Message Box confirms that "arrParts" is being filled properly with the values in "OpenArgs" (which are also displayed in the successful printouts).
I am really puzzled because as far as I am aware, this is a much simpler Report than most of the others in my application that generate a PDF file without a problem.
Any further thoughts?
Further interesting development!
I was using the "DoCmd.OpenReport strReportName, acViewNormal, , , , strOpenArgs" command because this report is being generated overnight without any operator interventions.
When I replace "acViewNormal" with "acViewPreview" (plus a couple of other extra instructions) as below, the PDF file gets saved OK with the "Header" fields populated OK.
DoCmd.OpenReport strReportName, acViewPreview, , , , strOpenArgs
DoEvents
ReFocusOnReport (strReportName)
DoCmd.RunCommand acCmdPrint
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
DoCmd.Close acReport, strReportName
This is still puzzling because the "acViewNormal" instruction works OK with other reports that also create a PDF file.
Is there another way I can force the report to print to the default printer without needing an operator present?
I was using the "DoCmd.OpenReport strReportName, acViewNormal, , , , strOpenArgs" command because this report is being generated overnight without any operator interventions.
When I replace "acViewNormal" with "acViewPreview" (plus a couple of other extra instructions) as below, the PDF file gets saved OK with the "Header" fields populated OK.
DoCmd.OpenReport strReportName, acViewPreview, , , , strOpenArgs
DoEvents
ReFocusOnReport (strReportName)
DoCmd.RunCommand acCmdPrint
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
DoCmd.Close acReport, strReportName
This is still puzzling because the "acViewNormal" instruction works OK with other reports that also create a PDF file.
Is there another way I can force the report to print to the default printer without needing an operator present?
Do you have code in the Open() event?
Code in the Open() event does not run (sometimes none of it, sometimes only portions of it) if the report is opened to print via "acViewNormal"
This is an old issue, ongoing since at least A2003.
The only way to get the report to do what you wanted was to open in Preview and then print.
A very recent discovery is that, if you place a dummy global variable in a code module
Global myDummy as Boolean
And touch it in the Open() event, all the code will execute
MyDummy = False
Is there another way I can force the report to print to the default printer without needing an operator present?
What in the code block I posted
requires an operator?
It should preview, print, PDF and close a report when the code is executed.
Code in the Open() event does not run (sometimes none of it, sometimes only portions of it) if the report is opened to print via "acViewNormal"
This is an old issue, ongoing since at least A2003.
The only way to get the report to do what you wanted was to open in Preview and then print.
A very recent discovery is that, if you place a dummy global variable in a code module
Global myDummy as Boolean
And touch it in the Open() event, all the code will execute
MyDummy = False
Is there another way I can force the report to print to the default printer without needing an operator present?
What in the code block I posted
DoCmd.OpenReport strReportName, acViewPreview, , , , strOpenArgs
DoCmd.SelectObject acReport, strReportName
DoCmd.PrintOut acPrintAll, , , , 1, True
DoEvents
If Not genAllBlanks(strArchiveFile) Then
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strArchiveFile
End If
DoCmd.Close acReport, strReportName
requires an operator?
It should preview, print, PDF and close a report when the code is executed.
Thanks, Nick67.
I am sorry I had overlooked your "acViewPreview / DoCmd.PrintOut" suggestion from your first posting. It has solved my problem.
The "acViewNormal" method had worked for me in every other instance where I did not need any operator intervention, and I had always used "acViewPreview" in combination with a separate "acCmdPrint" when I did want the operator involved.
I was so focussed on the internal formatting of my current report that I didn't think the way the report was called would make any difference.
In nearly all my other reports (and presumably all the ones that also get saved as a PDF) my "Reopert_Open" sub procedure does a lot more work than my current problematic report. Usually "OpenArgs" contains the "select", "when", "order by" clauses that I use to construct a report's "RecordSource" in "Report_Open". I usually call a "global" sub procedure to help with that, presumably sufficient to trigger the type of "global" activity you mentioned in your last comment.
Anyway, problem now solved. Thanks very much. Even after several years of using Access / VBA / SQL Server / T-SQL, I realise I am just scratching the surface of their features and idiosyncracies.
Cheers. Colin.
I am sorry I had overlooked your "acViewPreview / DoCmd.PrintOut" suggestion from your first posting. It has solved my problem.
The "acViewNormal" method had worked for me in every other instance where I did not need any operator intervention, and I had always used "acViewPreview" in combination with a separate "acCmdPrint" when I did want the operator involved.
I was so focussed on the internal formatting of my current report that I didn't think the way the report was called would make any difference.
In nearly all my other reports (and presumably all the ones that also get saved as a PDF) my "Reopert_Open" sub procedure does a lot more work than my current problematic report. Usually "OpenArgs" contains the "select", "when", "order by" clauses that I use to construct a report's "RecordSource" in "Report_Open". I usually call a "global" sub procedure to help with that, presumably sufficient to trigger the type of "global" activity you mentioned in your last comment.
Anyway, problem now solved. Thanks very much. Even after several years of using Access / VBA / SQL Server / T-SQL, I realise I am just scratching the surface of their features and idiosyncracies.
Cheers. Colin.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial