How to stop deleting after 12 months

[Bob]

How can I stop records being deleted if they are older than 12 months old
Thanks For any help................Bob

 

[fredg]

How can I stop records being deleted if they are older than 12 months old
Thanks For any help................Bob

Deleted from what? From Where?

I suspect you have posted to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.

Please repost to the correct newsgroup for whatever Office program you
are using. I would suggest you include your Windows and Office version
number in the message.

 

[Bob]

Sorry WinXp MS Access 2002
I have Records in a table that have a date next to them, what I wanted is
that they could not be deleted or altered after 12 Months...Thanks Bob

 

[Jeff Boyce]

Bob

There is nothing I'm aware of in Access "out of the box" that would delete
records from tables after 12 months (or 2 or 7.6). It sounds like your
Access application has some code associated with it that is performing this
function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry WinXp MS Access 2002
I have Records in a table that have a date next to them, what I wanted is
that they could not be deleted or altered after 12 Months...Thanks Bob

 

[Bob]

Jeff , I wanted to add some code so I would not delete any records that are
more than 12 months old, just rather a safety net.....Thanks Bob

Bob

There is nothing I'm aware of in Access "out of the box" that would delete
records from tables after 12 months (or 2 or 7.6). It sounds like your
Access application has some code associated with it that is performing
this function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Bob

I'm not sure I understand...

Are you saying that you don't now delete records more than 12 months old?
And that you want to prevent yourself from being able to do that 12 months
from now?

(by the way, the notion that records might need to be deleted is only rarely
true -- why do you think you would want to?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I don't think you are going to get a reply to your real question with this
many replies already. You have to word your question carefully or it will be
misunderstood. What you need is a validation calculation that will check the
date and mark it read only. I suggest you rephrase your question to
specifically ask how to write a this validation that checks the date in
another and after 12 months makes it write only. You will get a lot of
responses. Look up field validation so you know what I am talking about.
It makes a field obey some kind of business rules. Ask specifically how to
write this validation and where to put it in the field definition.

 

[Bob]

The records are payments made and I wanted to have a safety net so as if one
was altered or deleted I would not need to go further back than 12 months to
find it...Thanks bob

 

[John W. Vinson]

Sorry WinXp MS Access 2002
I have Records in a table that have a date next to them, what I wanted is
that they could not be deleted or altered after 12 Months...Thanks Bob

OK... let me see if I can interpret this correctly.

You want to be able to edit or delete records in your Table if those
records are newer than 12 months old, but older records should remain
unaltered and unalterable in the database forever, with no way to
change or delete them?

If so, I can't imagine any way to prevent a skilled and determined
expert from doing so. Access simply isn't Fort Knox.

If that is indeed what you're asking, about the best suggestion I can
make is that you use TWO backend databases: a current-data .mdb file
and an archive .mdb file. Implement Access security on both databases,
but lock down the archive backend really tight, so that only one job
can affect it at all; this job would be a monthly Append query to
migrate data older than 12 months using a criterion

< DateAdd("m", -12, Date())

to select the records. This query would be followed by a Delete query
to delete the archived records from the production frontend.

John W. Vinson [MVP]

 

[Bob]

Thanks John , I know just changing the computer clock would still allow me
to delete a record if it was further back than 12 months, but just wanted
some thing for safety for accidental Delete, In my Invoice code i have
something like this that does the same thing but only for 180 days back 6
Months:
Dim dDate As Date
Dim dtDiff
Dim nCountDaysOfYear As Long
Dim nLeapYearNow As Long, nLeapYearOfBillYear As Long
Dim nTotalDays As Long
dDate = Form_frmModify.lstModify.Column(3)
dtDiff = DateDiff("d", dDate, Date)

nLeapYearOfBillYear = DatePart("yyyy", dDate)
nLeapYearOfBillYear = nLeapYearOfBillYear Mod 4
nLeapYearNow = DatePart("yyyy", Date)
nLeapYearNow = nLeapYearNow Mod 4

If nLeapYearNow = 0 Or nLeapYearOfBillYear = 0 Then
nTotalDays = 180
Else
nTotalDays = 179
End If

If dtDiff > nTotalDays Then
cmdClose.SetFocus
subLockControls False, True
bLockFlag = True
Else
subLockControls True, False
End If

 

[John W. Vinson]

How can I stop records being deleted if they are older than 12 months old
Thanks For any help................Bob

Coming back to the thread...

A simple (albeit limited) approach would be to ensure that only a Form
can be used for such updating; use the Form's Current event to check
the value of the date field, and set the Enabled and Locked properties
of the relevant form controls appropriately (to prevent updating). Use
the form's BeforeDeleteConfirm event to cancel the Deletion.

John W. Vinson [MVP]