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 "beforeupdate" 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/libr...ffice.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()
 rs!PriorInfo = Me.ActiveControl.OldValue
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.ActiveControl.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 "ClaimDescrip"

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]

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

[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]

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 "TrackChanges" 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

[zmbd]

Here's somethng I've whipped up in my test database.

I have a table named "tbl_events"
I made an empty copy of "tbl_events" and renamed it "tbl_audit", change the [eve_id] from an autonumber to a number
Added two new fields [Audit_ID] as autonumber and primary key and [Audit_date] as a date field... should have added a third for the currently logged in user; however, that should be easy for you to do...

I have a form that is based on a query against the tbl_events with a control named "EVE_ID" and in that form the following code... :

Option Compare Database
Option Explicit
'
Private Sub Form_BeforeUpdate(Cancel As Integer)
'declare
Dim zdb As dao.Database
Dim zrs As dao.Recordset
Dim zssql As String
Dim zieid As Integer
Dim zddate As Date
'
'setup
Set zdb = CurrentDb
Set zrs = zdb.OpenRecordset("tbl_audit", dbOpenDynaset)
'
'set error trap
On Error GoTo z_bad_error_nonono
'
'set var
zddate = Format(Date, "mm/dd/yyyy")
zieid = Me![Eve_ID]
'
'build sql
zssql = _
    "INSERT INTO tbl_audit ( Eve_ID, Eve_Inv_ID, Eve_Sta_ID, Eve_Date, Eve_memo, Eve_Inls, Eve_WinUserID ) " & _
    "SELECT tbl_events.[Eve_ID], tbl_events.[Eve_Inv_ID], tbl_events.[Eve_Sta_ID], tbl_events.[Eve_Date], tbl_events.[Eve_memo], tbl_events.[Eve_Inls], tbl_events.[Eve_WinUserID] " & _
    "FROM tbl_events " & _
    "WHERE (((tbl_events.[Eve_ID])= " & zieid & "));"
'
'copy the current record to the audit table undo changes if there is an issue...
zdb.Execute zssql, dbFailOnError
'
'move to the last record in tbl_audit... should be the just created record
zrs.MoveLast
'
'open the record for edit and add the current date
zrs.Edit
zrs!audit_date = zddate
zrs.Update
'
z_clean_up_your_database:
zrs.Close
zdb.Close
Set zdb = Nothing
Set zrs = Nothing
Exit Sub
z_bad_error_nonono:
MsgBox Err.Number & vbCrLf & Err.Description
GoTo z_clean_up_your_database:
End Sub

Debugged and ran on 10 records in the test db w/o problems.

Now you have a copy of the old record with date changed... the rest is up to you :)

-z

[zmbd]

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.

Sorry missed that...
Either make a form that checks for null entry and locks the ok button until an entry is made...
or
place the input box in an infinate loop that checks for null or "space" and continues to loop.

I prefer the form as I have a listbox with the standard reasons; however I often use a combobox with the limit to list property set to false so that the user can enter a comment... in either case, there has to be a change in the control before the ok button unlocks and that change can not be a space charactor nor a null value or the button stays locked.
-z

[LeighW]

That looks great Z and a hell of a lot tidier I'll try your method now. The only thing is could I make the input form pop-up like the inputbox did as the update is about to occur? I take it it would be like a small data entry form which just writes to the other table (which would be great if it could do that as I could customise it a lot more).

[zmbd]

Yes,

With the form, you have to get the value back from it and have the code pause until the user supplies the information... this can be done by opening the form in dialog mode. Or you could have the form use a custom event, then we'd have to move some other code around... doable; However, If an input box will work, then I would use that... no need to re-invent the wheel. You can test the return from the input box in a loop and check for a non-zero length entry... even if it's just a few spacebar presses... at least the user had to press a key.

[LeighW]

After thinking about what we need archived and audited for future reports, we need need more than one text box to explain the reason for modification or adding a record. One for Reason (necessary), one for the Document used (Optional), one for the signifiance (yes/no) and if yes is entered then one for Impact.

This will then allow us to view why the changes that were made and the significance of the changes for audits and reports on how it is progressing.

Thinking about that data (which will essentially be a one to many with the main form) a dialog form that allows the text and yes/no fields to be tagged to the end of the record entered into the archive table (which is occuring on the BeforeUpdate event of the main form as above) would be great. I've created a dialog form like that bound to the archive (audit) table but at the moment it is entering the data into a second record rather than the same record as the BeforeUpdate process on the main form.

So after a modified record is saved in the table "Audit" I get:
First Row: FormName, DateChanged, TimeChanged, ClaimNo, Claim, ClaimDescrip, Permit, CurrentUser
Second Row: Mod_Reason, Mod_Doc, Mod_Significance, Mod_Impact

Obviously what I want to happen is for all the data to get saved into one row rather than two rows. It's occuring cause the BeforeUpdate process is happening before the Dialog Form.

I have no idea how to call the data into same row of the table when the form updates.

The other way as it is a one-many would be a subform (that is only accesible when you are editing or adding a record) which writes to the same record in the audit table. The problem as I said before is that the main form updates when the subform gains focus so again it would save to a different row...

I'm close but this is starting to ache my brain!

[LeighW]

I thought of a way of doing it and that is using an unbound dialog form which has the required textboxes, etc in. Once you press submit it makes the dialog form invisible.

On the form there are hidden textboxes linked to the dialog form via the expression Forms!frm_ClaimQA!Reason. The form then has a value which is saves to the audit table on update. After update the dialog form closes and resets. Works alright actually! Users will have to enter data into the dialog form as I'll disable the submit button until the controls have been changed.

[zmbd]

I'll keep an eye on the thread for little while to see how you progress.
-z