Timer event does not fire

J

Jack

Hi,
I have a small application where I would like to automate the button click
(without clicking the button) in Access form. I am using a timer control at
interval of one minute to do this. This seemed to have worked last time I
tried. However it is not working now. I am not sure why the timer fucntion is
not triggering autoclick of the two button I have in a form which is open.
There are number of rows for each query. I would appreciate any help to
resolve this. Thanks

CODE:
Option Compare Database

Private Sub cmdTest_Click()
Call SendOverdueNotice
End Sub


Private Sub cmdTest2_Click()
Call SendAcknowledgemtDueNotice
End Sub

Private Sub Form_Load()
Me.TimerInterval = 60000
End Sub

Private Sub Form_Timer()
cmdTest_Click
cmdTest2_Click
End Sub


Public Function SendOverdueNotice() As String

Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False
' Dim SecurityManager As New AddinExpress.Outlook.SecurityManager
' SecurityManager.ConnectTo (outlookApp)
' SecurityManager.DisableOOMWarnings = True



'Open the controlling recordset
'Set Rs = Db.OpenRecordset("qrySendEmailAdvice")
Set Rs =
Db.OpenRecordset("qrySendEmailAdvice_7_or_10_days_from_responseduedate_open",
dbOpenDynaset, dbSeeChanges)
While Not Rs.EOF
If Rs![Interval] = 7 And Rs![EmailSent7th] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar Closing
Reminder", _
"Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding to
" & Rs![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "Update dbo_tblActionRequest SET EmailSent7th=-1 where IDNum ='" &
Rs![IDNum] & "'"
Db.Execute sSQL

ElseIf Rs![Interval] = 10 And Rs![EmailSent7th] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar Closing
Reminder", _
"Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding to
" & Rs![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE dbo_tblActionRequest SET EmailSent10th=-1 where IDNum ='" &
Rs![IDNum] & "'"
Db.Execute sSQL
Else
Exit Function
End If

Rs.MoveNext

Wend
Rs.Close
Set Rs = Nothing
'Control recordset now closed

'Reset environment
' DoCmd.SetWarnings True
' OlSecurityManager.DisableOOMWarnings = False

End Function

