Phil-
Here's the complete list of ControlType values:
Rather than test the ControlType, you can use the Tag property to indicate which controls you want to audit. For each form that contains controls you want to audit, set the Tag property of the controls you want audited to something like "Audit". Then change this code from:
If .ControlType = acTextBox Then
to:
If .Tag = "Audit" Then
This is a better way to do it because you can avoid unbound controls that perhaps contain filter values.
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 02:59, Phil Knowles pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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 (4) |
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