Timer to display live report and new record with day change? 1

[irethedo]

I need to figure out a way to do the following things:

1) Is there a way to set a form up so that as long as it is open, it will update every so often (like every 5 minutes) from live data?

2) I need to create a new record for each day and copy a few fields of data from the current record if the date has changed (meaning a new day). How can this be accomplished?

3) This will be used by multiple users so all will update at the same time...

Thanks in advance

 

[VicRauch]

To get a form to do something every 5 minutes, using the OnLoad event, set the form's timer interval. Me.TimerInterval = 300000, then set the OnTimer event to the code that you want executed. By the way, each "tick" is one millisecond, so there are 1000 "ticks" per second. Therefore, for 300 seconds (5 minutes) there are 300,000 "ticks."

Within the OnTimer event is where you would put the code to create a new record for each day and do all these other things. Who ever has the specified form open, this procedure would work for them.

HTH,
Vic

 

[irethedo]

Thank you for the response Vic-

I just realized that I did make a typo on my initial post where I had my question correct in the title but not in the question...(sorry- my fault)

Rather than dislaying a form that is live, I wish to display a report and have it update every so often.

From the info that you sent, I looked at the event choices and quickly realized that there are not as many event choices for the report as there are event choices for the forms... (there is no onLoad command for reports)

Is it possible to set the report to update to some known timer? ( would I use the Open event for this code?)

As an added twist, is it possible to make the database alternate between displaying two different reports while they are updating them on a regular basis?

Thanks again!

 

[VicRauch]

In order to display a report and have it automatically updated every 5 mintues, the only way I can think to do this would still be from a form. How ever you would want the sequence started, be it the user clicks on a button somewhere, or just starts whenever the user first opens the database. The controlling mechanism would be like this:
1. Open a form as hidden

DoCmd.OpenForm "formname",,,,,acHidden

2. Within this form's timer control stuff (OnTimer and TimerInterval as discussed in my previous post) Open the report, then every 5 mintues close the report and reopen it.

Sub OnTimer()
  DoCmd.Close acReport, "rptRefreshedReport"
  DoCmd.OpenReport "rptRefreshedReport"
End Sub

Using a hidden form to control the closing and reopening of the report will act as a "refresh" for the report. I know of no actual "refresh" command for a report.

I'm not sure what you mean by two different reports. If there are actually two different reports you want to display before the users, one for 5 minutes, and a reprt of different things for the next 5 minutes, then you would need a Static variable in your OnTimer code in the controlling form. The Static variable would be a Booleen that if true you would close report1 and open report2, then set the variable to a "not" of itself

  BooleenSwitch = Not BooleenSwitch

Then when the variable (BooleenSwitch) is false, close report2 and open report1.
If this was your idea as a way for the system to be able to "refresh" your data, this will happen by just closing the one report and then reopening the same report. As long as your RecordSource is a query over the actual live data in your system, every 5 minutes your report will be truly "refreshed."
HTH
Vic

 

[irethedo]

Thanks Vic- I am going to digest that and try it- it may take me a couple of days to get to this and I do appreciate your advice on this!

 

[irethedo]

Thanks Vic-

That worked pretty slick! ( I had to add the ACPreview to the Open report because my print que ws starting to fill up as it wanted to print a bunch of reports)

Here is the code I set up in the timer procedure per your suggestions:
------------------------
Private Sub Form_Timer()
' open and close report upon the time value in the timer table
DoCmd.Close acReport, "schedule"
DoCmd.OpenReport "schedule", acPreview
End Sub
-----------------------

==============================

There is one problem though...
------------------------------
I can't close the report without shutting off the database-
it keeps refreshing the report...

I tried to set it up to close the hidden form (which sets up the timer up) when the reort closes but then it tries to close it whenever the timer goes off and I get a compile error on the DOCmd CloseForm "Hidden Form" line in the code below:

---------------------------
Private Sub Report_Close()
On Error GoTo Err_Exit_Click

' Close form
DoCmd.Close
' Close the hidden form that has the timer
DoCmd.Close acForm "Hidden Form"
DoCmd.OpenForm "Main Form", acNormal

Err_Exit_Click:
Exit Sub

End Sub

---------------------------

I am not sure how to gracefully drop out of this report with this timer on it and I would be willing to kick the user out of the database if they close the report but I am not sure how to close the report with the timer code using the DoCmd Close function as this would try to shut down the database on the first cycle of the timer..

Any Ideas?

Thanks again!

 

[VicRauch]

irethedo:

If you are trying to just close the report and not have it refreshed anymore, then you need to just close the hidden form. Within the hidden form, withing the OnClose event, you need the code to close the report. The catch here is that the hidden form is "in control." If you put code in the report to close the form when the report is closed, then you will be closing the In-Control form every time you close the report, which the form is doing every 5 minutes. So, by closing the hidden form, the report will not be opened again. So as the hidden form is closing, close the report this one last time.
HTH,
Vic

 

[irethedo]

Thanks Vic-

But you kind of lost me there....

If the hidden form is hidden, and I can't close it on the close event of the report, and the user doesn't even know that the hidden form is there, how can I close the hidden form?

I know that the hidden form is in control but unless I had a button to close the report o nthe reprot (I don't know if that is possible) I am not sure how to close it...

I must be missing something here...

 

[VicRauch]

Somewhere in your system you must have some form of menus. Give the user a very clear place to find a button or option within the menu structure to "Close the Re-freshing Report" When they click that button, or select that option within your menu system, the code behind that action will close the hidden form, and the report.

 

[irethedo]

That works good Vic-

Thank you very much for all your help!