Solved

How do I run an AutoExec Macro Only Once Each Day

Posted on 2006-11-20
7
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

617 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