Solved

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

Posted on 2006-06-14
10
262 Views
Last Modified: 2008-03-10
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
Comment
Question by:wexomixo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 64 total points
ID: 16909130
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
 

Author Comment

by:wexomixo
ID: 16909310
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
 
LVL 77

Expert Comment

by:peter57r
ID: 16909445
Hi wexomixo,
Rockiroads code is all you need - it will work in a continuous form or a single form.

Pete
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:wexomixo
ID: 16909657
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 62 total points
ID: 16909759
'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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16909969
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 62 total points
ID: 16911151
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 62 total points
ID: 16913316
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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question