Audit Trail - Help Required 2

[suntsu]

Hi All,

I am currently working on an audit trail that captures the following details:

Form Name/Field Name/Old Value/New Value/Action (create/edit/delete), etc...

The problem that I am having is when I try to record a user deleting a record, when I pass the form_delete event over to my auditTrail sub I get the following error...

2474: The expression you entered requires the control to be in the active window.

The auditTrail sub attempts to create the record in tblAuditTrail but fails (the autocount ID increments without adding record).

The form containg the data that is being recorded is a subform if it helps, oh and I have tried adding setting the focus to the subform (in case the delete event throws it off, but this didn't help).

This is the code I am using for the auditTrail on this form:
------------------------------------------------------------
Function fAuditTrail()

On Error GoTo handler

Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim idx As Index
Dim vfld As String

'Set pCurrform = Me.Form
'pRecordSource = pCurrform.RecordSource
'These two lines should appear in the 'after update' event of the control in question


' Return references.
Set db = CurrentDb
Set tdf = db.TableDefs(pRecordSource)
' Enumerate through Indexes collection of TableDef object.
For Each idx In tdf.Indexes
' Check Primary property of Recordset object.
If idx.Primary Then
For Each fld In idx.Fields
vfld = fld.Name
Next fld
End If
'Check for another element to primary key
Next idx

'Write to Audit Trail Table
Set rs = db.OpenRecordset("tblAuditTrail&quot
rs.AddNew

rs!txtARID = pCurrform(vfld)
rs!txtFieldName = pCurrform.ActiveControl.Name
rs!txtOldValue = pCurrform.ActiveControl.OldValue
rs!txtNewValue = pCurrform.ActiveControl

If IsNull(pCurrform.ActiveControl.OldValue) Or pCurrform.ActiveControl.OldValue = "" Or pCurrform.ActiveControl.OldValue = pCurrform.ActiveControl.DefaultValue Then
rs!txtUpdateReason = "Create"
ElseIf IsNull(pCurrform.ActiveControl) Or pCurrform.ActiveControl = "" Then
rs!txtUpdateReason = "Delete"
Else
rs!txtUpdateReason = "Modify"
End If

rs!txtDate = Date
rs!txtTime = time()
rs!txtUserID = fCurrentUser()
rs!txtWorkStationID = fGetComputer()

rs.Update
rs.Close
Set db = Nothing

fAuditTrail = 1
sSaveIt
Exit Function
handler:

If Err.Number = 0 Or Err.Number = 2427 Then
Exit Function
Else
MsgBox Err.Number & " : " & Err.Description & Chr(13) & Chr(13) & "If problem persists, please contact the System Administrator.", vbInformation, "System Management"
Exit Function
End If
End Function
------------------------------------------------------------
Sorry for the long post, as you can probably gather i am getting desperate.

Thanks,

SunTsu

 

[MichaelRed]

faq181-291

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[suntsu]

Thanks for the advice Michael.

SunTsu