469,361 Members | 2,363 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,361 developers. It's quick & easy.

Tracking record changes in another table

In the database I'm working on (2003) I have a client table that includes a client status field, and I have a linked notes table that includes the note entry date, note type, and note details. Each client has multiple notes.

Each time the client status is changed in the client table, I would like to have a note automatically created in the notes table that details the status change and the note type.

For example: if the user changes the client status in the client table from "Pending" to "Attending", I would like a note to be created for that client with the note type of "Attending" and note details stating that the status has been changed from "Pending" to "Attending."

These changes are all done by the user through forms. I don't need to know which user made the change. How do I do this? I know some VB, but I'm not advanced.
Mar 25 '08 #1
1 1972
I am trying to remember where i found this but I use the following:

1: create a table with the name of AuditTrail with the following fields:
EditRecordID,EditDate,User,RecordID,SourceTable,So urceField,BeforValue,AfterValue (I set all to text/255)

2: Create a standard module with a name of basAuditTrail and insert the following (this is not my work and no copyright information was given)
[HTML]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.
'If .ControlType = acTextBox Then
Select Case ctl.ControlType
Case acTextBox, acCheckBox, acComboBox, acOptionGroup
If .value <> .OldValue Then
varBefore = .OldValue
varAfter = .value
strControlName = .name
'Build INSERT INTO statement.
& "AuditTrail (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 Select
End With
Set ctl = Nothing
Exit Sub[/HTML]
3:on each form that you want to track changes insert the following to a [HTML]Form_BeforeUpdate() [/HTML] event

[HTML]Call AuditTrail(Me, Form name right here)[/HTML] so it would look something like this

[HTML]Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, Form name right here)
End Sub[/HTML]
Sep 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Dan Greenblatt | last post: by
3 posts views Thread by Bryan Christopher | last post: by
4 posts views Thread by Glenn Owens | last post: by
1 post views Thread by fred tate via .NET 247 | last post: by
4 posts views Thread by Middletree | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.