Improve company productivity with a Business Account.Sign Up

x
?
Solved

How do I run an AutoExec Macro Only Once Each Day

Posted on 2006-11-20
7
Medium Priority
?
798 Views
Last Modified: 2008-02-26
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
Comment
Question by:robyergovich
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17981367
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
 

Author Comment

by:robyergovich
ID: 17982174
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 17982235
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

Author Comment

by:robyergovich
ID: 17982528
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
 

Author Comment

by:robyergovich
ID: 17990165
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17990239
a C grade? this will be my last comment on your post.
0
 

Author Comment

by:robyergovich
ID: 17990378
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question