On Timer Event Not Working in Access Forms

[sprite]

Any idea why my On Timer event won't work in my Access Form? I've tried many different timer intervals from 1 to 1000000. Here is my code:

Private Sub Form_Timer()

' Check to see if today is Tuesday and if true
' import weekly data
If Day(Date) = 3 Then

DoCmd.RunMacro "macAutoEmailMasterbaseToNicola"

End If

End Sub

Thanks if anyone can help.

 

[dhookom]

Day() will return the day of the month. For instance Day(Date()) at the moment is 29. I think you want WeekDay(Date).

BTW: I would probably add something so you don't send emails more than once per week unless this is what you want.

Duane
Hook'D on Access
MS Access MVP

 

[sprite]

Thanks Duane, that worked, but youre right I don't want to send more than one email. How would I do that? So, if I opened the db this morning and the email was sent, then I close it, but I open it again later, its gonna send another email. I thought about some kind of counter or somethin, like if the macro was run, it counts it as a 1, so there would be an If stmt that says something like If counter is >0, then dont run. But I dont know how to write that. Could you help me?

 

[RoyVidar]

I'd probably keep a table for this, either just a one record table to update per each mailing, or one to document the sending history. Here a short sample using a one record table called TheTable with a date/time field called TheDate - it will need an initial record/date.

[tt] ' Check to see if today is Tuesday and if true
' import weekly data
If WeekDay(Date) = 3 Then
If DLookup("TheDate", "TheTable") < Date Then
DoCmd.RunMacro "macAutoEmailMasterbaseToNicola"
CurrentDB.Execute _
"UPDATE TheTable SET TheDate = Date()", dbFailOnError
End If
End If[/tt]

Roy-Vidar

 

[dhookom]

I have done this before with a log table with just a date field.
[tt]
tblEmailLog
=====================
SentDate datetime (set default to Date())
[/tt]
You can use code to check to see if the current Tuesday's date is in the table prior to sending the email. If not, send the email and append a record.

Duane
Hook'D on Access
MS Access MVP

 

[sprite]

hey, thanks guys.
got one last request. i've decided to list specific dates, like: if date() = 12/30/2009 or if date() = 01/14/2010 or if date() = 02/21/2010, etc. but whats the right way of doing it.

here is the code i have so far:

Private Sub Form_Timer()

If Date = 12 / 30 / 2009 Then

If DLookup("TheDate", "tblEmailLog") < Date Then
MsgBox ("it worked")
CurrentDb.Execute _
"UPDATE tblEmailLog SET tblEmailLog.TheDate = Date()", dbFailOnError
End If
End If

End Sub

 

[RoyVidar]

Then use DMax in stead of DLookup, and in stead of updating the record, add...

[tt] If WeekDay(Date) = 3 Then

If DMax("TheDate", "tblEmailLog") < Date Then
MsgBox ("it worked")
CurrentDb.Execute _
"INSERT INTO tblEmailLog (TheDate) Values (Date())", dbFailOnError
End If
End If[/tt]

The above is typed and not tested...

Roy-Vidar

 

[dhookom]

If you want to send emails on specific dates, I would create a table of dates with a field that identifies if the email has been sent:

[tt][blue]
tblEmailLog
================
SchedDate
SchedDone y/n (default to 0)
[/blue][/tt]

Then, your code might look like (caution air code):

   If  Nz(DLookup("SchedDone","tblEmailLog ","SchedDate =#" & Date() & "#"),1) <> 1 Then
       DoCmd.RunMacro "macAutoEmailMasterbaseToNicola"
       CurrentDb.Execute _
          "UPDATE tblEmailLog " & _
          "Set SchedDone = -1 " & _
          "WHERE SchedDate = Date()", dbFailOnError
   End If

Just add dates to the table as needed.

Duane
Hook'D on Access
MS Access MVP

 

[sprite]

Duane,
ok, that works except now im back to where i was b4 where it keeps running over and over continuously. i need something back in there to tell it not to run if its already run before.
thanks.

 

[dhookom]

What's your exact code? What does the table look like after sending an email? Did you try to debug the code? faq705-7148

Duane
Hook'D on Access
MS Access MVP

 

[sprite]

Hi Duane,

here is my code:

Private Sub Form_Timer()

If Nz(DLookup("SchedDone", "tblEmailLog ", "SchedDate =#" & Date & "#"), 1) <> 1 Then
DoCmd.RunMacro "macAutoEmailMasterbaseToNicola"
CurrentDb.Execute _
"UPDATE tblEmailLog " & _
"Set SchedDone = -1 " & _
"WHERE SchedDate = Date()", dbFailOnError
End If


End Sub


and after it runs, my table looks like this:

SchedDate SchedDone
1/4/2010 Yes
1/29/2010 No
2/12/2010 No
2/26/2010 No
3/12/2010 No
3/26/2010 No
4/16/2010 No

 

[PHV]

I'd replace this:
<> 1
with this:
= 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sprite]

Hi Duane and PHV,

Actually I figured it out, the value there should be <> -1. It works now. Thanks for everybody's help.

Robert

 

[sprite]

oops, I spoke too soon. after i took out todays date in my table (i was using it to test), it didnt work right. it kept running over and over. seems like there shld be something in the code that evaluates todays date and if todays date is in the table. if not then dont run. if todays date is in the table, then run, as long as the "sched done" = no.

still need help,sorry. thanks.

 

[PHV]

Did you try my suggestion ?

 

[sprite]

PHV,

o my gosh. the first time i tried it, "=0" didnt work, now it does.

thanks for your help.