How can I show the edit history of individual records??

[supanoods]

I hope that I'm not re-asking a question from before - but I could'nt find this in the forums!

I have been asked to set-up a run-of-the-mill "action plan" dbase. Basically a manager can create a plan of work to do, and then his team can view/edit/create records of thier own accord.

However the tricky part (for me) is that the records should show who (and if possible - when) has last edited a record. For example: J.Bloggs creates record#1 12/02/05, but A.Nonymous edited record#1 13/02/05. and this should show on the record#1 form.

I am also attempting to gather the user name(s) from a "login" screen (another form) that should give the entered name as a value for the above example.

Can any one help/advise on the above - any tips will be GREATLY appreciated

"If it aint broke - dont fix it!

 

[PHV]

I am also attempting to gather the user name
If the database is secured: CurrentUser
If no win9x box, network login name: Environ("UserName")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BaudKarma]

I'm working on something similar for our Loss Prevention dept - they not only want to track who/when records were edited, they want to make sure that once text is added to a memo field, nobody can go back and change it.

I'm looking at displaying the selected field in read-only mode, with an "edit" button. Once the user goes into edit mode, they won't be able to exit the record without choosing "save" or "cancel". Save would commit the changes, and also put an entry in a seperate tblUpdates, which would hold UserID, RecordID, and a date/time stamp.

For the memo fields, I'll probably have a double-click on the field open a popup with a blank memo field. User types in their dialog, hits the save key, and some code will add that dialog to the end of the existing memo field along with a header that identifies user and date/time.


Just muddling my way through.

 

[traingamer]

BaudKarma,
Sounds like you need a separate memo table(s) with keys for memo type, user name, etc and date time stamps. Then link them back to your main records in subforms in chronological order. (Or concatenate them for display if that's part of the requirement).
That way it's MUCH easier to find entries from certain users or certain times, types etc. etc.
Normalize, normalize....

traingamer

 

[perrymans]

Method #1:

Use an unbound text box to type entries with the Memo field below (Enabled=No, Locked=Yes). Click the Add>> button to insert the text into the Memo field. You can set the Date and Time in VBA and connect the strings together, probably with a INSERT query.

Method #2:

traingamer has a pretty good idea with the subform of comments, and when you display it to the user, you simply concentate the recordset fields together into an unbound text box complete with carriage returnes.

I like method #2 because the flexibility is far greater for filtering and searching.

Sean.

 

[BaudKarma]

Searching within the memo field(s) won't be an issue, or so the users say. That said, the concatented subform method would seem to be a lot more flexible if/when the users change their minds at some future date. Any thoughts on performance issues? This is going to be a networked app, probably half-a-dozen users at any one time, but I don't expect any of them to be pounding the app heavily. Will concatenating several memo fields make for a major slowdown?



Just muddling my way through.

 

[blackarma]

We have what is called a "Journal" section or tblJournal that keeps track of who has done what and so forth. the popup form is linked to a certain person and when that person has any activity, calls in, requests something, has information updated, we not it i -n the popup form. The CurrentUser is auto entered along with the date/time. The user will pick a entry type and enter their information.

 

[perrymans]

Performance won't be an issue if the DB is designed right. If your form is pulling 1000 records at a time, it is going to be slow no matter what.

Make sure you are using the Client/ Server model where only the tables are on the server, and a front-end is placed on each users computer. If the front-end is on the server, you have already shot yourself in the foot.

Also, you can look at ulling records using ADO vice DAO. ADO stores the recordset in SYSTEM MEMORY. This means you can pull up the recordset, disconnect from the server, make all the changes you wish, and quickly since it is stored in RAM, then update back to the tables all at once.

And don't forget to turn off AutoNameCorrect in Options for real speed improvement.

Sean.

 

[obli]

Hmmm...

Judging by the responses maybe I'm missing something in the question but I'm wondering why no one has suggested an audit trail such as the one from the following site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AuditTrail.mdb

Just guessing.

 

[supanoods]

Thanks guys for all your help!

I downloaded the AuditTrail.mbd and I am picking the bones out of its idea!

Thanks again

Phasid!

"If it aint broke - dont fix it!