• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

How can I prevent old records from being modified on a form?

This database is used as an electronic timecard.  Each record in the Work table contains how many hours a person worked on a certain date.

On the form used for entering data into this table, I would like to prevent an existing record from being modified it is over a week old. This is so that after payroll is complete for that week the data can't be changed.  I would prefer to somehow lock the records in the table itself but I understand that's impossible.

Also, on a side note, is there an easy way to include a field in the table that would be automatically updated with the latest time/date that the record was modified?

Thanks for your help.
0
wexomixo
Asked:
wexomixo
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
rockiroadsCommented:
well u could use the form's on current event

Private Sub Form_Current()

    if DateDiff("d",me.mydatefld,Now()) >= 7 then
        msgbox "Old Record"
        me.allowedits = false
        me.AllowDeletions = false
    else
        me.allowedits = true
        me.AllowDeletions = true
    end if
End Sub




Add a new field in  your table, say DateTimeStamp
add this to your form

then on forms beforeupdate

try this

private sub form_beforeupdate(cancel as integer)
     if Format(Me.DateTimeStamp,"YYYYMMDDHHNN") <> Format(Now(),"YYYYMMDDHHNN") then
            Me.DateTimeStamp = Now()
     end if
end sub
0
 
wexomixoAuthor Commented:
Wow, thanks for the quick reply.  How would I modify this code so that it runs when a record is actually selected or modified?  This is a continuous form that shows multiple records at the same time.
0
 
peter57rCommented:
Hi wexomixo,
Rockiroads code is all you need - it will work in a continuous form or a single form.

Pete
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wexomixoAuthor Commented:
On my form users have the option of looking up existing records using filters.  When I do a lookup I get the error message (msgbox "Old Record") from the code even though no record has actually been selected.  What I would like to do, if possible, is block users when they actually try to modify a record.  Blocking them when they select the record would also work.
0
 
peter57rCommented:
'even though no record has actually been selected'

If at least one record is visble then a record has been 'selected'. There is always a 'current record' - if the user has not clicked into one then it will be the first one in the list.

I'm sure Rocki only put the msgbox there to illustrate what the code was doing.  If you don't want the message then comment out or delete that line of code.

Pete
0
 
rockiroadsCommented:
Hi wexomixo

Pete is right about what he has said and also regarding the msgbox. That there was a diagnostic so you are aware that the code is supposed to be working properly. When your happy, just remove it.
0
 
jefftwilleyCommented:
Another perspective.....
Locking records in a database can mean a lot of things. flagging the record as having been dealt with by Payroll. A status field would do this. O for open, C for closed, etc.... I assume that you want to still show the record or records, that' why you simply want to disallow edits on the form? another option is to write records older than 1 week to an archived table, effectively securing them away from the user. rocki's suggesting works perfectly for what you originally explained.  Just some thoughts from this old dog.
J

0
 
harfangCommented:
Hello wexomixo

In fact, this can be done at the table level using validation rules.

datLastChanged
    Default Value: =Now()
    Validation Rule: >=Date()
    Validation Text: You cannot fake a record change in the past.
    Required: Yes

Table properties:
    Validation Rule: [datLastChanged]>Date()-7
    Validation Text: Record too old. It can no longer be edited.

This does not prevent the editing, only the saving of the record. So it's not a replacement of good form-level handling, only an additional safeguard agains editing directly in tables or bugs in the form.

Cheers!
(°v°)
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now