Trigger a macro to run on a specific date

[R Bruns]

Is it possible to trigger a macro to run on a specified date - for example,
every Monday - without any intervention? The macro is a SendObject - sending
a report.

Thanks for your assistance!

 

[Ken Snell \(MVP\)]

You can use an AutoExec macro to do this. The macro needs to have a step
that tests the "day" when the database opens, and if it's not Monday, then
run a StopMacro action. Otherwise, it then should run the SendObject action.

You can test if today's date is a Monday by this type of expression:

DatePart("w", Date(), 1) = 2

Note: you'll need a way to know when the report has been sent already on a
Monday -- otherwise, if you close and open the file again on that same
Monday, the macro again will send the report.

 

[R Bruns]

That worked great to get the macro to run on the date. Thank you very much!

But you pose the next question; how do I ensure that it runs only once? Any
assistance is greatly appreciated!

 

[Ken Snell \(MVP\)]

Create a table (name it "tblMacroRanOn"). Add one field to this table:
DateLastRun. Make this field a DateTime datatype.

Open this table in datasheet view, and add one record to it. Put any date
prior to the next date on which you want the macro to run in that field --
for example, 4/1/2008.


Then create an update query (name it "qryUpdateMacroRanOn"). This query
should look like this (SQL view):

UPDATE tblMacroRanOn
SET DateLastRun = Date();

This query will be run by your macro to update the "last run date" in the
table when the macro runs.


Then add a new step as the first step of the macro, with a Condition:
Condition: DLookup("DateLastRun","tblMacroRanOn")<>Date()
Action: StopMacro

The above step will test if the date in the table is today's date, and if
yes, not run the next steps of the macro.


Then add 3 new, final steps to the macro (after your current step that has
the DatePart expression as the condition):
Action: SetWarnings
Warnings On: No

Action: OpenQuery
Query Name: qryUpdateMacroRanOn
View: Datasheet
Data Mode: Edit

Action: SetWarnings
Warnings On: Yes

 

[R Bruns]

Ken,

Thank you so much - this worked perfectly! You are amazing!

Roslyn

 

[Ken Snell \(MVP\)]

Thanks... I just noted that I made a slight error.

Change this condition line:

Condition: DLookup("DateLastRun","tblMacroRanOn")<>Date()
Action: StopMacro


To this (note that I've changed the <> to =):

Condition: DLookup("DateLastRun","tblMacroRanOn")=Date()
Action: StopMacro


Sorry about that!