Audit Trail 1

[Shaz1]

Hello, I have no experience with VB or programming. I have copied some code from Candice Tripps link which is called Audit trail. I've added the modules as she shows in her example database and modified to suit. What I would like to do is on the form after a new record is inserted copy user name and changes to the Audit table. My form is designed for transactions purposes. The database is populated via the forms. I have 120 forms for this purpose.
Here is the code used:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()


Dim x As Integer

If Not IsNull(Me!ID) Then
x = WriteAudit(Me, Me!ID)
End If

Dim stDocName As String
End Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub
------------------------------------------------------------

Public Function WriteAudit(frm As Form, lngID As String) As Integer
On Error GoTo err_WriteAudit

Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean

bOK = False

DoCmd.SetWarnings False

' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO AUDIT ( ID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngID & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
MsgBox Err.Description
Resume exit_WriteAudit

End Function
-----------------------------------------------------------
When I select any control I receive "The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives."
*The expression may not result in the name of a macro, the name of a user-defined functon, or [Event Procedure]
*There may have been an error evaluating the function, event, or macro.

Any help on this problem would be greatly appreciated.

 

[Remou]

Hi
Here is one problem:

Private Sub Form_AfterInsert()


Dim x As Integer
    
    If Not IsNull(Me!ID) Then
        x = WriteAudit(Me, Me!ID)
    End If
     
    Dim stDocName As String
End Sub[blue]

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub[/blue]

You have code after an End Sub (in blue). It looks as if you may have pasted some code over other code. What errors do you get if you choose Debug, Compile?

 

[Shaz1]

Hi Remou,

I had actually pasted that code on the webpage only. I checked mine and it is okay.

After running Debug/Compile I get
Compile error:
Member already exists in an object module from which this object module drives.

I've looked for duplicates and can't seem to find any in that module.

 

[Remou]

Hi
I have tried the Audit code as shown above, and it works for me, though only if I put it in the Before Update Event, as it references Old Value. The above error implies that you have two things the same name or have named something with a reserve word. Was anything highlighted?

Having Trouble Getting Your Events to Work At All?
faq702-1443

 

[Shaz1]

Hi Remou,


I moved it to BeforeUpdate and I receive a runtime error '6'

The debug highlites:

x = WriteAudit(Me, Me!EquipID)

 

[Remou]

It is still working for me ... Can you post the code you are using at the moment, the name of your form and a sample EquipID?

 

[Shaz1]

Remou,

Form Name = F-View All Equipment Tags
EquipID is "1242516" or "367"

Error: Run-time error '6': Overflow
Code used on BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim x As Integer

If Not IsNull(Me!EquipID) Then
x = WriteAudit(Me, Me!EquipID)
End If
End Sub

Code used for General:
Public Function WriteAudit(frm As Form, lngID As Integer)
On Error GoTo err_WriteAudit

Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean

bOK = False

DoCmd.SetWarnings False

' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO AUDIT ( EquipID, FieldChanged, FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngID & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
MsgBox Err.Description
Resume exit_WriteAudit

End Function

 

[Remou]

Got it:
Public Function WriteAudit(frm As Form, lngID [blue]As Long[/blue])
lngID was Integer!

 

[Shaz1]

Remou,

You sure were right on that one! Thank you so much for your efforts on that one. I was still trying to change focus. Trying anything actually.

I found that it's not updating the table though. I'm wondering if it know which record to write to the Audit table?

 

[Remou]

Well, after all that, the code will only ever write changed records, not new records . So you might be better off looking at this:
Transaction Log for Ms. Access
faq181-291

 

[Shaz1]

Remou,

I'll try working with the new code tomorrow. But back to this one. It's not updating the changes to the Audit Table. I have a dummy database I tried it on and it's fine. But the live one *shrugs shoulders*

 

[Remou]

Bet you have the User field set that it will not take a zero length string and you have no User. Try commenting:
On Error GoTo err_WriteAudit
DoCmd.SetWarnings False
To see what it is bothered about.

 

[Shaz1]

Remou,

You definately close on that one. I added a watch after the commenting. I get Value <Out of context> and Type Empty

 

[Shaz1]

Remou,

It working.....just didn't look at the table this time! Need coffee. Thanks so much! Too bad I can't buy you a beer or coffee.

Cheers!

Two thumbs up!

 

[Remou]

Have you tried the debug.print on the SQL? The result can then be pasted into the query designer, to see exactly why / where it is failing.

 

[MichaelRed]

the basic process being used looks familiar. I believe it is a simplified version of what is found in faq181-291 perhaps, instead of fixing the snippet, you could look at / use the more complete version?




MichaelRed

 

[Remou]

MichaelRed
That's what I thought too, and recommended your solution in a post above (2 Nov 05 17:17). Shaz replied "I'll try working with the new code tomorrow." So hopefully ...

 

[MichaelRed]

So you did, sorry about the duplication, I just skimmed the thread and only noticed the similarity of the loop.




MichaelRed

 

[Datathumper]

Here is a question.

Using the audit trail code, how could a person add a revision number to the changes. Basically, every time the data on a record changes, I would like to update the revision number (another field in the record) to notify users that someone has edited the data?

Has anyone done this before? Is it possible? I am pretty green in the VBA code world, so please done beat me down if I asked the obvious.

Thanks.