Problem suppressing the printing of Access report (from Excel)

[Thebatfink]

Hi, I am scratching my head. For a long time I've been sucessfully printing an Access report via Excel. Recently I have requirement to now export this to PDF instead, so ultimately I am trying to call an Access report to open from Excel, passing a where clause and then export that report to PDF. It seems DoCmd.OutputTo can't take a clause and the solution I keep seeing is to open the report in print preview first and then export it with OutputTo.

My problem is really two fold, but I don't know if the first issue is causing the second. When I call the report, it is still immediately trying to print, but my understanding of acViewPreview is that this should not be the case. This is using MS Access 2013 and the database is mdb format. Here is my code, this is the sub in its entirety:

Private Sub Test()
Dim objAccess As Object
Set objAccess = GetObject(, "Access.Application")
Dim stdocname As String
stdocname = "Specification"
Dim mnumber As String
mnumber = "166767"
With objAccess
    .DoCmd.OpenReport stdocname, acViewPreview, ,"[SPECIFICATION]   ='" & mnumber & "'", acHidden
    .DoCmd.OutputTo acOutputReport, stdocname, acFormatPDF, "C:\Temp\TestReport.pdf"
    .DoCmd.Close acReport, stdocname, acSaveNo
End With
Set objAccess = Nothing
End Sub

First Issue

.DoCmd.OpenReport stdocname, acViewPreview, "[SPECIFICATION]   =""" & mnumber & """", acHidden

This is always instantly printing the report, but I read acViewPreview should not cause this? I do have code in my report for Report_Load and Report_Open but all this does is unhide a label on the report based on the value of a field. I can not think of anything else which could be triggering the print. What am I doing wrong here? Here is the report code for clarification:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err
DoCmd.SetWarnings False
If IsNull(Me.[OPERATIONS MGR]) Or IsNull(Me.[PROCESS DEVPT]) Or IsNull(Me.[QA MANAGER]) Then
    Me.Label239.Visible = True
    Me.Label209.Visible = True
Else
    Me.Label239.Visible = False
    Me.Label209.Visible = False
End If
DoCmd.SetWarnings True
Exit Sub

Err:
DoCmd.SetWarnings True
MsgBox Err.DESCRIPTION
End Sub

Second Issue

.DoCmd.OutputTo acOutputReport, stdocname, acFormatPDF, "C:\Temp\TestReport.pdf"

From my original sub above, after the printing happens (which I am trying to stop), this is causing an 'Output To' prompt to appear in Access asking what type of file format to export to, even though I passed the acFormatPDF argument? But I don't know if it's something to do yet with the previous line printing when not expecting it to.

Any guidance would be much appreciated! Thanks.

 

[Micron]

I can't see anything wrong with the code. Suggest you step through it to ensure the execution is what you think it is, which might also point out if it's a timing thing since doing so will introduce a slight pause between open and output. If that turns out to be the case, you can add a call to a timer function and inject a 1 or 2 second pause between open and output.

 

[Thebatfink]

Thanks for taking a look. I guess you specifically mean the OutputTo line. I've had breakpoints on all the lines, behaviour does not change. I did also follow the manual OutputTo prompt in Access and selected PDF and it complains about there being no object "|1", so I think the OpenReport is not working right and that seems also evident by the printing which shouldn't be happening. Even if I change OpenReport to acWindowNormal I don't see the report appear in Access - I'm not sure if I should or not though.

 

[Micron]

If your break points resulted in a pause between open and output lines, then I guess you can rule out the need for a pause. I actually meant at the beginning and step through, checking that things are doing what you expect. One would have been to determine that the open line actually opens the report because if it doesn't, then the output line will open the report and not only would I expect it to open and print, it won't open filtered either. Maybe put break point on output line so that it won't execute that line and insert between open and output lines:
Msgbox CurrentProject.AllReports(stdocname).IsLoaded

If the resulting message is False, then the report didn't open. Or you could remove the hidden factor and see if the report opens.

 

[Thebatfink]

I’ll try the isloaded. I thought I was clear, but maybe I am not. When I say ‘I’ve had breakpoints on all the lines’ it literally means all the lines, so code is not executing until I continue it in Excel after each line. I execute a single line of code and then go watch watch happens in Access, then I got back to Excel aftwerwards and manually trigger the next single line of code to execute. Maybe my terminology is not great.

The first lines in my sub are creating the variables and writing values to them, it is not doing anything that is visible (other than I could verify the variables are holding correct values which they are)..

Execution of code in Excel now stops at the break on the first DoCmd in Excel waiting for me to begin execution of that line.

First DoCmd (the Open_Report) runs and does not cause a report to visibly open in the access database, but it does cause the print dialog to appear, and it automatically starts to send both pages of the report I called in that line to the physical printer. It did also print the report with the record from the where clause I passed as argument. But I use acViewPreview so something is wrong as this is supposed to open the report and NOT print. Again, once the print completes, the prompt goes away, the report is not visible in the Access window. I now have an unexpected physical print out on the printer.

At this point we are break at the second DoCmd back in Excel waiting for me to begin execution of that line. I begin execution manually on the second DoCmd line of code.

The second DoCmd (OutputTo) causes a prompt to appear in Access with a list of filetypes for how it should export (even though I passed acFormatPDF in this line of code). I choose PDF in the export prompt within Access and continue, but Access throws an error saying it can’t find object ”|1”. No file exists in C:Temp. No file gets exported. No report is visibly opened in the database.

At this point code fails at this line in Excel due to the error in Access..

Result is, a reported printed to physical printer when it wasn’t expected to. No report ever becomes visible inside the access database screen. No file ever got exported Automatically. OutputTo causes a filetype selector to appear in Access instead of exporting a report. Following it manually with mouse (not code execution) choosing pdf, generates MS Access error no object “|1”. Excel sub fails to complete.

To your last point, again sorry maybe I was not clear in my previous post, I did change the argument to acWindowNormal which will result in it not being hidden, and as I said, the report is not visible in Access.

 

[Micron]

Sounds like you covered all the bases, and the code looks correct, and you are correct in that opening the report in preview should not cause it to print out, and the output line should output the filtered report that should already be open. I'd try executing these lines in an Access module and see what happens there. If it behaves as expected, your solution might be to call the Access code from Excel.

Lately, M$ is breaking Access each time they issue a new release, which was meant to fix the problems introduced in the prior release. Maybe this is just another thing that worked for years and gets broken - seems like it's happening every month now.

 

[Thebatfink]

So I would like to thank you. Firstly for your patiance but also though we did not directly find the solution, you helped direct my thought process and I eventually got to the solution. I first tried each of these individual commands in Excel within single subs and again in Access module. I found that in Access it works 100% as expected. In Excel, always report is opened but immediately printed. In Excel export is triggered (displaying the file type dialog) but never it happens automatically. All work fine in Access.

It felt like some of the arguments are not getting passed or interpreted correctly as the basic functions themselves are being triggered in Access (it does open the report and it does start the export) but it's almost as though it is ignoring my additional arguments.

That set me on a different path with google and I stumbled on one guide with a screenshot of the references window and then it dawned on me.. I have not set references in the VBA project.

I never thought to because the subs ran without error. However when I add Microsoft Access 15 Object Library to the project - bang, it starts behaving properly and now calling the commands in Excel behave the same as when called from within Access itself and all the arguments are being read and actioned.

So problem solved, the code was indeed all correct - don't be stupid like me and forget the project references!

Thanks

 

[Micron]

I took it for granted that you had the Access reference if you knew that much about automation - my bad. I would not expect to control another Office app without setting a reference to it, but then I don't think I ever tried. Any automation code I've ever borrowed set a reference to the other app library. Glad you solved it.