审计跟踪的一种形式 [英] Audit Track a form
问题描述
我设立的形式在我的数据库中的审计跟踪系统。我下面从苏珊·哈金斯的例子<一href="http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/6166807"相对=nofollow>这里
I am setting up a Audit Tracking system for the forms in my database. I am following the example from Susan Harkins Here
我的code ++工程,为我的客户的形式是基于关闭客户表。这是我的code:
My code works for my form customers which is based off the customers table. Here is my code:
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
MsgBox "Step 1"
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
然而,当我尝试在表单中更改我的子窗体的数据我得到不支持这种类型的对象操作的错误。我可以看到错误是发生在这里:
However, when I try to change data in my subform within the form I get an error "Operation is not supported for this type of object". I can see the error is occuring here:
If .Value <> .OldValue Then
我的子窗体基于关闭它是以脱三个表的查询
My subform is based off of a query which is based off of three tables
我试图改变在客户产品客户的价格,并保持记录这些变化。是否有什么我失踪或周围的工作。
I'm trying to change a customer price under Customer Products and keep a log of those changes. Is there something I'm missing or a work around.
感谢您的帮助!
推荐答案
暂时禁用错误处理程序是这样的:
Temporarily disable your error handler like this:
'On Error GoTo ErrHandler
当你得到一个关于不支持的操作的错误通知,从错误对话框中选择调试。这将让你了解当前的文本框控件被触发错误的详细信息。尝试在即时窗口下面的语句:
When you get the error notice about "operation not supported", choose Debug from the error dialog. That will allow you to find out more information about the current text box control which is triggering the error. Try the following statements in the Immediate window:
? ctl.Name
? ctl.ControlSource
? ctl.Enabled
? ctl.Locked
? ctl.Value
至少 ctl.Name
将确定哪个文本框触发的错误。
At least ctl.Name
will identify which text box is triggering the error.
检查DB后,我会建议一个函数( IsOldValueAvailable
)指示是否 .OldValue
可用对于电流控制。有了这功能,跟踪线索
此更改后的程序如下:
After examining the db, I'll suggest a function (IsOldValueAvailable
) to indicate whether .OldValue
is available for the current control. With that function, the AuditTrail
procedure works after this change:
'If .ControlType = acTextBox Then
If IsOldValueAvailable(ctl) = True Then
和功能。它可能还需要更多的工作,但我没有发现在我的测试任何问题。
And the function. It may still need more work, but I didn't spot any problems in my testing.
Public Function IsOldValueAvailable(ByRef ctl As Control) As Boolean
Dim blnReturn As Boolean
Dim strPrompt As String
Dim varOldValue As Variant
On Error GoTo ErrorHandler
Select Case ctl.ControlType
Case acTextBox
varOldValue = ctl.OldValue
blnReturn = True
Case Else
' ignore other control types; return False
blnReturn = False
End Select
ExitHere:
On Error GoTo 0
IsOldValueAvailable = blnReturn
Exit Function
ErrorHandler:
Select Case Err.Number
Case 3251 ' Operation is not supported for this type of object.
' pass
Case Else
strPrompt = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure IsOldValueAvailable"
MsgBox strPrompt, vbCritical, "IsOldValueAvailable Function Error"
End Select
blnReturn = False
Resume ExitHere
End Function
这篇关于审计跟踪的一种形式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!