Timed Events

[azgambit]

Is there any way to program access to run a macro once a year on a set date?

 

[fernin8r]

I would try using an if statement with the Today Function

If( Today() = 1/1/03) Then

Macro

End If

i'm not sure how you would increment the year...

 

[Cosmos75]

Not much help but....

Maybe some VBA with this logic

If Now()=RunMacroDate
MacroName

Where RunMacroDate is the date you want the macro to run, and MacroName is the name of the macro.

Something along these lines? Am not good with VBA code, somewhat better with the flow of code than the actual code

 

[Cosmos75]

You could format Now() and RunMacroDate as Month-Day (or day-manth)

 

[raskew]

(1) Create tblLastRun to hold the date the process last occurred.
Field: LastRun, type: Date, format: shortDate

(2) Initialize by adding one record, using 7/20/90

(3) Open the autoexec macro and add:
Action: RunCode, Function Name: StartForm()

(4) Copy/paste this to a new query and name it qryLastRun:

SELECT Max(tblLastRun.LastRun) AS MaxOfLastRun
FROM tblLastRun;

(5) Create a new module (basStartUp) and copy/paste this
code to it.

Public Const conRunDate As Date = #7/20/90#

Function StartForm()
'*******************************************
'Name:      StartForm (Function)
'Purpose:   Run a procedure once a year, on
'                 or after a specified date.
'*******************************************

Dim dteRunDate As Date, dteLastRun As Date
Dim strSQL As String, msg As String, db As Database
'refers to public const conRunDate
dteRunDate = DateSerial(year(Date), Month(conRunDate), day(conRunDate))
'determines when the procedure was last run
dteLastRun = DLookup("MaxOfLastRun", "qryLastRun")

'since it's possible that the database won't be opened on the
'exact date, it's necessary to use Date >= rather than Date =
'it's also necessary to ensure that the process
'hasn't already run, eitherwise you'll end up with 
'an endless loop
If Date >= dteRunDate And year(dteLastRun) < year(Date) Then
   docmd.Beep 'your code to run the procedure would go here
   Set db = CurrentDb
   'adds new record to tblLastRun, showing today's date
   strSQL = "INSERT INTO tblLastRun ( LastRun )SELECT Date() AS Expr1;"
  'run the append query
   db.Execute strSQL
   msg = "LastRun date corrected to #" & Date & "#"
   MsgBox msg, vbExclamation, "Procedure Alert"
   db.Close
   Set db = Nothing
'delete the following if desired
Else
   msg = "Procedure last run on #" & dteLastRun & "#"
   MsgBox msg, vbExclamation, "Procedure Alert"
End If
'do something else, in this case open the main form
docmd.OpenForm "z_WizMain", acNormal, , , , , "zf_Instructions"
End Function

(6) With the module open, step through the code to see it in action. Once in place, it'll process each time you open the database.