Solved

How do I run an AutoExec Macro Only Once Each Day

Posted on 2006-11-20
7
689 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
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
access vba 5 57
Access Need to add combo box to sub form 10 51
Access VBA How To Check a Table To See If The Field Value Is Null 6 40
ACCESS / VBA - Count of Rows in Table 4 47
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

734 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