Good afternoon,
I have audit trail tracking set up in my database using the following:
Function LogChanges(lngID As Long, Optional strField As String = "")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String
varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("tblTracking").OpenRecordset
With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(Nz(varNew, 0))
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function
I have VBA set up to Call LogChanges on the "Before Update" event in certain fields on a form. I have a field called "Status" that pulls from a value list. It always defaults to "Call" and when we're done with the billing, we change it to "Work Completed". Since I've set up the tracking, I've had issues with this field. If we change this field FIRST, it works just fine. If we change this field after changing any other field, we get the following error.
Any ideas why this works if we change the status FIRST, but errors if we change it after making changes to any other field?
Thanks,
Heather
I have audit trail tracking set up in my database using the following:
Function LogChanges(lngID As Long, Optional strField As String = "")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String
varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("tblTracking").OpenRecordset
With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(Nz(varNew, 0))
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function
I have VBA set up to Call LogChanges on the "Before Update" event in certain fields on a form. I have a field called "Status" that pulls from a value list. It always defaults to "Call" and when we're done with the billing, we change it to "Work Completed". Since I've set up the tracking, I've had issues with this field. If we change this field FIRST, it works just fine. If we change this field after changing any other field, we get the following error.
Any ideas why this works if we change the status FIRST, but errors if we change it after making changes to any other field?
Thanks,
Heather