Public Function SendAcknowledgemtDueNotice() As String

Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
' Set Rs =
Db.OpenRecordset("qrySendEmailToContactWithFourHoursOfAcknowledgementDue")
Set Rs1 =
Db.OpenRecordset("qrySendEmailToContactWithFourHoursOfAcknowledgementDue",
dbOpenDynaset, dbSeeChanges)
While Not Rs1.EOF
If Rs1![Interval] = 24 And Rs1![Acknowledgement24HourEmailSent] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs1![EmployeeEmail], , , "CarPar
Acknowledgement Due Reminder", _
"Hello " & Rs1![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar Acknowledgement
Due corresponding to " & Rs1![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs1![Interval] & " days overdue. You need to work
on this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "Update dbo_tblActionRequest SET Acknowledgement24HourEmailSent=-1
where IDNum ='" & Rs1![IDNum] & "'"
Db.Execute sSQL

' ElseIf Rs![Interval] = 10 And Rs![EmailSent7th] = False Then
' 'For each record (CaseID) send an email
' DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar
Closing Reminder", _
' "Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding
to " & Rs![IDNum] & _
' vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False
'
' 'Update the Booking table so that emails are not duplicated
' sSQL = "UPDATE dbo_tblActionRequest SET EmailSent10th=-1 where IDNum ='" &
Rs![IDNum] & "'"
' Db.Execute sSQL
Else
Exit Function
End If

Rs1.MoveNext

Wend
Rs1.Close
Set Rs1 = Nothing
'Control recordset now closed

'Reset environment
' DoCmd.SetWarnings True
' OlSecurityManager.DisableOOMWarnings = False

End Function
 
T

Tom van Stiphout

On Mon, 4 May 2009 13:49:01 -0700, Jack

Your code does not have Option Explicit. Very important, but likely
not the cause of your current issue.
Sometimes event procedures get disconnected from their event. To fix
this up, open the form in design view, get Properties, and click on
the ... button next to the event. This will re-establish the relation.
Put this in the Timer event:
debug.print "In Form_Timer" to debug if it gets called.
Remember that timer events are low priority, so they won't fire if
other long-running code is still active.

-Tom.
Microsoft Access MVP

Hi,
I have a small application where I would like to automate the button click
(without clicking the button) in Access form. I am using a timer control at
interval of one minute to do this. This seemed to have worked last time I
tried. However it is not working now. I am not sure why the timer fucntion is
not triggering autoclick of the two button I have in a form which is open.
There are number of rows for each query. I would appreciate any help to
resolve this. Thanks

CODE:
Option Compare Database

Private Sub cmdTest_Click()
Call SendOverdueNotice
End Sub


Private Sub cmdTest2_Click()
Call SendAcknowledgemtDueNotice
End Sub

Private Sub Form_Load()
Me.TimerInterval = 60000
End Sub

Private Sub Form_Timer()
cmdTest_Click
cmdTest2_Click
End Sub


Public Function SendOverdueNotice() As String

Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False
' Dim SecurityManager As New AddinExpress.Outlook.SecurityManager
' SecurityManager.ConnectTo (outlookApp)
' SecurityManager.DisableOOMWarnings = True



'Open the controlling recordset
'Set Rs = Db.OpenRecordset("qrySendEmailAdvice")
Set Rs =
Db.OpenRecordset("qrySendEmailAdvice_7_or_10_days_from_responseduedate_open",
dbOpenDynaset, dbSeeChanges)
While Not Rs.EOF
If Rs![Interval] = 7 And Rs![EmailSent7th] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar Closing
Reminder", _
"Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding to
" & Rs![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "Update dbo_tblActionRequest SET EmailSent7th=-1 where IDNum ='" &
Rs![IDNum] & "'"
Db.Execute sSQL

ElseIf Rs![Interval] = 10 And Rs![EmailSent7th] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar Closing
Reminder", _
"Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding to
" & Rs![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE dbo_tblActionRequest SET EmailSent10th=-1 where IDNum ='" &
Rs![IDNum] & "'"
Db.Execute sSQL
Else
Exit Function
End If

Rs.MoveNext

Wend
Rs.Close
Set Rs = Nothing
'Control recordset now closed

'Reset environment
' DoCmd.SetWarnings True
' OlSecurityManager.DisableOOMWarnings = False

End Function

Public Function SendAcknowledgemtDueNotice() As String

Dim Db As DAO.Database, Rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set Db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
' Set Rs =
Db.OpenRecordset("qrySendEmailToContactWithFourHoursOfAcknowledgementDue")
Set Rs1 =
Db.OpenRecordset("qrySendEmailToContactWithFourHoursOfAcknowledgementDue",
dbOpenDynaset, dbSeeChanges)
While Not Rs1.EOF
If Rs1![Interval] = 24 And Rs1![Acknowledgement24HourEmailSent] = False Then
'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , Rs1![EmployeeEmail], , , "CarPar
Acknowledgement Due Reminder", _
"Hello " & Rs1![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar Acknowledgement
Due corresponding to " & Rs1![IDNum] & _
vbCrLf & vbCrLf & "is " & Rs1![Interval] & " days overdue. You need to work
on this ASAP", False

'Update the Booking table so that emails are not duplicated
sSQL = "Update dbo_tblActionRequest SET Acknowledgement24HourEmailSent=-1
where IDNum ='" & Rs1![IDNum] & "'"
Db.Execute sSQL

' ElseIf Rs![Interval] = 10 And Rs![EmailSent7th] = False Then
' 'For each record (CaseID) send an email
' DoCmd.SendObject acSendNoObject, , , Rs![EmployeeEmail], , , "CarPar
Closing Reminder", _
' "Hello " & Rs![AssignedTo] & vbCrLf & vbCrLf & "Your CarPar corresponding
to " & Rs![IDNum] & _
' vbCrLf & vbCrLf & "is " & Rs![Interval] & " days overdue. You need to work
on this to close this ASAP", False
'
' 'Update the Booking table so that emails are not duplicated
' sSQL = "UPDATE dbo_tblActionRequest SET EmailSent10th=-1 where IDNum ='" &
Rs![IDNum] & "'"
' Db.Execute sSQL
Else
Exit Function
End If

Rs1.MoveNext

Wend
Rs1.Close
Set Rs1 = Nothing
'Control recordset now closed

'Reset environment
' DoCmd.SetWarnings True
' OlSecurityManager.DisableOOMWarnings = False

End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to create a macro to incorporate the following code 1
How to send email from Access without opening Outlook 4
Data Type Conversion error 5
Not sure what is wrong here 3
recordset UPDATE error 5
Event procedure not working 2
Run-time error 430: Class does not support Automation..... 4
What's wrong with this code? 5

Top