Close open reports first when exiting MsAccess

[AshikHusein]

I ahve a situation where I need to close all open reports first when I exit MsAccess.exe. I should not have to specify which reports are to be closed (because all open reports have to be closed first. How could this be done if it is possible to do so? Thanks.

 

[Fizzio]

[b]In a Public Module[/b]

Sub CloseAllReports()
Dim dbs As DAO.Database, ctr As Container, doc as document
Set dbs = CurrentDb
Set ctr = dbs.Containers("Reports")

'Loop through reports
For Each doc In ctr.Documents
     if IsReportOpen(doc.name) = True then
          docmd.close acReport, doc.name
     End If
Next
End Sub

Public Function IsReportOpen(strRptName As String) As Boolean
IsReportOpen = (SysCmd(acSysCmdGetObjectState, acReport, strRptName) <> 0)
End Function

Run the procedure from vba using

Call CloseAllReports

 

[AshikHusein]

How do I use the above code as an event procedure? Like for example I need to activate this code when MsAccess is closed if a user clicks on the X button. Thanks.

 

[AshikHusein]

I am working on this project which pulls up reports based on a table(say table1) which is created when a certain form is opened. The table is deleted when the form is closed..

The report buttons are on the form.

Now if a report is open and the end user closes MsAccess without closing the report, there is an error saying that the table1 cannot be deleted because it is being used by another application. That is because it first tries to close the form (which has an event procedure to deletes the table1) and it cannot do that because the reports are still open. So I need to close the reports on exiting MsAccess. Thanks.

 

[Fizzio]

Why are you creating tables on opening a form and then deleting them on the form close? sounds like an inefficient way of creating a form/reports recordsource. You can hide the access main window so that the close button is never accessible but I would look at why you are needing to create and delete a temporary table first though.

 

[AshikHusein]

The reports are are based on multiple queries. The sequence is something like the following:

Table1->Query1->Query2->Report

Now the problem that I was initially having was that the report does not work for the filtered data (but for all data even though it is filtered). So if I want the reports for say Montreal only, it is not possible because the although I filter the table for the query sequence and the report, the report does not base itself on the filtered table. Now there are about 30 to 40 reports. So the best way I thought of doing it all (although very violent and bloody!!) is to create a tempoaray table, delete it for fields not required, run the reports and delete the temporary table when not required.

If there is a better way please let me know since it will simplify things to a very great extent. I am basically trying to standardize the queries and make the table to be variable, as per needs.

The report generation may go upto tables for upto a 100,0000 records and I hope it does not mess up!!

 

[Fizzio]

Base your report on a query then. You can then specify what fields to include and add any criteria you wish. I think your diagram table->qry1->qry2->report, does initially seem to indicate that you are basing the repory on the second query but I'm not sure that is how you have set it up. Get back if I am wrong.

 

[AshikHusein]

The report is based on qry2 as per your diagram. There are different query sequences for each report and there are about 11 initial reports. Now for Toronto and Montreal there are 11 reports each ( for 22 more so a total of 33).

So effectively as per your query sequence I would pull three reports from qry2 (for National, Toronto and Montreal).

Now the way that I do that is that I take the main table copy, it to a temporary table, delete the fields not required( as an example I delete the fields for Montreal for the Toronto reports) and use qry2 to pull the reports.

So then the managers have to just use the buttons to go to different forms (for Toronto, MOntreal or National) and click on the specific report button.

I had this filter problem when I started doing this project but from the replies I got there was no way to filter the data in the main table first and then pull up the queries. So this drastic way was the only solution for me (of creating and deleteing a temporary table)

Now what I find is that if somebody closes MsAccess by mistake when a report is open, they get an error saying that the table is locked (obviuosly because it is being used by the report) and that it cannot be deleted. You have to click on "halt" to "halt" the macro and then the database closes but if it is used again the temporary table is not deleted causes strange messages (which I can understand but it does not create a user freindly impression).

You must have relaized by now that this is my first ACCESS project and I am sure there is a better way to do it. Thanks.

 

[Fizzio]

I think you are using your queries inefficiently. In a query, you can pick and choose whichever fields you want to show from the table and set whatever criteria on any of the fields you include in the query (whether you show them on your report or not).

It may be easier for you to post a cut-down zipped version of your Db here as if you do not get the basics right, youll be paddling upstream and not go anywhere!

 

[AshikHusein]

I could not send the file because I dont have the program which can zip a file since it is not required for the work we do.

But just one last question to clarify matters.

When I program a macro to open reports there is a "Where" condition at the bottom. Is this the place to specify the filter conditions for a specific field in the final query from which the report is drawn? Thanks and I appreciate your help in this matter.

Also please let me know of a way to hide the close button for the ACCESS window. Thanks again.

 

[Fizzio]

The WHERE condition does not filter the query that supplies the report, but filters the report itself.

I still think you need to change your approach to this problem as getting into bad habits early will make future developments harder and cause you to run into problems with this current project.

If you want to hide the close button, you will need to use API calls to manipulate the Windows windows.

 

[AshikHusein]

Thanks for all your help. What I am going to do for the time being is run your report closing VBA code on the event that the form closes. I will add in the code to delete the table within your code. This should be sufficient for the time being so that I can get the project on time.

In the meanwhile I am also working on more efficient ways to use the query. After all this experience I am a lot more clear on what can be done or not done in Ms Access.

The centre fields (Ie National, Toronto, or Vancouver) were not required within the bodies of the reports. So initially when I started desiging the queries, if I added the centre" field, it would start sub grouping on the centre field also( which is what I did not want it to do). From my experience if I put in the centre field after all the other fields it does not sub group for the "centre" field.

So I wil take this approach and hopefully should get some desirable results! Thanks again for your help and if you do hav eany further insights for me please do write back.