Macro condition to check if report is already open

[MarvinM]

My users are clever enough to find problems that I'm not smart enough to solve.
I'm using Access 2007.
My macro runs a query and opens a report. If the user closes the report and goes back to the form, they can choose a different report, and everything works fine.
However, my users will open several reports at a time. They use the document tabs to go back to the form instead of closing the reports. Eventually, they click the button again for a report that is already open. When this happens, an error occurs. A query cannot be run when it is in use by an open report.

At first, I thought the easy solution is turn off those document tabs. I've searched the forums a whole day and that appears to be impossible to do with VBA.

So my next idea is to put a condition in the macro to check to see if the report is already open. I assume that the condition would look something like this: Reports![Qtrly Sales].Open = True
Again, searching the Access help and forums for another day finding nothing.

I remember seeing something about this a few months ago, but of course, can't remember where. Does anyone know how to do this check? Thanks!

 

[Mr. B]

I do not think you will find an option to check for an open report when only using a macro. However, you can create or use a user defined function (VBA code) that will do the checking. You can then call the function using a macro.

Take a look at this link:
http://www.devhut.net/2010/06/10/ms-access-vba-check-if-a-report-is-open/

It shows a function that you can call with a macro. You will need to add the Function code to a module and add "Public" to its definition so you can call it with your macro.

Public Function IsRptOpen(sRptName As String) As Boolean

Hope this helps.

 

[EternalMyrtle]

You can use the following VBA code to check if a form is loaded:

CurrentProject.AllReports("YourReportName").IsLoaded = True

Having said that, you would perhaps be better off preventing users from opening multiple reports at the same time in the first place. So maybe when the user goes back to the form, you can program Access to automatically close the report.

 

[EternalMyrtle]

Or even simpler: you can make the report modal so that the user cannot click on the form. S/he will have to close the report to go back to the form.

 

[MarvinM]

Wow. Thank you. All good answers. I like the modal idea. The automatic close is good too. But I know that the users will insist that they need to have two (or more) reports open at a time so that they can go back and forth to make comparisons. I think I can make my forms modal to prevent them from clicking back to the main menu when they have the report menu open. That will be a big help. What I will need to test is if the report menu form is modal, and then I open a couple of reports that are not modal, will I be able to click back and forth between those two reports and the report menu form? It sounds like it should work since the reports are being opened after the modal form, their z-index should be higher, right? I'll test that.
I'll try Myrtle's code first to see if that will work as a condition. If not, then I'll go to Mr. B's function plan. Thank you both!

 

[MarvinM]

O.K. I have a solution.
The code from EternalMyrtle works. I put this in as a condition for the OpenQuery action in my macro:

CurrentProject.AllReports("Qtrly Sales").IsLoaded = False

If the report is not open, the query runs, and there is no locking error.
The next line of my macro is an OpenReport action. If the report is already open, the focus just moves to that tab. That's exactly what I needed. I'll put that condition in for each report query.
Thank you!

A follow up on the modal idea. When I make the report menu form modal, it does prevent the users from clicking on the document tab to go back to the main menu. However, when a report is opened, it opens behind the modal form. The z-index is lower.
So if I want the reports to come to the top, I would need to make them modal too. But then, the users wouldn't be able to go back and forth to make their comparisons. I'm just going to have to give up on the modal idea.

Maybe I can disable the main menu when the report menu is opened. I'll give that a try, but I won't post any more about that here as it would be so far off topic that it should be a different thread. I'll search the forums for "disabling forms".

Thank you all again.

 

[EternalMyrtle]

Unfortunately, I don't think making the form modal (instead of the reports) will work.

How are your users running the macro that writes the query and opens the report?

To implement the code I suggested, it will probably make the most sense to switch the macro to VBA. If you don't want to switch to VBA you could do as Mr. B suggested: write the public function to check if the report is already open and call it in your macro using RunCode. If the condition is true, you would close the report or display your message box.

All in all, I think switching the macro to vba would be better in the long run.

 

[EternalMyrtle]

O.K. I have a solution.
The code from EternalMyrtle works. I put this in as a condition for the OpenQuery action in my macro:

Great! I didn't think you could do it with a macro but glad it worked!

 

[MarvinM]

Users have a Report Menu form with a dozen buttons on it. They push the button for the report they want. Embedded Macro behind the On Click event has 4 actions
. Turn Warnings Off
. OpenQuery runs a make table query
. OpenReport opens a report bound to that table
. Turn Warnings On

It's pretty simple, but I can see how getting parameters and building a query will be much more useful. I have to deliver the simple version right away, but I will be able to make enhancements to it over the next couple of months. Thanks for your ideas.