Audit Trail Question

[DKM]

Hi

I am currently trying to add an audit trail into a database (using the code from these forums). Its working fine when used on a specific table, however i need to adapt the code slightly to record the changes made on a different form in the same place.

I have 2 forms

Main form (FrmFactorScores) contains scores for certain areas. such as knowledge. any changes to this form as recorded in one of the controls on the form (tbFactorauditDSview).

the second form contains an addition notes field such as Knowledge notes (the record is refreshed based on the active record from the main form to ensure the records link up) . What i am trying to do is to get any changes to the knowlodge notes field also record in the same place as changes to the main form (tbfactorauditDSview).

At the moment i have the audit trail working fine on the main form, however when it tries to record the changes to Frminfo i get an error message (error 2465) however i have checked all the modules and cannot see where the error is coming from, as i beleive all controls are correctly listed.

Have i missed something, or is what im attempting to do even possible? I have attached an example database to show how it all fits together as my descritptions arnt always the best

Any help or advice is appreciated.

 

[jepoysaipan]

Just a thought, did you try cathing the afterupdate event on the second form and update the AuditTrail by referencing:

Forms!FrmFactorScores.tbAuditTrailDSView = Forms!FrmFactorScores.tbAuditTrailDSView & "**-**" & Me.Remarks

It's just a quick fix

Jeff

 

[DKM]

this is the advantage of another pair of eyes on some of these problems, it works well in adding what the field has been changed to into the audit field, however i would need it to record it both the before and after data held is the field.

could this be done via copying the info as part of a before update event procedure and only pasting it into the audit trail field if it doenst match the new information (either in the event procedure for each field, or for the form itself). The new record and delete logging isnt required on this one due to the relationship between the data.

 

[DKM]

reading a couple of other threads im beginning to understand where this is going wrong. the code im using is this:

Option Compare Database
Option Explicit

Public Function Audit_TrailJobInfoDSView()
On Error GoTo Err_Audit_TrailJobInfoDSView

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
sUser = CurrentUser

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = Forms!FrmFactorScores.tbAuditTrailFactorScores & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = Forms!FrmFactorScores.tbAuditTrailFactorScores & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

'Check each data entry control for change and record old value of the control.
For Each ctl In MyForm.Controls

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "tbAuditTrailFactorScores" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = Forms!FrmFactorScores!tbAuditTrailFactorScores & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = Forms!FrmFactorScores!tbAuditTrailFactorScores & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = Forms!FrmFactorScores!tbAuditTrailFactorScores & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
End If
End Select

TryNextControl:
Next ctl

Exit_Audit_TrailJobInfoDSView:
Exit Function

Err_Audit_TrailJobInfoDSView:
If Err.Number = 64535 Then 'Operation is not supported for this type of object.
Exit Function
ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
Beep
MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_TrailJobInfoDSView

End Function


Now i know that the myform!Auditrail is trying to reference a control that does not exist on the second form (frmjobInfo). Is anyone able to provide advice as to how i would get this to point to the audit trail field (tbAuditTrailFactorScores) held on form 1 (frmFactorScores).

If its not possible to tell the form to save the audit trail on another open form is there a way to save the information in an audit field specifically for FrmJobInfo, and then run an append code to move it into where i want it to be saved? or will this cause more problems than its worth?