Solved

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

Posted on 2006-06-14
10
255 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
  • 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now