Good point, Duane. I assume that the columns for varBefore and varAfter are Text data type to be able to hold any data type. There should also be a test for IsNull(varBefore) and IsNull(varAfter) to insert the special Null value instead of the value of the control. Something like this:
.
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 & IIf(IsNull(varBefore), "Null", "'" & varBefore & "'") & cDQ & ", " _
& cDQ & IIf(IsNull(varAfter), "Null", "'" & varAfter & "'") & cDQ & ")"
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Aug 10, 2017, at 08:01, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Phil,
Does the existing code even work? The string/text values in the INSERT statement don't have quotes around them. What are the data types of the fields in the Audit table. I expect BeforeValue and AfterValue must be memo or text fields to accommodate text, numbers, and dates.
You can try something like the following but I would comment out the DoCmd.RunSQL strSQL line and review the debug window results before implementing:
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 .ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionGroup
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 Select
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub
Duane Hookom
Minnesota
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Phil Knowles pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, August 10, 2017 2:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Audit Trail
Sent: Thursday, August 10, 2017 2:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Audit Trail
Can anyone help me on this one?
cheers
Phil
On Wednesday, 9 August 2017, 14:31, "pdk444444@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I have been trying to find some sample code on this topic as I am sure there is some somewhere so as not to bother you with my specific question - but unfortunately have not been able to find it.
I have found some code for an audit trail
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 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 It works but only handles controls of the type acTextBox
When I tried to extend this to include other types of controls I have run into problems (due to my lack of knowledge in this area)
I want to include all the controls on my forms in the audit trail and I think of them as text or numeric or date etc
but there doesn't seem to be the equivalents of acTextBox for these types of controls.
If there is a better sample of an audit trail in your system please let me know how to get at it and I will see if that is any clearer, otherwise any help in improving the above code would be very much appreciated.
cheers
Phil
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar