Best Way to Automate Printing of Report

[CreativeDavid]

Long time reader of this forum, and had lots of help from people here so want to start out by thanking you all for your help. Believe it or not I have been able to get around near every single challenge with your help in past posts.

I have been making a kind of hybrid access mix. It is driven primarily by stored procedures I have built in SQL, and Access is acting as the forms interface and in this case report generator. This is working well for the most part and achieving great outcomes given how complex some of the queries are. Please I don't want to be drawn off into discussions on using pass through queries and so on, I have been working on this for many years and this is not my first iteration of this specific tool.

Todays challenge is I have a report that I am populating from the ADO recordset. I know this cant be done but I have worked around this limitation. The code works great as long as I load the report in Preview. The negative with this is someone has to push a button or click a mouse to print the report. When I change it to Normal and print the output manually as a PDF (I don't own a printer anymore) the report is blank. I guess I need to add a command to tell the report to print and then to close. But just in case someone here has a better idea...Here is the code:

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

On Error GoTo cssRptLoad_ErrHandler:

Set rst = New ADODB.Recordset

Set cmd = New ADODB.Command

'Check connection is open
    If gcn.State = 0 Then Call cssOpenConnection
   
      cmd.ActiveConnection = gcn
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "mysp_ProofofJobScott"

      'Your parameters
      cmd.Parameters("@StartJob").value = 1234
      cmd.Parameters("@Cmonth").value = "February 2020"

      Set rst = cmd.Execute
               
        g_pFormName.cssRecord = rst
     
      Do Until rst.EOF
     
        DoCmd.OpenReport strReport, acViewPreview, , , acDialog, 1
       
        'Wait if I need to add it.  The above is a bit of a pain as someone has to be there
               
        rst.MoveNext
   
    Loop
   
    Exit Sub

As I said I can code to print and close and then move on, but it seems more likely there is a simpler solution.

 

[Jon]

Quick question: what made you decide to join up today?

 

[pbaldy]

I'd try taking out the acDialog argument, add

DoCmd.OutputTo

with the appropriate arguments after, then close the report.

 

[CreativeDavid]

Quick question: what made you decide to join up today?

Been playing with this for four days. Its been a very large struggle for me to grasp how to work around the limits on the application of the SP recordset into the report as the simplistic approach doesnt work.

The approach I finally settled on was driven largely by this forum. Every search of google, every time I looked into this I was lead to this forum some posts as far back as 2012. I have seen lots of help and had a lot of help by simply lurking.

But today is a public holiday here. I am nearly ready to roll production on the first stage of a very large project 20 years in constant development and I can smell this is the final iteration. My skill sets, my knowledge, the tools already in place and most importantly funding (as there is a reasonable amount required for some other tools) are all in place.

The issues I am taking on are best explained in what I have termed the Airport Conundrum. I choose airports to explain it as it is easier to conceptualise than the esoteric area I am working in, but it gives a good insight into what I am broadly doing.

First rule of programming being not to program...as cant make errors if I dont write anything. I did spend considerable time in two major revisions of all available service management packages available once in 2011 when I first moved to cloud based systems and again in 2017 when my funding arrangements changed. Both times non packages came close for under $30,000 that I could find at that time. I then went towards ASP.Net and started learning that language with an intent to move towards this type of solution, however it proved too challenging to move, I can still struggle in ASP, but prefer the rapid application environment of Access.

I hope this answers the question...I may not be the most prolific poster but I do read a considerable amount before deciding an approach...and this particular one has taken nearly a week of reading to work through.

 

[Jon]

Thank you for your comprehensive reply. I am pleased you joined up. Everybody here is really helpful and it can save you a ton of time trying to figure things out by yourself.

 

[CreativeDavid]

I'd try taking out the acDialog argument, add

DoCmd.OutputTo

with the appropriate arguments after, then close the report.

I hadn't thought of using that. Its amazing the acViewNormal doesn't work. It prints out blank files. Perhaps this approach wont?

 

[pbaldy]

I hadn't thought of using that. Its amazing the acViewNormal doesn't work. It prints out blank files. Perhaps this approach wont?

Obviously I can't test your situation, but typically OutputTo will accurately output the report that's open, so if the preview is correct I'd expect the PDF to be as well. It's a common way to output a report filtered with a where condition to PDF.

I would have expected sending directly to a PDF driver to work as well. I just did that to test a process that normally prints out. Being offsite I didn't want to waste paper that I couldn't look at anyway so I used cutePDF to print to PDF.

 

[CreativeDavid]

I'd try taking out the acDialog argument, add

DoCmd.OutputTo

with the appropriate arguments after, then close the report.

Tried that...same issue. Loads of blank reports. Iterates through correctly. I get the distinct impression there isnt enough time for the code to run to populate the report. As I iterate over the controls and tell them to bind to the recordset.

Before deciding to post I was going to add a timer in to allow the report to populate and see if that fixed this issue...,perhaps I go back to that idea?

 

[pbaldy]

The code is all inside the loop? You could try adding DoEvents between the lines:

DoCmd.OpenReport...
DoEvents
DoCmd.OutputTo...
DoEvents
DoCmd.Close...

 

[The_Doc_Man]

I have no idea how much data you are describing, but the only common solution I have found in my online searches include the idea of creating a DAO table as a temporary intermediate (perhaps via MakeTable) and driving the report from that temporary table. This opens up other cans of worms regarding the need for compact & repair on the DB file that was used to host the intermediate.

Some theories I saw online suggest that despite the appearance of having a workaround, you really cannot do this sort of thing because the ADO recordset vanishes too soon (i.e. what is sometimes called as RACE condition). If that is true, you are losing the race. So the solution of making the ADO recordset into a DAO recordset, then deleting the table when done, might get you past the first hump.

If you want to go that way, there is the hump of a database that gets bogged down by garbage left over from previous runs. If you do not want to choose or at least try that approach, there is no point in describing the second hump. Therefore, if you don't want to do that, I won't bore you with the details.

 

[CreativeDavid]

I have no idea how much data you are describing, but the only common solution I have found in my online searches include the idea of creating a DAO table as a temporary intermediate (perhaps via MakeTable) and driving the report from that temporary table. This opens up other cans of worms regarding the need for compact & repair on the DB file that was used to host the intermediate.

Some theories I saw online suggest that despite the appearance of having a workaround, you really cannot do this sort of thing because the ADO recordset vanishes too soon (i.e. what is sometimes called as RACE condition). If that is true, you are losing the race. So the solution of making the ADO recordset into a DAO recordset, then deleting the table when done, might get you past the first hump.

If you want to go that way, there is the hump of a database that gets bogged down by garbage left over from previous runs. If you do not want to choose or at least try that approach, there is no point in describing the second hump. Therefore, if you don't want to do that, I won't bore you with the details.

Appreciate the thought. Part of the approach I am taking is avoiding (Access) tables completely. I am unsure if this will be completely successful, but so far with a lot of thinking in between, I have been successful.

I am persisting the recordset via a class. In testing to date this is working well. I don't know if I have ham fisted found a method or ...

 

[CreativeDavid]

The code is all inside the loop? You could try adding DoEvents between the lines:

DoCmd.OpenReport...
DoEvents
DoCmd.OutputTo...
DoEvents
DoCmd.Close...

Not quite but close enough...

I ended up opening the report in acViewPreview from the form.

Then in the onactivate method of the report I run the output to and this achieves exactly what I am aiming for.

So in brief for those interested in words...

I set up an SP to perform the work I normally do by hand of generating jobs each month. The SP adds the jobs to the open jobs file and sends the new job list back.
I push the job list into a recordset with ADO
Then send the recordset into a class for holding (as I under stand it the recordset is in fact a pointer not the recrodset)
I then iterate teh report controls which I have set up using a naming convention populating them in the Onload event from the recordset in the class (this only works in Preview)
Then output via the docmnd.Output to the file I want to put them in so I can print later

Thanks very much all that helped.

 

[pbaldy]

Glad you got it sorted. With my code, were you not opening the report in preview mode? That was my intention.

 

[CreativeDavid]

Glad you got it sorted. With my code, were you not opening the report in preview mode? That was my intention.

I was. But had the output too in the wrong location. It was creating mayhem till I moved to on activate.

Thanks again