Storing form history

[Jdreyfus]

I've searched around a bit and couldn't find any helpful information on storing a history of changes within a form.

I'm wondering if anyone has any idea of what the possibilities are of storing a history of revisions within a form? In my database I have a form with a precreated tab labeled comments. Within it are two text boxes, one labeled 'New Comment' and other 'Comments History'. Not sure exactly how they were supposed to work, but I imagine that I screwed something up, because entering something within the new comment text box does not store a history of that something within the Comments History textbox. Is anyone familiar with this sort of thing?

Anyway, it also got me thinking that a general history of fields within the entirety of this form would come in very handy. Has anyone ever archived a history in this manner? If so, how would I go about it?

Thanks a lot for anyones input.

 

[Jdreyfus]

Didn't think the check the template I originally worked off of, fixed the textbox thing with: =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

Still would like to know peoples thoughts on how to go about saving a record of any changes to a record within a form though.

 

[dkinley]

In regards to your historical memo fields .... I use something similar to mine to track major changes in a records history (last modified by/date, status by/date, deleted by/date, etc.) This only records changes to the data in the record not to actual design changes of the form.

To do this I have a global variable that uploads the user's name during login (Access 2007). Since I dumb down my forms, the users have to have met certain criteria to exit a form. Once I have funnelled them and they actually 'Save' the form I have a quick sequence that goes along the following ... (this example is for deleting a record)

sMsg = InputBox("Input the reason for the deletion of this record.", "Delete Record")
If Len(Nz(sMsg, "")) = 0 Then
MsgBox "You must enter a reason for the deletion." & _
vbCrLf & "Cancelling the deletion.", , "Cancel Delete"
Else
If Len(Nz(Me!txtNotes, "")) = 0 Then
Me!txtNotes = "- Deleted on " & Format(Now(), "mm/dd/yy hh:mm ") & _
" by " & GBL_Name & "." & vbCrLf & "Reason: " & sMsg
Else
Me!txtNotes = Me!memNotes & vbCrLf & "- Deleted on " & _
Format(Now(), "mm/dd/yy hh:mm ") & " by " & GBL_Name & "." & _
vbCrLf & "Reason: " & sMsg
End If

Me!txtStatus = "DEL"
Me!txtModifiedDate = Now()
Me!txtInitials = GBL_sInitials
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

The user's aren't allowed to edit txtNotes and it provides traceability information for administrators. Of course, this tracing modifications (simple data changes, not status changes) only has an age of 1 change, unless I copied to another DB to keep an actual change history for forensics.

-dK

 

[missinglinq]

Maintaining any kind of audit log can be a huge drain on resources, and like most security strategies, should only be done when there's a very good reason! After all, what you're basically doing is doubling your apps work. If you edit a field, you're actually editing a field, writing that to a table and making a notation that the edit has been done and writing that to a table. This could cause a big drag in performance in large multi-users systems.

 

[Jdreyfus]

dkinley, thank you, I'll have to study your example more closely to see whether I can attempt something similar.

missinglinq, I can understand how quickly such a process would balloon the db outward, fortunately at most there'll only be 5 users, and I really only want a record of about 7 fields, most of which won't change at all. The database I'm using is an asset tracking and management design, so mainly the fields I'll be concerned with are location and condition/repair history. I work in an AV department at a community college, so certain technologies we've implemented are often swapped from room to room as necessary. So a history that I could potentially query would allow me some very necessary insight, for example, if we're constantly having to repair projectors assigned to a specific location, then the projectors might not be the issue, there may be faulty wiring, or improper ventilation. So that's really why I'm eager to bother with the risks of logging form history.

 

[Jdreyfus]

Is there any VB Process that does something similar to "=ColumnHistory([RecordSource];"Location History";"[ID]=";0))" ?

I'm thinking now that I really only want a record of the comments and past locations, I've been reading a bit about how =ColumnHistory works and it seems like it's very difficult to clear out outdated history. I'm wondering if there is a vb script that will record a history of changes within some parameter, like say 10 moves or 5 years?