Solved

How do I run an AutoExec Macro Only Once Each Day

Posted on 2006-11-20
7
662 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 500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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