Report not hidden when using acHidden with Docmd.OpenReport

[anymuon]

Running Office 365 enterprise

I have a form that narrows down the reports for field staff. It allows them to set the report query filter, which is a parameter query (2nd line in code below). I can't change the use of SetParameter or I would just change the report source and change it back. It is not my database. They can also choose their output format, save as pdf, view on screen and print to printer. There is a 10-20 sec delay while querying the database since it is an Oracle backend, over VPN and I am remote.

The code executes fine, except for the report being visible. I thought the whole purpose of acHidden was to not be seen.

I'm running the snippet of code to save as pdf and the report is not hidden:

    strpdfsave = strfilepath & strrpt & ".pdf"
                DoCmd.SetParameter "[Enter PWSID (Use ""IN"" + PWSID)]", Chr(34) & vid & Chr(34)
                DoCmd.OpenReport strrpt, acViewPreview, , , acHidden
                DoCmd.OutputTo acReport, strrpt, acFormatPDF, strpdfsave, False
                DoCmd.Close AcReport, strrpt

A couple of screenshots, 1st one is after OpenReport while querying the backend and before OutputTo. The second screenshot is after OutputTo.

 

[Gasman]

Why would you put preview AND hidden?

Edit: I have just tried that combination and it is quickly visible and then not? so something else going on there.
Edit:Edit: I changed the DB to Tabbed Documents and it still remains hidden.?

 

[Pat Hartman]

I thought the whole purpose of acHidden was to not be seen.

Access has to actually open the report. Perhaps the slow connection is why you see the report render before it gets hidden.

This is an inefficient way to open the report. It is far better to have the report get the argument from a form control. But, if it is not your app, you can't change how it works. You could suggest the option though.

If you are only opening ONE report, the efficiency aspect is irrelevant. However, if you have to create multiple reports, you will see a time savings if you can use the other method which is to have the report be bound to a query where the query has criteria that gets a value from a form control.

Select ... From .... Where Pswd = Forms!someformname!somecontrol

 

[anymuon]

Why would you put preview AND hidden?

Edit: I have just tried that combination and it is quickly visible and then not?

Riiiggghhhht! EXACTLY. I wonder why this is a thing, a PITA thing.

Can't use acViewReport or acViewNormal with DoCmd.SetParameter.

The SetParameter string is in the report source query. There are a couple of hundred reports using this format.

I have just tried that combination and it is quickly visible and then not? Mine are not, hence the question.

 

[Pat Hartman]

Why would you put preview AND hidden

That's the only way you can use variable criteria with the OutputTo method. It doesn't have the same options that OpenReport does.

 

[Gasman]

Riiiggghhhht! EXACTLY. I wonder why this is a thing, a PITA thing.

Can't use acViewReport or acViewNormal with DoCmd.SetParameter.

The SetParameter string is in the report source query. There are a couple of hundred reports using this format.

I have just tried that combination and it is quickly visible and then not? Mine are not, hence the question.

Well TBH I hought one conflicted with the other?, but it works for me, so perhaps just a timing issue?, or does it not disappear at all?

Edit: Seems no need for preview?

Redirecting

 

[Gasman]

That's the only way you can use variable criteria with the OutputTo method. It doesn't have the same options that OpenReport does.

Never knew that Pat , thank you.

 

[anymuon]

Pat:
This database goes back to 1997 and uses mostly macros with limited vba coding. Coding has been patched but not really updated. They have over a couple of hundred reports plus that many queries. Field staff prints out 5-30 reports, depending on site and staff, and has to supply the parameter (PWSID) with each one. I was asked to simplify that process. I run this against a table that stores the report name and the group. I would change the report source query, but any glitch that interrupts code execution you get the changed report source. I ran into this with testing and couldn't come up with a good way, even in an error handler to change the report source back to the parameter query.

That is what I do on my form:

 

[Pat Hartman]

