Record the date time a record has been last edited

[gmurph]

I am using an access database backend. I want to record 'date' and 'time' when a record has been updated.

I have tried
1. CanInsertRecord event ... this works on new records but get upset when I try and update the field when I edit the record
2. onAfterSQLCommandExecute Where I have some code run that updates the data after each time the record is updated...
I could not get this to work!
dim cn as sql::connection
dim cs as c
dim sql as c
dim flag as l
dim args as sql::arguments

cs = e.cn
flag=cn.Open(cs)
sql = "UPDATE ProspectCompany Set LastChange = :nowarg WHERE " + e.primaryKeyWhereClause
args.XML = e.argsXML
args.add("nowarg",convert_type(now(),"D"))
flag = cn.Execute(sql,args)

3. The next possibility is to use Java to update the value of the 'LastChange' field when any other field is edited... Does anyone have code for that

OR...... this seems to be usual for me in Alpha5.... I usually find there is a REALLY simple way to do things... i.e. a checkbox hidden somewhere!

i.e. what is the simplest way to have a field that records the datetime of the last time the record was edited... that's all I'm trying to do..

Thanks

[chadbrown]

Re: Record the date time a record has been last edited

I don't know if its possible with access but with MySQL you can simply have a field that is a time-stamp and set it to update automatically on any update from alpha or directly updating records.

[JimDK]

Re: Record the date time a record has been last edited

You can put the date and time into the record on the 'CanUpdateRecord' event you don't need to do any coding, se example.
Snap9.jpg

[George Frankland]

Re: Record the date time a record has been last edited

What I do is append three fields to all my tables and allow MySQL to update the fields via a series of triggers and procedures. Attached is the SQL for the 'tbl', tiggers and procedure creation.

CREATE TABLE tbl (
ID int AUTO_INCREMENT NOT NULL,
.
.,
Date_Created datetime,
Date_Edited datetime,
Date_Deleted datetime,
/* Keys */
PRIMARY KEY (ID)
) ENGINE = InnoDB
CHARACTER SET ascii COLLATE ascii_bin;

ALTER TABLE tbl
AUTO_INCREMENT = 1000000;

CREATE TRIGGER tbltInsert
BEFORE INSERT
ON tbl
FOR EACH ROW
BEGIN
/* Trigger text */
SET NEW.Date_Created = CURRENT_TIMESTAMP();
SET NEW.Date_Edited = CURRENT_TIMESTAMP();
END;

CREATE TRIGGER tblUpdate
BEFORE UPDATE
ON tbl
FOR EACH ROW
BEGIN
/* Trigger text */
SET NEW.Date_Edited = CURRENT_TIMESTAMP();
END;

CREATE PROCEDURE tblDelete
(
IN ID int
)
BEGIN
/* Procedure text */
UPDATE
tbl
SET
Date_Deleted = CURRENT_TIMESTAMP()
WHERE
tbl.ID = ID;
COMMIT;
END;

CREATE PROCEDURE tblUndelete
(
IN ID int
)
BEGIN
/* Procedure text */
UPDATE
tbl
SET
Date_Deleted = NULL
WHERE
tbl.ID = ID;
COMMIT;
END;

CREATE PROCEDURE tblZap ()
BEGIN
/* Procedure text */
DELETE
FROM
tbl
WHERE
tbl.Date_Deleted IS NOT NULL;
COMMIT;
END;

When the record is created 'inserted' the 'tblInsert' trigger sets the Date_Created and Date_Edited fields, and when the record is 'updated' the 'tblUpdate' trigger sets the Date_Edited field. The 'tblDelete' procedure I call when the user deletes the record and this sets the Date_Deleted field. The record is not really deleted but the filter on my grid filters records on Date_Deleted = NULL for users allowing administrators to undelete the record using the 'tblUndelete' procedure. I run the 'tblZap' procedure once a month to delete those records permanently.
The advantage of using JimDK�s approach is that you can get the username field updated as well. I have also noticed that when uploading an image, using Image Upload feature pack, it does not fire the �tblUpdate� trigger. I find this very odd.
I hope this helps.