Hi all can you assist with this problem please , I have a database this a main form [mainfrm]and two subforms [subform]and [ work details], when I open the main form I keep the subforms hidden until a button is clicked , the subforms are connected via a one to many relationship, all works great ,
then I came across this audit trail demo http://www.fontstuff.com/access/acctut21.htm
this is a great demo , and I have tried to add it to my database , this is where the problem starts , it will record any amendments I do to the main form but not to the subforms even though I have carried out the same instructions as on the main form, I have tried contacting the developer of this but as of yet I have no response . can you see where I am going wrong please or is their another demo you know of that will do this and is easy to add to a database?
hello made some changes, add another parameter, the form where you want to audit, or something:
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("EmployeeID", "DELETE", Me.Form)
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("EmployeeID", "NEW", Me.Form)
Else
Call AuditChanges("EmployeeID", "EDIT", Me.Form)
End If
End Sub
the module basAudit should look like this:
Code:
Option Compare Database
Option Explicit
'
' ================================================
' Code by Martin Green Email: martin@fontstuff.com
' Visit my Office Tips website @ www.fontstuff.com
' YouTube tutorials www.youtube.com/martingreenvba
'
' modified by arnelgp
' 19 january 2016
' ================================================
'
Sub AuditChanges(IDField As String, UserAction As String, f As Access.Form)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
'For Each ctl In Screen.ActiveForm.Controls
For Each ctl In f
If TypeOf f Is SubForm Then
Else
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
'![FormName] = Screen.ActiveForm.Name
![FormName] = f.Name
![Action] = UserAction
'![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![RecordID] = f.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
'![FormName] = Screen.ActiveForm.Name
![FormName] = f.Name
![Action] = UserAction
'![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![RecordID] = f.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub
hello arnelgp, thank you for your solution , it works a dream, just a few changes , well done , I have noticed that if you delete a record/row in the subform that it shows we have deleted a row but does not show what was in that row, so if this was accidently deleted I cannot replace it , is it possible to store the deleted row to replace in the event of deleting the wrong row/record
I love the changes you made to this code to make it more comprehensive. I implemented the same code from http://www.fontstuff.com/access/acctut21.htm only to discover that it's recording the incorrect ID of deleted records. When you delete a record, it records the RecordID of the next record in lieu of the record that's actually deleted. I have posted this question to another board at http://www.utteraccess.com/forum/index.php?showtopic=2043062&st=0&p=2640184&#entry2640184 with no results. Everyone seems to have a audit trail of their own but I have had issues with them all. The code from FontStuff is the closes to my needs.
Do you have any idea what is missing from this code so it stores the correct ID of deleted records?
Margaret,
I looked at the code and article on Martin Green's site (his listing 4) and note that he does not store details for a deleted record. His sample shows record id 723 DELETED. My guess (and it's only a guess) is that if you have the audit trail in place, then all additions and modifications to record id 723 will be in the audit trail. And that's how you would reassemble a deleted record if such recovery was ever needed.
But, if you are saying --OK that's fine -- but it really isn't record id 723 , it should be record 722, then that's another story.
Do you have a test copy of your database -something with only a few records that can be used to delete a known recordId. Please post a zipped copy.
That's what I'm saying. If you delete record 722, it's recording record 723.
At the bottom of Martin Green's tutorial are two samples in zip files. I'm interested in the AccessAuditTrail_Detailed version.
It seems that the AfterDelConfirm triggers the Sub AuditChanges but by then the record is already deleted so it's recording the ID of the next record. I, unfortunately, don't know enough about VBA yet to correct or add code to fix this issue.
Margaret,
I downloaded his detail example database.
I modified the ADD and made a DELETE user action so that the details of a NEW record or a DELETE record would be recorded in the audit log.
I do not see the issue with the RecordID being incorrect.
However, I do not see how he activates the Delete or ConfirmDelete type of event in his sample database, nor how his form is supposed to be used for Adding, editing, deleting records. But that is not the main point of his audit trail approach. He calls the table tblCustomers, and the PK CustomerID, but the rest of the fields are named Publisher...??
If your record is already deleted when you write the audit record, then your Delete is in the wrong place (in my view), or there is mix up in the logic. If you delete the physical record, then attempt to get info about that record--the record no longer exists. So, I suggest capturing the info for the delete, writing the record, then delete the physical record.
If you have a sample database, and post it i will look.
Here is part of the audit log showing where I added 33 and deleted 33. Note I have been playing with Delete code so it shows up multiple times in the log. My point is, I'm showing field values for ADD, and DELETE actions.
Code:
AuditTrailID DateTime UserName FormName Action RecordID FieldName OldValue NewValue
16 02-Apr-2017 9:42:26 AM mellon frmCustomers EDIT 32 PublisherName MyPublishing Inc MyNew Publishing Inc
17 02-Apr-2017 9:42:26 AM mellon frmCustomers EDIT 32 AddressLine2 Bldg 6 Bldg 6A
18 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 PublisherName Customer32 NEW
19 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 AddressLine1 333 Second Avenue
20 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 AddressLine2 Attention: JBHoldings
21 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 City Toronto
22 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 State Ontario
23 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 Postalcode K7T1K8
24 02-Apr-2017 9:44:02 AM mellon frmCustomers NEW 33 Country Canada
25 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 PublisherName Debuggers Anonymous
26 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 AddressLine1 123 First Street
27 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 AddressLine2 FlexParkway Industrial Park
28 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 City Orlando
29 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 State Florida
30 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 Postalcode 33766
31 02-Apr-2017 9:51:41 AM mellon frmCustomers NEW 34 Country USA
32 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 AddressLine1 333 Second Avenue
33 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 AddressLine2 Attention: JBHoldings
34 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 AddressLine3
35 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 City Toronto
36 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 State Ontario
37 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 Postalcode K7T1K8
38 02-Apr-2017 10:45:04 AM mellon frmCustomers DELETE 33 Country Canada
39 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 PublisherName Customer32 NEW
40 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 AddressLine1 333 Second Avenue
41 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 AddressLine2 Attention: JBHoldings
42 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 AddressLine3
43 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 City Toronto
44 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 State Ontario
45 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 Postalcode K7T1K8
46 02-Apr-2017 10:48:59 AM mellon frmCustomers DELETE 33 Country Canada
Thanks for your assistance. I'm not just deleting a field, I'm deleting an entire record and then it's recording the wrong RecordID. Even Mr. Green's sample database is recording the wrong ID of a deleted record.
Unfortunately, I can't send you my database because it contains confidential information. If you know a fix, could you implement it in Arnel's code above?
Actually, when I delete a record it's only adding one line to the Audit Trail table with the Date/Time, User Name, FormName, RecordID (Wrong ID), and Action of "Delete". It's not listing each of the record's fields on a separate line marked with "Delete" and the old value.
For the test database I was clicking on the recordselector and pressing the delete key, however, wouldn't a delete no matter what caused it behave the same way, e.g. I used the recordselector or if I inserted a command button on a form to delete a record?
Yes, that's exactly what I'm wondering.
But I'm not sure how to delete the record with a button or some control.
I'm not getting a delete of the record by choosing the record selector and pressing DELETE key???
I can delete a record from the underlying table, but that bypasses all the form events, so nothing gets logged.
Ok.
I am returning your zip file. I have modified the database you sent, but have not changed the name. I added a delete button to the form and added some additional events based on what I found on google. There are some comments within the code.
I suggest you try the revised database and see if it meets your needs. I did add code to show all 'field" values for ADD and DELETE actions.
Good luck.
I'm sure there are better methods - perhaps the class approach suggested by Jon on the other forum.
In the database you returned, I can delete a record with no issues using the command button you inserted. For curiosity sake, I clicked the Record Selector in the form for a record and hit the delete key and received this error message "The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time." The record appeared to still be in the form, but when I clicked on any of the fields, they changed to #Deleted. I closed the form and opened it again and the record was deleted and recorded correctly in the audit log. While I would never have a user delete a record in this method, I'm curious if there is still an underlying issue with this code?
You may be right that there could be better methods out there, but I have experienced issues with a number of them. Allen Browne's method doesn't support cascading updates or deletes and you had to have two extra audit tables for every table you want to audit which to me was too much.
I experienced one issue after another with Jon Smith's method. He did reply on a different forum that he was going to post an update to his method.
In your previous message #8, you indicated that you downloaded Mr. Greene's database and everything work correctly for you. Is that accurate or did you also experience issues with deleting records? The log you posted in that message appeared correct. Can you send me the database you downloaded and used to create that post?
The code I gave in the returned zip works with the data and form you sent, I think.
I had issues with the detailed sample from Martin Green --recordids as you found also.
I'm still not following exactly what you are doing in this
For curiosity sake, I clicked the Record Selector in the form for a record and hit the delete key and received this error message "The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."
My best guess is that the form should be requeried after doing the physical Delete. Since the form is open to that record, you will see the data, and it isn't until the form/screen is refreshed that the new data (minus the deleted record) will be available for display. If you don't move off that record, you will see #Deleted.
I added the Me.Requery line in this procedure
Code:
'-----jed
Private Sub Form_AfterDelConfirm(Status As Integer)
Select Case Status
Case acDeleteOK
'
'ok delete was confirmed so add info to audit log
Call AuditChanges("ID", "DELETE", Me.Form) 'updated thee audit log
'then delete the record from the table
Debug.Print "Physical delete of record id:" & Me.ID 'for debugging can be deleted or commented
CurrentProject.Connection.Execute "Delete from tblMain where id =" & Me.ID & ";", dbFailOnError
[B] Me.Requery[/B]
MsgBox "Deletion occurred normally." 'system message that could be commented
Case acDeleteCancel
MsgBox "Programmer canceled the deletion." 'system message that could be commented
Case acDeleteUserCancel
MsgBox "User canceled the deletion." 'system message that could be commented
End Select
End Sub
Good luck with your project.
I have not tried Jon's class approach nor the Allen Browne example. I have seen posts where others have had issues with the Allen Browne version.
or is their another demo you know of that will do this and is easy to add to a database?
