Self refreshing query

[Gkirkup]

I have a 'Snapshot' report, which gives up-to-the-minute data on a business - sales year-to-date, month-todate, week-to-date and today. They run this many times a day, but ideally, they would like to have the information display on a monitor, and be updated automatically every few minutes.
Is there any way that a query can run by itself every few minutes, and gather the current information?

Robert

 

[edtab]

You can use the On time event of the form to run a particular
vba code every so often. The timer interval controls the frequency
that the code is triggered. I believe 1000 is equivalent to one second,
so if you put 5000, it should run every 5 seconds.

 

[boblarson]

Yes, 1000 is equivalent to one second (it's expressed in milliseconds) so you can put 300000 in to update every 5 minutes.

The database would have to be open, and you would likely have to have code that closes the report and reopens it in that timer event. I think I would create a form to display that data instead as it is easier to refresh a form than a report.

 

[The_Doc_Man]

Bob, you don't have to close and reopen a report if you remember that the only difference between a form and a report is that a report can't be used for data input so easily.

Gkirkup - build a FORM with the information you want and put a timer event behind it. Also put on a button control with an on-click event. As suggested above, pick a viable interval. In the OnTimer routine, you have to do a few things.

1. Requery the form's recordsource. Me.Requery
2. Repaint the displayed form. Me.Repaint
3. Renew the timer. Me.Timer = 300000

The BUTTON routine has to do two things.

1. Zero the timer.
2. Close the form. Me.Close

 

[Gkirkup]

On looking at the ON form event properties, I see that there is also one called On Data Change. That would be even better, as the form would refresh only when the underlying data was changed - when a new sale is made.
But I see that I have to write an event procedure to redisplay the form, or at least the data. How do I do that?

Robert