Running macro once per year

[wlkr.jk]

Hi,

I'm trying to run a macro that runs an archive command once per year. But, I need to make sure that the archive actually happens after a certain date even if the program isn't opened on that specific date to run the macro. For example, I need to archive all my records on March 31 every year. But if March 31 happens on a Saturday, no one is going to open the program, so the macro won't run. And the next time the program will be opened is the following Monday. Therefore, I've come up with the following code with the issue of somehow using an "exists()" function in access:

if exists(archivecount)=false then
archivecount = 0
end
temp2 = DateDiff(yyyy,#31/03/2014#,Now())
if archivecount < temp2 then
archive()
archivecount = temp2
end

The reason for the first if statement is because I need to initialize the variable 'archivecount' if it hasn't already been set. I don't want to reinitialize the variable every time the code is run because I want to run the code every time the program opens and because my infinite archivecount is what determines if the actual archive() command should be run.



For example, in 2016, the difference between 2016 and 2014 is obviously 2 years and if archivecount is less than 2, then that means the archive() function should be run....

[ranman256]

You would need a table to store the last time run or the NEXT time run, because the computer or app could be off.
if Now > 1 year and hasnt been run yet for that year

[wlkr.jk]

Ok thats what my next step would have been. I think what I'm going to do is just use a blank table with only the 'ID' field. Every time I do the 'archive()' command, I will add a new record to the table (therefore increase the number of records and effectively the largest ID number in the table being the number of times archive has been run).

However, now I'm trying to figure out how to return the last ID number from the table. Any ideas?

[ranman256]

Make a query or txtbox to call: DMAX([ID])

[wlkr.jk]

Ok, so I created a new table tblArchiveCount. Currently, I have 1 record in the table to act as my first year

Function AutoExec()
On Error GoTo FY_End_Archive_Err
With CodeContextObject
Dim temp2 As Variant
Dim archivecount As Variant
temp2 = DateDiff(yyyy, #3/31/2014#, Now())
archivecount = DMax("ID", "tblArchiveCount", "")
If ((archivecount - 1) < temp2) Then
Call Archive
Call Add_Record

End If
End With


FY_End_Archive_Exit:
Exit Function


FY_End_Archive_Err:
MsgBox Error$
Resume FY_End_Archive_Exit
End Function


So two questions, do you think this will do it, and what I'm I doing wrong as far as AutoExec() goes? I did it this way so that it would open automatically when I first open the program but nothing happens when I open it up (I put message boxes in for testing but took them out to post here)?