Auto archive at end of month

[skyline666]

Hi,

I have done some archiving before on a database that i worked on previously, and so know how to do the archive through code, but how would you make the archiving run automatically at the end of the month? The code I used previously was used on the OnClick event of a command button, and so didnt matter when it was pressed:

Private Sub cmdMonthEndArchive_Click()

DoCmd.SetWarnings False
Dim stDocName As String

    stDocName = "MakeRentalArchive"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    stDocName = "MakeItemArchive"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.SetWarnings True
MsgBox "Finished archiving tables: 'Rentals', 'Rental Items & Rates'", vbInformation, "Archiving Tables"

End Sub

where MakeRentalArchive is:

INSERT INTO RentalArchive ( RentalID, ProjectSplitNameID, ItemID, Quantity, MonthlyRentalCost, RentalMonth, ArchiveMonth )

SELECT Rental.RentalID, Rental.ProjectSplitNameID, Rental.ItemID, Rental.Quantity, Rental.quantity*Item.MonthlyRental AS Expr1, Rental.RentalMonth, Now() AS ArchiveMonth

FROM Item INNER JOIN Rental ON Item.ItemID = Rental.ItemID

WHERE (((Rental.RentalMonth)=Format([forms]![Menu]![cmbMonth],"dd/mm/yyyy")));

and MakeItemArchive is:

INSERT INTO ItemArchive ( ItemID, Description, PurchaseCost, AnnualFee, MonthlyRental, CategoryID, ArchiveMonth )

SELECT Item.ItemID, Item.Description, Item.PurchaseCost, Item.AnnualFee, Item.MonthlyRental, Item.CategoryID, Now() AS ArchiveMonth

FROM Item;

Many thanks in advance,

Andrew

 

[TheAceMan1]

How are ya skyline666 . . .

The Idea is to use windows [blue]Task Scheduler[/blue] to open the Db with a [blue]command line arguement[/blue] that runs a macro. The macro in turn runs your code.

To maintain functionality of your button, put your routine in a module in the modules windows. Here it can be called from anywhere:

[blue]   Call [b][i]RoutineName[/i][/b][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[skyline666]

Hi TheAceMan1,

Looks like im going to have to get back to you with this becuase as ever the user likes to change his/her mind so might be archiving a different way to originally planned (theres a system already in place at work, which at first was going to go the same sort of lines, but then decided not to, and now maybe they do!).

Andrew

 

[TheAceMan1]

skyline666 . . .

You can also detect the date/time when the form opens (AutoExec Macro) and decide when to run the code!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sfatz]

Forgive me if I state something obvious, but don't forget to 'record' that the archive has been executed so that it doesn't execute again for that month.