I know this has been discussed before but a search on this topic produced no results.. Sooo.. I would like to have my Access 97 database print a report every hour automatically.. Apparently the users find it hard to push a button once an hour.. Can this be done without using the windows scheduler? Thanks..
I think we can all sympathise with your user problem. Does each of your users need to print off a report every hour or is it the system that needs to print off 1 report every hour. If you can clarify this it would be easier to provide a solution.
Simple solution: (I'm assuming you know either Macros or VBA)
1. Create a new application linked to your current data base (Is your database split FE/BE or single application) and make a form that opens automatically on startup.
2. In the forms 'Timer interval' event property section change from 0 to 3600000 (Don't be put off by the big number, it's in milliseconds 1000*60*60).
3. Create either a Macro (or code)to open,print, and close the desired report (you will have to recreate the report in this app). Then insert the name of the macro into the 'On timer' property of the form. close and save the form.
4. Close and reopen the application, then just leave it running in the background. This will print the report every hour.
5. There are of course many ways to improve and enhance this solution, but this should work as basic as it is.
Alternatively:
6. There is dedicated scheduling software available for Access such as Total Visual Agent from FMS (www.fmsinc.com, there's a 30 day demo on the site). Thats what I use and a number of other people on this forum (but only because I got the money off the boss, it's not bad though). Some others have their own apps that they've written, I just don't have the time or the inclination.
Assuming that the database is going to remain open throughout the day, it's just a matter of using a timer event and setting the timer value to 3,600,000, this will make the event run once an hour, but will be at hourly intervals from when the form was loaded (I think).
So you could have a timer event that is set at an interval of 60,000 (one minute) and use code to test the minutes portion of the current time; there is also with this method the very slimmest chance that it might skip a report because the timer event might occur at say 10:00:59 and 999 milliseconds, but by the time that your code looks at the time, it's 10:01.
So, if you set the timer interval to 30 seconds (30,000), you can be sure that your code will run at least once a minute, and nearly always twice, but then you'd get one copy of the report at 10:00:05 (say) and another at 10:00:35.
to get around this you could have a global boolean variable (lets call it 'reported'), so your code in your timer event looks something like:
Code:
If Minute = 0 Then
If reported = False Then
DoCmd.OpenReport "MyReport", acViewNormal
reported = True
End If
Else
reported = False
End If
so the report runs when the minute is 0 and the 'reported' variable is false, but then the 'reported' variable is set to true to prevent the report running again if(when) the timer event occurs again while the minute is still 0. When the minute is no longer 0, the reported variable is reset to false.
You need to decide what form you're going to run this from; either your main switchboard, if that remains open all the time, or you could have a hidden form running just this timed task.
HTH
Mike
[This message has been edited by Mike Gurman (edited 04-30-2001).]
