?
Solved

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

Posted on 2006-06-14
10
Medium Priority
?
269 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 256 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 248 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 248 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 248 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

Industry Leaders: 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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

801 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