Originally posted by TonyT
Actually I meant automatically.. I do have dates set to records when users enter them in.. so when they enter in a record.. todays date is set to that record..
If there is a way.. to do it automatically ?
thanks for the response..
Ok. HOW "automatic" are you looking for? There is no *POOF* here today and gone tomorrow capability ... You can setup either a schedule or a form that periodically runs (or wakes up) that does the deletion ...
You could attach some code or query to a startup form in your db but once you've done a batch of archiving do you really want a reminder every time you open the db that x number of records are older than 60 months?!
For the effort versus benefits involved/received, I'd sooner create a query and just run it as a quarterly process.
IS there a way to run the code.. once every year ?.. like on a specified date.. check to see if there are any records that are over five years old.. if there is.. then msgbox you have records over 5 years old.. would you like to delete.. ?
I've never tried but I suppose you could create a procedure to do this.
Every time the db opens or closes the procedure checks todays date and if that date matches a preset archive date then a query runs to check for archive candidates. The main problem with this is that the database *has* to be open or closed on an archive date for it to run the procedure and make the match in dates - although to get around that problem I suppose you could work in some tolerance and make the procedure check 5 days each way of the archive date.
Rather than trying to do this yourself (if you're not familiar with VBA) you could start by looking on the net for some other clever b'stard who's probably already done this themselves. Try www.download.com and look for freebies, and check the usual Access sites and Google of course.
Here. I quickly wrote some code to get you started in case you want to tackle this yourself. This code will run an event if the date is 07/01 (07-Jan, in case you're a yank!!) so it will work on an annual basis. Dump it into a new module, save the module then create a macro that points to the code. If you call the macro autoexec, it will run everytime the db starts up.
Public Sub CheckDate()
Dim strToday As String
Dim strArchiveDate As String
'Set archive date
strArchiveDate = "07/01"
'Set todays date in right format
'Note: if annual check, there's no need to include the year data
strToday = CStr(Format(Now(), "dd/mm"))
'Check for date match and run something accordingly
If strArchiveDate = strToday Then
'Run your archive query in this section
MsgBox "Today is audit day!"
Dim db As Database
Dim ans As String
Set db = CurrentDb()
ans = msgbox("There are records that are five years old.. would you like to delete?", vbYesNo)
If ans <> "6" Then
msgbox "At this time the records have not been deleted!", vbOKOnly
db.Execute "Delete * FROM YourTable " _
& "WHERE YourTable.YourRecord <= Date() - 1825"
msgbox "Aged records have been deleted!", vbOKOnly