Hello
I have the following function which checks certain fields on a form for any changes. It then stores these changes and puts them on a form for the user to be able to keep track of old / new data.
The problem I have is that I didnt allow for 'new data'... I only want the SQL statement to run execute if the old data has a value, in other words, I dont want it to record changes where the old data was NULL....
Please can someone advise the best way for me to add this clause either into the loop in the VBA OR could I build a query on the table that will eliminate NULL values?? Which would be best...
Code as follows...
Public Function AuditTrail(frm As Form)
On Error GoTo Error_Handler
Dim sAuditTable As String
Dim lsSQL As String
Dim sTable As String 'Table where the record is being edited
Dim ctrl As Variant 'The control in the form being edited
Dim sFrom As String 'Original Data in the control
Dim sTo As String 'What the original data was changed to
Dim AuditControls As Variant
' Skips this procedure if a new record is being entered in the form
If frm.NewRecord = True Then
Exit Function
End If
' Runs through each control on the form and checks for edits/changes
AuditControls = Array(Me.txtForename, Me.txtSurname, Me.txtPosition, Me.txtTel, Me.txtMobile, Me.txtFax, Me.txtEmail)
For Each ctrl In AuditControls
Select Case ctrl.ControlType 'Only checks data entry type controls.
Case acTextBox, acComboBox, acListBox, acOptionGroup
sFrom = Nz(ctrl.OldValue, "")
sTo = Nz(ctrl.Value, "")
If sFrom <> sTo Then
' Gets the required Info
sTable = frm.RecordSource
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Amendments (CompanyRef, OnForm, FieldName, OldValue, NewValue, Who, DateChanged ) "
lsSQL = lsSQL & "VALUES ('" & ICompanyRef & "', '" & frm.Name & "', " & _
"'" & ctrl.ControlSource & "', '" & sFrom & "', '" & sTo & "', '" & SUser & "', '" & Now() & "')"
CurrentDb.Execute (lsSQL)
End If
End Select
Next ctrl
Error_Handler_Exit:
Exit Function
Error_Handler:
MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
Err.Clear
Resume Error_Handler_Exit
End Function
Many Thanks in anticipation....
I have the following function which checks certain fields on a form for any changes. It then stores these changes and puts them on a form for the user to be able to keep track of old / new data.
The problem I have is that I didnt allow for 'new data'... I only want the SQL statement to run execute if the old data has a value, in other words, I dont want it to record changes where the old data was NULL....
Please can someone advise the best way for me to add this clause either into the loop in the VBA OR could I build a query on the table that will eliminate NULL values?? Which would be best...
Code as follows...
Public Function AuditTrail(frm As Form)
On Error GoTo Error_Handler
Dim sAuditTable As String
Dim lsSQL As String
Dim sTable As String 'Table where the record is being edited
Dim ctrl As Variant 'The control in the form being edited
Dim sFrom As String 'Original Data in the control
Dim sTo As String 'What the original data was changed to
Dim AuditControls As Variant
' Skips this procedure if a new record is being entered in the form
If frm.NewRecord = True Then
Exit Function
End If
' Runs through each control on the form and checks for edits/changes
AuditControls = Array(Me.txtForename, Me.txtSurname, Me.txtPosition, Me.txtTel, Me.txtMobile, Me.txtFax, Me.txtEmail)
For Each ctrl In AuditControls
Select Case ctrl.ControlType 'Only checks data entry type controls.
Case acTextBox, acComboBox, acListBox, acOptionGroup
sFrom = Nz(ctrl.OldValue, "")
sTo = Nz(ctrl.Value, "")
If sFrom <> sTo Then
' Gets the required Info
sTable = frm.RecordSource
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Amendments (CompanyRef, OnForm, FieldName, OldValue, NewValue, Who, DateChanged ) "
lsSQL = lsSQL & "VALUES ('" & ICompanyRef & "', '" & frm.Name & "', " & _
"'" & ctrl.ControlSource & "', '" & sFrom & "', '" & sTo & "', '" & SUser & "', '" & Now() & "')"
CurrentDb.Execute (lsSQL)
End If
End Select
Next ctrl
Error_Handler_Exit:
Exit Function
Error_Handler:
MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
Err.Clear
Resume Error_Handler_Exit
End Function
Many Thanks in anticipation....
