Solved

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

Posted on 2006-06-14
10
254 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 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

14 Experts available now in Live!

Get 1:1 Help Now