I wish a message to display at a certain time in access

[philqw78]

I am trying to display a message at 17:30 to remind users to close their access database. Can this be done using VBA attached to a from and if so how?

[TheSmileyCoder]

You can make a hidden form, that is opened at startup, and then create a timer event on that. Timer events are run every X seconds, where X is a value you can specify(in miliseconds). You can then simply check what the time is using time(). An example of the code is shown below. Remember in the form to set the timerinterval to something like 30000 I.e. every 30 secs

Private Sub Form_Timer()
    If Time() > #17:30:00 PM# Then
        MsgBox "Please remember to close your database!"
        DoCmd.Close
    End If
 
End Sub

Welcome to Bytes

[philqw78]

Thanks Smiley. Made me smile

[NeoPa]

Nice answer Smiley. An alternative would be to reset the timer value when done, to something like 900,000 to ensure another reminder is sent if they're not out within 15 minutes. The form will be closed when the database is closed.

Private Sub Form_Timer()
    If Time() > #17:30:00# Then
        Call MsgBox("Please remember to close your database before you leave!")
        'Call DoCmd.Close
        Me.TimerInterval = 900000
    End If
End Sub

[philqw78]

@NeoPa
Thanks NeoPa. Figured that bit. I attached the timer event to the switchboard with a timer interval of 600000, so every 10 minutes she will get the reminder.

[NeoPa]

Cool. Sounds like you already have someone in mind that needs this ;)

Just a caveat. Remember when using the TimerEvent() to bear it in mind if ever you're debugging anything. It can make life quite complicated at times.

[philqw78]

It was fun when I set the time to 5 minutes ago and the timer interval to 1000 milliseconds so that I could see the result straight away. I couldn't close the message box fast enough to get back to design view. :>(

[NeoPa]

:D

Did I mention you may have problems debugging with a timer event enabled!

[FishVal]

Just a suggestion:


Since time difference is available when code runs first time, TimerInterval could be calculated to fire Form_Timer event exactly at expected time. This, however, is not an option in a case when system time is expected to be changed while TimerInterval is counted down. By the way, Form_Timer event handler may not run if there is some form opened as modal. If so, then programming Windows system timer via WinAPI could be an option.

Regards,
Fish