Triggering an event

  • Thread starter Thread starter David S.
  • Start date Start date
D

David S.

Hello,

I'd like to trigger an event using the ontimer property
at 5am during the weekdays. It needs to be done within
Access and not the Task Scheduler.

I'd appreciate any thoughts or ideas you may have.

Thanks
David S.
 
David,

The only way to do it is to have Access running all the time, with a hidden
form open all the time. The form's TimerInterval can be set to 60000 (1
minute) to regularly trigger the Timer event which should then compare the
current day and time to weekdays at 5AM.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks Graham. What would the code look like to compare
the current day to the weekday time?

David S.
 
David,

'Put this in the declarations section
Private vLastDone As Variant

Private Sub Form_Timer()
'Fire an action at 5AM on weekdays
Dim tm As Date

tm = Now()

'Only check if vLastDone < today, which indicates
'that the last time the "action" was carried out was
'some time before today. This prevents the "action"
'being carried out more than once per day.
If Nz(vLastDone, tm - 1) < tm Then
'Check that it's a weekday
If Weekday(tm + 5, vbMonday) < 6 Then
'Check that it's 5AM
If format(tm, "hhnn") = "0500" Then
'Indicate that the "action" has been carried
'out for this day, so it doesn't happen again.
vLastDone = Date

'Do whatever you like!
End If
End If
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top