By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Archiving records which have been edited/added/deleted

P: 73

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.

Jul 10 '12 #1
Share this Question
Share on Google+
21 Replies

ariful alam
P: 185
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. :!
Jul 10 '12 #2

P: 73
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.
Jul 11 '12 #3

ariful alam
P: 185
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.
Jul 11 '12 #4

Expert Mod 5K+
P: 5,397
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.
Jul 11 '12 #5

P: 73
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:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  3. "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  5. Function fOSUserName() As String
  6. ' Returns the login name for Adminstrator use
  7. Dim lngLen As Long, lngX As Long
  8. Dim strUserName As String
  9. strUserName = String$(254, 0)
  10. lngLen = 255
  11. lngX = apiGetUserName(strUserName, lngLen)
  12. If (lngX > 0) Then
  13. fOSUserName = Left$(strUserName, lngLen - 1)
  14. Else
  15. fOSUserName = vbNullString
  16. End If
  17. End Function
  19. Function TrackChanges()
  20. Dim db As Database
  21. Dim rs As Recordset
  22. Dim strSQL As String
  23. Dim strCtl As String
  24. Dim strReason As String
  25. strReason = InputBox("Reason For Changes")
  26. strCtl = Me.ActiveControl.Name
  27. strSQL = "SELECT Audit.* FROM Audit;"
  28. Set db = CurrentDb()
  29. Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  30. If rs.RecordCount > 0 Then rs.MoveLast
  31. With rs
  32. .AddNew
  33. rs!FormName = Me.Name
  34. rs!ControlName = strCtl
  35. rs!DateChanged = Date
  36. rs!TimeChanged = Time()
  37. rs!PriorInfo = Me.ActiveControl.OldValue
  38. rs!NewInfo = Me.ActiveControl.Value
  39. rs!CurrentUser = fOSUserName
  40. rs!Reason = strReason
  41. .Update
  42. End With
  44. Set db = Nothing
  45. Set rs = Nothing
  46. 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:

Expand|Select|Wrap|Line Numbers
  2. Private Sub Form_BeforeUpdate(Cancel As Integer)
  3. Claim = TrackChanges()
  4. ClaimDescrip = TrackChanges()
  5. 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?

Jul 11 '12 #6

ariful alam
P: 185
zmbd can answer this. but i can't :(. as i don't use coding in Access. over to ZMBD. :)
Jul 11 '12 #7

P: 73
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!
Jul 11 '12 #8

Expert Mod 5K+
P: 5,397
Remove the code from each of the control events.
Place the code in the form's events:

Attached Images
File Type: jpg form_update_event.jpg (35.0 KB, 1126 views)
Jul 11 '12 #9

P: 73
That's what I did before zmbd but it was coming up with the error shown in post #7
Jul 11 '12 #10

P: 73
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.
Jul 11 '12 #11

Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. '...lots of stuff before these lines...'
  2. Dim z_s_ctrloldval As String
  3. Dim z_s_ctrlnewval As String
  4. '...lots more stuff between these lines'
  5. z_s_ctrloldval = Nz(Me.ActiveControl.OldValue,"was null")
  6. z_s_ctrlnewval = Nz(Me.ActiveControl.Value,"now null")
  7. '...and yet more code before we get to...
  8. z_audit.AddNew
  9.    z_audit![auditdate] = z_d_currentdate
  10.    z_audit![auditformnname] = z_s_formname
  11.    z_audit![auditcontrol] = z_s_controlname
  12.    z_audit![auditoldvalue] = z_s_ctrloldval
  13.    z_audit![auditnewvalue] = z_s_ctrlnewval
  14.    z_audit![audituser] = z_s_logeduserid
  15. z_audit.Update
  16. '... 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...
Jul 11 '12 #12

P: 73
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:

Expand|Select|Wrap|Line Numbers
  1. Function TrackChanges()
  2. Dim db As Database
  3. Dim rs As Recordset
  4. Dim strSQL As String
  5. Dim strCtl As String
  6. Dim strReason As String
  7. strReason = InputBox("Reason for Changes")
  8. strSQL = "SELECT Audit.* FROM Audit;"
  9. Set db = CurrentDb()
  10. Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  11. If rs.RecordCount > 0 Then rs.MoveLast
  12. With rs
  13. .AddNew
  14. rs!FormName = Me.Name
  15. rs!DateChanged = Date
  16. rs!TimeChanged = Time()
  17. rs!ClaimNo = Me.PK_ClaimNo.Value
  18. rs!Claim = Me.Claim.Value
  19. rs!ClaimDescrip = Me.ClaimDescrip.Value
  20. rs!RSR = Me.RSR.Value
  21. rs!OWDA = Me.OWDA.Value
  22. rs!CWDA = Me.CWDA.Value
  23. rs!CA = Me.CA.Value
  25. rs!Other = Me.Other.Value
  26. rs!CurrentUser = fOSUserName
  27. rs!Reason = strReason
  28. .Update
  29. End With
  31. Set db = Nothing
  32. Set rs = Nothing
  33. 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.
Jul 11 '12 #13

Expert Mod 5K+
P: 5,397
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.

Jul 11 '12 #14

Expert Mod 5K+
P: 5,397
I don't suppose you've worked with insert queries?
Jul 11 '12 #15

Expert Mod 5K+
P: 5,397
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... :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. '
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5. 'declare
  6. Dim zdb As dao.Database
  7. Dim zrs As dao.Recordset
  8. Dim zssql As String
  9. Dim zieid As Integer
  10. Dim zddate As Date
  11. '
  12. 'setup
  13. Set zdb = CurrentDb
  14. Set zrs = zdb.OpenRecordset("tbl_audit", dbOpenDynaset)
  15. '
  16. 'set error trap
  17. On Error GoTo z_bad_error_nonono
  18. '
  19. 'set var
  20. zddate = Format(Date, "mm/dd/yyyy")
  21. zieid = Me![Eve_ID]
  22. '
  23. 'build sql
  24. zssql = _
  25.     "INSERT INTO tbl_audit ( Eve_ID, Eve_Inv_ID, Eve_Sta_ID, Eve_Date, Eve_memo, Eve_Inls, Eve_WinUserID ) " & _
  26.     "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] " & _
  27.     "FROM tbl_events " & _
  28.     "WHERE (((tbl_events.[Eve_ID])= " & zieid & "));"
  29. '
  30. 'copy the current record to the audit table undo changes if there is an issue...
  31. zdb.Execute zssql, dbFailOnError
  32. '
  33. 'move to the last record in tbl_audit... should be the just created record
  34. zrs.MoveLast
  35. '
  36. 'open the record for edit and add the current date
  37. zrs.Edit
  38. zrs!audit_date = zddate
  39. zrs.Update
  40. '
  41. z_clean_up_your_database:
  42. zrs.Close
  43. zdb.Close
  44. Set zdb = Nothing
  45. Set zrs = Nothing
  46. Exit Sub
  47. z_bad_error_nonono:
  48. MsgBox Err.Number & vbCrLf & Err.Description
  49. GoTo z_clean_up_your_database:
  50. 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 :)

Jul 11 '12 #16

Expert Mod 5K+
P: 5,397
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...
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.
Jul 11 '12 #17

P: 73
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).
Jul 12 '12 #18

Expert Mod 5K+
P: 5,397

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.
Jul 12 '12 #19

P: 73
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!
Jul 13 '12 #20

P: 73
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.
Jul 13 '12 #21

Expert Mod 5K+
P: 5,397
I'll keep an eye on the thread for little while to see how you progress.
Jul 13 '12 #22

Post your reply

Sign in to post your reply or Sign up for a free account.