D
Debra Farnham
Hey All
I have developed an audit trail using the sample code provided by Microsoft
as follows:
Public Function WriteAudit(frm As Form, lngPermitNumber) As Boolean
On Error GoTo err_WriteAudit
Dim varcboInstalltype As Variant
Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean
bOK = False
DoCmd.SetWarnings False
' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is CheckBox Or TypeOf ctlC Is
ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO tblAudit (lngPermitNumber, FieldChanged,
FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngPermitNumber & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC
WriteAudit = bOK
exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function
err_WriteAudit:
If Err.Number = 2427 Or Err.Number = 3251 Then
Exit Function
End If
MsgBox Err.Number, Err.Description
Resume exit_WriteAudit
End Function
My Question is this:
If the control is a combobox, I do not want to store in the table tblAudit
the actual value of the combobox as it is usually an autonumber. I want to
store the value/text that is displayed to the user instead (usually the
second column).
For example, is there any way of capturing the old value of column 2 of
cboSize and the new value of column 2 of cboSize or will I have to do a
lookup on cboSize once I have stored as variables the actual old and new
values of the combobox?
I am working with a form that has 16 comboxes. Dlookup seems like a "clunky"
solution but I can't think of another one.
Any assistance, as always, is greatly appreciated.
Debra
I have developed an audit trail using the sample code provided by Microsoft
as follows:
Public Function WriteAudit(frm As Form, lngPermitNumber) As Boolean
On Error GoTo err_WriteAudit
Dim varcboInstalltype As Variant
Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean
bOK = False
DoCmd.SetWarnings False
' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is CheckBox Or TypeOf ctlC Is
ComboBox Then
If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO tblAudit (lngPermitNumber, FieldChanged,
FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngPermitNumber & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC
WriteAudit = bOK
exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function
err_WriteAudit:
If Err.Number = 2427 Or Err.Number = 3251 Then
Exit Function
End If
MsgBox Err.Number, Err.Description
Resume exit_WriteAudit
End Function
My Question is this:
If the control is a combobox, I do not want to store in the table tblAudit
the actual value of the combobox as it is usually an autonumber. I want to
store the value/text that is displayed to the user instead (usually the
second column).
For example, is there any way of capturing the old value of column 2 of
cboSize and the new value of column 2 of cboSize or will I have to do a
lookup on cboSize once I have stored as variables the actual old and new
values of the combobox?
I am working with a form that has 16 comboxes. Dlookup seems like a "clunky"
solution but I can't think of another one.
Any assistance, as always, is greatly appreciated.
Debra