Solved: Execute Queries and reports automatically

[austenr]

Hope someone can help me here. I have several queries that I run monthly. From those queries, I produce a report. Is there a way to run the queries and report automatically at a designated time each month? There are four queries and from those queries i pull data for 2 reports one summary and one detail. Any ideas would be appreciated. Thanks as always

[OBP]

austenr, yes you can do this with VBA which would require the Database to be opened on a specific day of the month. This can be done using Windows Scheduler or if you open the database every day then it could rely on doing it manually.
You can simply use an Autoexec Macro to open a form which has the VBA in it's "On Current" event procedure which would check the "System date" against the day of the month that you have specified, if it is the correct day it runs the queries and then run the 2 reports (assuming the reports don't automatically run the queries anyway).
You could also have a table containing a "flag" that is set when the reports run along with a date of running and if the system date is more than a month later than the last run date then it runs the reports. This would allow some leeway for vacations, illness etc.

[austenr]

Could you provide an example on how to create one? Thanks for the reply

[OBP]

The attached database should do what you want.
It has an autoexec macro, 2 tables, queries, forms and reports. It could be done with just one table, query and form if you wanted.
When the database is opened, the autoexec macro opens the rundate form which has the VBA to do what you want.
The VBA checks the current day against the day stored in the day table and also the number of days since the reports were last run (the date in the rundate table/form). Both values can be set by you, as could the number of elapsed days if you wanted.
If either criteria is met then it opens the queries and runs the reports in preview mode and reset the run date.
I have left message boxes in the VB to tell you the values involved.
It doesn't close all the forms, queries and reports.

[austenr]

OBP,

Thank you for your effort. This is almost exactly what I need to accomplish. There are a few modifications I want to be able to do though. The queries that are presently in my DB are make table queries. So I would want those to be deleted before the new make table queries run. I tried to find a DoCmd for that but could not. How would you accomplish this? Also if the user could enter the date for the month on open as you have now. Currently you have to go in and ehter it in each query because it uses that criteria to pull the correct records from the DB. The data would be pulled after each month is over. So they would have to enter for example 9/1/2006 as a start date and then 9/30/2006 as an end date. Could this be accomplished as well? Other than that it is exactly what I am looking for and can be a quite useful tool for me in the future. Thanks again for all of your help.

[OBP]

To delete the tables prior to running the queries use
DoCmd.DeleteObject acTable, "TableName"
where TableName is the name of the table to be deleted.
Yes you can enter the dates on a form and have any query "pick it up", in the query's Criteria row you enter
forms![FromName]![FieldName]
to use a single field as the criteria.
If you want to use the "Between" function it would be
Between forms![FromName]![FieldName1] and forms![FromName]![FieldName2]
If you wanted to run the "Previous Month's" data i.e. from the first day of the previous month to the last day of the previous month that can be done automatically by manipulating the date.

[XLGibbs]

Austen, is this the same thing you emailed me about? I forgot about using Windows scheduler...

Were you able to work with the macro wizard at all to do the process like we discussed?

[austenr]

Hi Peter. Yes it is sort of the same thing. Your solution was very helpful in that it allows me to let the user run their reports simply by opening the DB. The problem I have posted here is that there are some DB's that run reports that require dates to be changed to extract the correct data. What I wanted was to let the user input the starting dates of the data they wish to pull, or if possible make the query smart enough to realize that if it is October then in a field called "History Date" change the criteria date to 09012006 and to do that as the months and years go along. Not sure if Access can be made to manitulate dates like Excel does.

Also, I just realized that I need a way to parse the report out to Excel as well as producing an Access report because some of my end users take the data and manipulate it. Not really sure how to do this but i suspect some sort of VB script could be used. Any ideas?

Anyway both you and OPB have given me a ot of useful and informative info that I can and will implement this week as I have to rebuild the data reporting that you and I emailed about last week.

Any thoughts on the date manipulation and parsing to Excel would be helpful as like yourself time is critical. Thanks to you both.

[XLGibbs]

YOu can accomplish the date parameter thing a few different ways.

1. YOu could have a Form set up that simply allows for a date to be entered into a text box. The queries Criteria line would simply point to that Form's text box for the needed criteria.

2. YOu could have the query prompt for the desired parameter

Parsing the data out to excel? YOu could have present query tables built using MS Query. OR you could use the macro builder to transfer data to excel (but the path has to be identified either in VB or in the macro builder itself.

Lots and lots of ways to skin this kitty Austen...

[austenr]

Pete,

Not sure what you mean when you are telling me about parsing to Excel. Could you provide an example? I can hard code the path to suite me later. As I said, I could not find a good example of this either by searching this forum or Googleing it. Thanks. :dunno

[OBP]

austenr, you send the Report's queries or the report directly to Excel using the following code

DoCmd.OutputTo acOutputQuery, "rundate Query", acFormatXLS, "rundate.xls"

the second part with the rundate.xls should include the full path name if you do not want to save it in the "current" Access directory. Change the outputto a report and the name to a report name to use a report instead of the query.
You can also automatically email a "snapshot" of the report to anyone you want at the same time.

The dates can be automatically set in VBA if the are for fixed "days" i.e. always from 01/mm/2006 to 16/mm/2006, the mm can be calculated as can the year.

[austenr]

Tried that with no luck. Here is my code. I want to output the query called "mkt ICM Service Detail for Report" to the Excel workbook called "Detail.xls". Does the workbook have to be open?

DoCmd.OutputTo acOutputQuery, "mkt ICM Service Detail for Report", acFormatXLS, "Detail.xls"

[OBP]

Are you sure that it didn't work?
Did you get an error message?
Have you checked the Access system folder for the file as you didn't specify a path in the file name?

[austenr]

Peter and OBP. I have solved my problem thanks to your help. Thanks a lot. Solved.