populate data into a table when a form closes (1 Viewer)

[accesslearner]

i have a form has a text feild control which records all changes made to the form values. The form gets values based on the employee table. so if anyone changes the records it creates an audit trail of values.

A code i got from this forum.

I want to be able to copy these values into an audit table to keep track of it.
is there a way to do this.

The audit table has
id- autonumber
audittrail - memo
Date entered - date

basically when i close my employee form all data that has been edited is captured into a text feild called audit trail and when i close this form this data should be updated into the audit table.

thanks in advance for your help.

 

[CEH]

If you got the code here, it should look something like this......

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.
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

And it should already be doing what you need. If not, go back to where you got the code and see what is missing......

 

[accesslearner]

tblAudit
Feild Name Data Type
Recordid Autonumber
AuditTrailData Memo
DateRecorded Date
username Text

These are the 4 feilds in my tblAudit.

Thanks