skyline666
Programmer
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:
where MakeRentalArchive is:
and MakeItemArchive is:
Many thanks in advance,
Andrew
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:
Code:
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:
Code:
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:
Code:
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