audit trail issues

[cdmonroe]

I'm implementing someone else's code for creating an audit trail for data edits. I REALLY need this or something similar to track changes made to my the data in my forms.

The first time I put it in, it worked great... I tried in another database and started getting ByRef argument type mismatch errors... I had to go back and modify the original database and now I'm getting "Compile Error: type mismatch" errors. I'm going insane with this!
I'm a very novice programmer...

Thanks in advance for any insight!



Here's the code I'm using:

In the form:

[CODE=vb]Private Sub Form_BeforeUpda te(Cancel As Integer)
Call AuditTrail(Me, RecordNo) 'RecordNo is the primary key for each table
End Sub[/CODE]
In basAuditTrail:
[CODE=vb]Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox, acCheckBox, acComboBox
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "tblAudit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("userna me") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSourc e & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnin gs False
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True
End If
End Select[/CODE]

[Killer42]

The AuditTrail sub expects a form and a control. You appear to be passing it a form (Me) and what is probably a numeric variable (RecordNo).

If this is not the case, please provide more detail as to the context (for example, what is RecordNo?), the actual error message (and number if possible) and exactly where in the code the error is reported.

[cdmonroe]

Here's the weird thing... this code actually works on one form in the database and not on another (I haven't checked more yet). On the broken one I currently get a Runtime error '13' type mismatch and it pops at the line.
Call AuditTrail(Me, RecordNo)


I think you're right about the control versus variable, but why is it treating it differently in one form versus the other? All tables in this database use [RecordNo] as an autonumber primary key.

Is there a way for me to force it to recognize RecordNo as a control?

I tried "Dim RecordNo as Control" and then I get the error:

The expression Before Update you entered as the Event Property setting produced the following error: Member already exists in an object module from which this object module derives.

Thanks for your feedback!

Cheryl

The AuditTrail sub expects a form and a control. You appear to be passing it a form (Me) and what is probably a numeric variable (RecordNo).

If this is not the case, please provide more detail as to the context (for example, what is RecordNo?), the actual error message (and number if possible) and exactly where in the code the error is reported.

[Killer42]

Sorry, you've completely lost me there. I'd suggest you take it up with the experts in the Access forum.

One thing - perhaps the form where it does work already has a control of some sort called RecordNo. Could you interrupt the code at that call and check out what it's referencing?

[cdmonroe]

Sorry, you've completely lost me there. I'd suggest you take it up with the experts in the Access forum.

One thing - perhaps the form where it does work already has a control of some sort called RecordNo. Could you interrupt the code at that call and check out what it's referencing?

You are brilliant! I found the problem. I had copied and pasted repeated fields from the first form to the others.. so the name of the control was wrong! There was a difference in the "Type" on the two watches. THANK YOU THANK YOU THANK YOU! I'm a real novice at all this and haven't had to debug anything yet. I will make use of the watch lists more now.

Thanks again!!!

[Killer42]

... I found the problem ... Thanks again!!!

No problem, glad we could help.

Just remember - the debugging tools are your friend. :)

[suckerpunchnz]

Hey.

I'm using the same code in my Access Db.
Does anybody know how I would get this audittrail module to track the access user login name, rather than the pcs user name logged in?

Thanks heaps for your help,
Phil.

[Killer42]

I'd recommend posting that as a new question in the Access forum. You should get quite a quick response there.