Form Timer

[D Willett]

A bit of a dilemma here..

My form needs to requery every 5 minutes to update data, this I have working successfully. I also have to close the database down at a specific time to allow the server to complete maintenance tasks.
This is my code:

Code: Select all

Private Sub Form_Load()
     Me.TimerInterval = 300000

End Sub

Private Sub Form_Open(Cancel As Integer)
    
    DoCmd.Maximize
    Me.txtStop.SetFocus
    
End Sub

Private Sub Form_Timer()

Me.Painting = False
    [sbfInProgressWithECD].Requery
    [sbfCompletedToday].Requery
    [sbfCountOfCompletions].Requery
    [sbfInProgressTBA].Requery
    [sbfInProgressECDB4].Requery
    [sbfInProgressMon].Requery
    [sbfInProgressTues].Requery
    [sbfInProgressWeds].Requery
    [sbfInProgressThurs].Requery
    [sbfInProgressFri].Requery
    [sbfInProgressSat].Requery
    Me.Refresh
    Me.txtStop.SetFocus
    Me.SetFocus
Me.Painting = True


If Time = TimeValue("01:45") Then
DoCmd.Quit acQuitSaveAll
End If
End Sub

The first part of the code works where as the second part doesn't. I originally had the second part of the code running on a hidden form, but as the hidden form has no focus the code fails to run.
I moved the second coding to the main form which is frmWIP ( as above) in the hope that this would work, it doesn't I think because it can't capture the time exactly so missing its opportunity.
Can anyone take a look and advise the best method to allow this to work?

1, Requery the database.
2, Close down at a specific time.

Thanks

[Rudi]

Hi Dave,

I have been playing with this code and it is working to close a sample form I have

Code: Select all

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 10000
End Sub

Private Sub Form_Timer()
 If Time() >= #12:00:00 PM# Then
    DoCmd.Close acForm, "Sales"
End If
End Sub

In your code the timer might well be working, but it might only quit 5 minutes after your indicated time??? Mine closes 10 seconds after the time in Form_Timer based on the TimeInterval value set at form open.
This is a guess since it is not my field of expertise.

My Form_Timer code is slightly different to yours...maybe experiment with this format...

[D Willett]

Hi Rudi
Thanks for the info, I think we have similar code to achieve the close event but my problem is having 2 sets of timer events on the same form, so I'm not sure how to achieve them.

Regards

[Rudi]

Could the main form not open a second (possibly hidden) form with its own timer with the sole purpose of closing the DB?

[D Willett]

Hi Rudi, I did have that setup initially but with the main form being modal it kind of messed things about.
But, I'm trying this:

Added a tectbox on the main form (frmWIP) called txtTime with a control of =Time()

Then run this after the requerycode:

Code: Select all

If Me.txtTime >= #11:22:00 AM# And Me.txtTime <= #11:30:00 AM# Then
DoCmd.Quit acQuitSaveAll
End If

The only down fall could be that the Requery code runs every 5 minutes, this code may close the database twice....
I've set to check between an 8 minute window.

[HansV]

Once the database has been closed, the code doesn't run anymore, so there should be no risk of the database being closed twice...

[D Willett]

Yes, I realised after the posting. And there is a schedule to reopen the database 4hrs later when the server has run its maintenance so the code shouldn't trip over its self.

Thanks guys for inspiration...

[HansV]

Instead of

If Me.txtTime >= #11:22:00 AM# And Me.txtTime <= #11:30:00 AM# Then

you might as well use

If Time >= #11:22:00 AM# And Time <= #11:30:00 AM# Then

[D Willett]

Noted :-)

Thank you again Hans.