Archiving records which have been edited/added/deleted

[LeighW]

Hello,

Ok this is quite a complicated question and I'm not sure how to go about it...

At the moment I have Form 1 which is based on Table 1. I am required to create a method on tracking any edits/additions/deletions with a reason why. Currently I have a way of only allowing the user to press on a Submit button and disabling navigation buttons when edit is pressed or a new record event occurs. This submit button makes the form footer appear where the save button currently hides. Once saved the form footer then disappears again and the buttons return to enabled.

I created this format to add in fields of who/when/why the person is editing for quality assurance reasons when I knew more about what needed to be added. The fields within Form 1 need to be archived with the editing reasons within a table.

I thought maybe a subform would work within the form footer for the editing fields as they work on a one-many relationship with each record. There are two problems with this method.

1 - Being that I need to bypass the update which occurs when the subform gains focus and have the save occur with both the form and subform.

2 - I have no idea how to keep the old records from Table 1 each time, i.e. If you need to edit a field in Form 1 and have previously edited it before that how could I go about saving all the editable fields within Table 1 with a new record and not overriding the old one before edits?

If anyone can understand this then good on you. If you can think of anyway to help me then even better.

Leigh.

[ariful alam]

you can copy the edited/deleted data into another table named like operation_log with the reason of edit/delete, user who edited/deleted etc and after that run the edit/delete code.

for add i don't think u need to copy the data in operation_log table, but u can store some information that who is adding new data along with new data.

hope it's work. :!

[LeighW]

Thanks Ariful.

I've looked around and most people that use Audit Trails use Modules. The problem with the Modules is that I want the user to be able to add the reason why themsleves and enter the document they are using for the change. The Module is more automatic, great for tracking the user and where the change is made but I'd like that and a WHY the user is changing data.

[ariful alam]

for edit/delete you can use query (delete/update query) means the form for edit/delete will be a separate form from the form of adding new data. And you can add a extra field in edit/delete form named remarks. And you can add two button there named update and delete. whenever user click on update/delete button those will check the remarks field is filled up or not. if filled up then the query to copy of that data from the actual table will happen with the remarks data. And after that the update/delete query will execute.

hope it's help.

[zmbd]

I have used the "beforeupda te" event in the subform for such audit trails. Usually I have the event automatically recorded; however, you could also use an input box or a form popup.

This is for ACC2000 however the principal is the same.

http://msdn.microsoft.com/en-us/library/aa158559(v=office.10).aspx

[LeighW]

I've found some code which would work perfectly for me and wouldn't require a subform. However, it doesn't seem to be working!

Here's the code which I entered into the form's module:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
[U][B]rs!PriorInfo = Me.ActiveControl.OldValue[/B][/U]
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

The error is related to the line highlighted in bold within the code (rs!PriorInfo = Me.ActiveContro l.OldValue). It states "You have entered an expression that has no value".

In the BeforeUpdate event on the form I've put the code:

   
Private Sub Form_BeforeUpdate(Cancel As Integer)
Claim = TrackChanges()
ClaimDescrip = TrackChanges()
End Sub

Which should hopefully track the changes of the controls "Claim" and "ClaimDescr ip"

I've created a table named "Audit" and the field names are FormName, ControlName, DateChanged, TimeChanged, PriorInfo, NewInfo, CurrentUser, Reason.

I've not really used functions and modules before so it's going out of my ballpark but I need a system that works well as the QA part is very important!

So back to my previous question why am I recieving the error?

Leigh

[ariful alam]

zmbd can answer this. but i can't :(. as i don't use coding in Access. over to ZMBD. :)

[LeighW]

I've actually worked it out. It's because I needed to add it into the BeforeUpdate section of each of the controls and not the main form. The problem is that it saves to the table every time the control you are editing loses focus when I'd prefer it to save after I've saved the whole record as there maybe more than one control you are changing and you may want to undo the change.

Almost there though!

[zmbd]

Try the form's events.

Remove the code from each of the control events.
Place the code in the form's events:

[imgnothumb]http://bytes.com/attachment.php? attachmentid=64 71&d=134201531 2[/imgnothumb]

[LeighW]

That's what I did before zmbd but it was coming up with the error shown in post #7

[LeighW]

I've got it now no worries. Works like a beast. Had to change the "ActiveControls " within the code to my actual control names. Now it saves every editable control to a table which is what I wanted in the first place.

[zmbd]

form level detail before update

I don't have that problem in my test nor production DB... However, I don't use the controls directly in my new record code as you have in lines 37 and 38. Instead, I like to pull the form values into variables. Allows me to play with them before using them... just the change in the last field to have the focus.

So using my method with your code you'd get something along the lines of:

'...lots of stuff before these lines...'
Dim z_s_ctrloldval As String
Dim z_s_ctrlnewval As String
'...lots more stuff between these lines'
z_s_ctrloldval = Nz(Me.ActiveControl.OldValue,"was null")
z_s_ctrlnewval = Nz(Me.ActiveControl.Value,"now null")
'...and yet more code before we get to...
z_audit.AddNew
   z_audit![auditdate] = z_d_currentdate
   z_audit![auditformnname] = z_s_formname
   z_audit![auditcontrol] = z_s_controlname
   z_audit![auditoldvalue] = z_s_ctrloldval
   z_audit![auditnewvalue] = z_s_ctrlnewval
   z_audit![audituser] = z_s_logeduserid
z_audit.Update
'... and then stuff to close the record set

The issue with this approach is that only the change in the active control is logged. If you have 6 fields and make changes in all 6 fields, then you will not have an audit for all of them...

If you need to have a record of each field's changes then we either need to place the code at the control level or some sort of temp holding area for the record. would be nice to have the changes at a transaction level; however, I don't think that is possible.

ok... Grimlins requiring my love and attention in the lab...
-z

[LeighW]

Yeah saving for each control is a problem, I'd prefer the save to the other table to occur when the record is saved it makes much more sense to me.

I've changed the coding to this:

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason for Changes")
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!ClaimNo = Me.PK_ClaimNo.Value
rs!Claim = Me.Claim.Value
rs!ClaimDescrip = Me.ClaimDescrip.Value
rs!RSR = Me.RSR.Value
rs!OWDA = Me.OWDA.Value
rs!CWDA = Me.CWDA.Value
rs!CA = Me.CA.Value
rs!HSC_COMAH = Me.HSC_COMAH
rs!Other = Me.Other.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

With "TrackChang es" within the BeforeUpdate Event on the form

I basically listed all controls rather than the active control. You may see this as a disadvantage as it would save all controls and not just the one(s) edited, however due to the nature of the database I need to be able to recover old records in full to create full reports of records which have been approved. Newly edited/added data may not have been approved so we have to go back to the archived data.

The one problem I have is that the "Reason for Editing" bit is entered using a InputBox. I don't like the look of it and the user can click Cancel which I do not want as it's necessary data.

[zmbd]

There has to be a more elegant solution... (maybe just me).
I haven't looked yet, however, I would think that you could take the current record, and somehow in the before update, copy that record to a backup table...

Let me think about that for awhile and in the meantime maybe one of the others has an idea.

-z

[zmbd]

I don't suppose you've worked with insert queries?
-z