Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

How do I run an AutoExec Macro Only Once Each Day

I have a macro set up to automatically email notifications etc. in my access database.  I understand how to set up a scheduled task in the windows scheduler to open my database once a day and have the "Autoexec macro" send the email.  I would prefer if possible to eliminate the scheduled task and have the macro run the first time the database is opened each day.  But only the first time its opened.  Is there a way to do this?
0
robyergovich
Asked:
robyergovich
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
1. create a table  {tblSent }  with one field date/time (dtSent)
2. in the open event of your startup form
    read the the date from the tblSent
    if dtSent < Date() then
      'send the email notif
      currentdb.execute "update tblSent set dtSent=Date()"
   end if

0
 
robyergovichAuthor Commented:
O.k. this isnt quite working.  I did everything above but it sends the email everytime I open the database.  I looked in {tblSent} and the date is being populated but it runs the macro everytime I open the database and I want it to only happen the first time its opened each day.  Thoughts?
0
 
Rey Obrero (Capricorn1)Commented:
you now have to rename the Autoexec macro to something else

Autoexec macro runs when the db is open

is the automacro just set to send notif?

if yes, rename it to NotifMacro

then call it from the startup form

   if dtSent < Date() then
      'send the email notif
      docmd.runmacro NotifMacro
      currentdb.execute "update tblSent set dtSent=Date()"
   end if
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
robyergovichAuthor Commented:
I actually already did that thinking that might be the problem and it still runs everytime I open the database.  I named it mcrEmail.  Yes the macro is only for sending the email notifications.
0
 
robyergovichAuthor Commented:
O.k. I had to figure this out on my own.  There were two problems.  

1.) The way you have it written above calls it from the startup form and the table you had me create isnt open at that point.  I had to use the DLookup function to get VBA to understand what dtSent was.

2.) The other problem is I couldn't get VBA to understand "Date()".  VBA won't let me write the code the way you have it above.  It removes the () after Date and it doesnt understand what I want because it still sends the email everytime.

I fixed it by putting a field on the startup form with the value = Date().  I then named that field myDate and in VBA used the DLookup to return the value of dtSent and called it LastSend.  I then modified your code to start off with If LastSend < MyDate then....

Now it works.
0
 
Rey Obrero (Capricorn1)Commented:
a C grade? this will be my last comment on your post.
0
 
robyergovichAuthor Commented:
I'm sorry but I looked at the grading guidelines and that's the best I could give you.  Had you placed as much importance on responding to my follow up posts asking why this wasn't working, as you did on my grade selection this would have worked out differently. You have responded to my posts before and your explanations are usually very quick and frequently incomplete with little to no follow up when something doesn't work.  In some cases that may be adequate but I expect more than that for 500 points.  Given the excellent, completely thorough follow up I have received from other experts on this site I could not justify giving you the same grade as I have given them.  I should point out that I have never posted a question on this site worth less than 500 points.  Ever.  In addition I had to explain my solution so that anyone reading this as an answer would understand how to make it work.  With respect, I would actually appreciate it if you did not respond to any more of my posts.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now