Here are two samples based on a similar idea. One of them supports batch processing so you can select several reports at one time and then press print. The other does them one at a time but has more sophisticated selection criteria. Neither actually works because the reports are all part of a different application. All that is included is the "structure" to show how the solution works. You would need to import the forms, queries, and tables into your app and then modify them to suit your requirements.

To automate the process further, you could create a group table that lets you pre-select groups of reports so you don't have to select them each time you want to print them. AND, you can go even further and create macros that can be used to print a group and that will allow you to create a windows task that opens the database specifically to that macro so you can run group A on monday night and group B on thursday night. Just make sure the macro closes the application when it is finished.

 

[isladogs]

I'm using similar code to generate a table of contents (TOC) in a report with 9 optional subreports.
The report is output to PDF (or text file) so Access goes through each page in turn to generate the TOC then the report is opened properly in print preview

That works fine as long as all 9 subreports are used but gives spurious page numbers when 1 or more options are excluded.
That is because using OutputTo means report code doesn't run

To fix that, I first need to open the report in print preview (hidden) to run the code which hides the subreports & page breaks for options not in use.
Doing that works fine - the hidden report doesn't appear and the TOC gives the correct values.

 

[anymuon]

Here are two samples based on a similar idea. One of them supports batch processing so you can select several reports at one time and then press print. The other does them one at a time but has more sophisticated selection criteria. Neither actually works because the reports are all part of a different application. All that is included is the "structure" to show how the solution works. You would need to import the forms, queries, and tables into your app and then modify them to suit your requirements.

To automate the process further, you could create a group table that lets you pre-select groups of reports so you don't have to select them each time you want to print them. AND, you can go even further and create macros that can be used to print a group and that will allow you to create a windows task that opens the database specifically to that macro so you can run group A on monday night and group B on thursday night. Just make sure the macro closes the application when it is finished.

Quite a nice piece of work. I'll keep this in my code library. Had quite a few ideas in it. Nice to have a database that is a little more rigorously built.

I had a feeling there was maybe a timing issue - why I included rough execution time. I do have a couple of areas I can optimize some code, especially on the PWSID check. I try to fix some simple issues with the number and check the database for validity. I hit the check 3x and could be slowing things down. More of an annoyance right now, other than not being hidden. I'm going to wrap some timers in and test home remote vs work remote plus do just a number pull into a recordset on form opening and validate against that. Should be faster even though the PWSID is indexed. I will update when I get to it.

 

[ebs17]

The real problem above is the parameter passing.
I show two ways how the query as the report's data source gets this information before the report is handled and used. This is quite interesting, for example, if you are dealing with sub-reports and you have to consider that sub-reports are loaded before the main report. In any case, you make yourself independent of events in the report and the resulting dependencies.

1) Using a parameter table
An additional parameter table with one field per parameter is built into the query statically.

SELECT D.* FROM tblData AS D, tblParameters AS P WHERE D.Field1 = P.Parameter1

Values in the parameter table are replaced before calling the report. With only one record in the parameter table, there is no undesired data duplication through the CROSS JOIN.

2) Changing the query definition by code

' qryReport is a saved query and fixed data source of the report
CurrentDb.QueryDefs("qryReport").SQL = "SELECT * FROM tblData WHERE Field1 = " & IntegerParameter

After using one of the operations shown, OutputTo can be used immediately.

 

[isladogs]

Interesting ...that will be relevant to the OP but I think not to my related example of creating a TOC based on subreports.

As I knew subreports open before the main report (as with subforms), I had hoped I could avoid the extra step described in post#10 when creating the TOC where some subreports are not used. In practice, I found the extra step was required

 

[Pat Hartman]

Quite a nice piece of work.

Thanks. Every application gets some version of what is in those two samples. Usually it's the version that hides/shows the various parameters. It's funny. Lots of reports have specific criteria (including the ones you see listed) and can get pretty complicated but for every application that has had enough reports to need a report manager, the number of variables is actually quite manageable.