Hello
I am hoping someone can shed some light on the following for me as I am getting confused... My array is not working so the For Each Loop will not work, I am hoping someone may be able to point me in the right direction of why it is not working?
I am getting the following error: error description: Object Required
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 Control '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 AuditCtrls 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
For Each ctrl In AuditCtrls
Select Case ctrl.ControlType 'Only checks data entry type controls.
Case acTextBox, acComboBox, acListBox, acOptionGroup
sFrom = Nz(ctrl.OldValue, "Null")
sTo = Nz(ctrl.Value, "Null")
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.Name & "', '" & 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
Property Get AuditCtrls() As Variant
Dim ctrl As Control
'exposes an array of controls for which we want to track history of edited data
AuditCtrls = Array(Me.txtCompany, Me.txtFees, Me.txtAddress1, Me.txtAddress2, Me.txtAddress3, Me.txtAddress4, _
Me.txtPostcode, Me.OptCompany, Me.hypWebsite, Me.cboProvider, Me.txtContract, Me.txtFeeRev, Me.SubFrm_Contacts!txtForename, Me.SubFrm_Contacts!txtSurname, _
Me.SubFrm_Contacts!txtPosition, Me.SubFrm_Contacts!txtTel, Me.SubFrm_Contacts!txtMobile, Me.SubFrm_Contacts!txtFax, Me.SubFrm_Contacts!txtEmail)
End Property
Many thanks in anticipation... I will continue to try and fix it in the meantime...
I am hoping someone can shed some light on the following for me as I am getting confused... My array is not working so the For Each Loop will not work, I am hoping someone may be able to point me in the right direction of why it is not working?
I am getting the following error: error description: Object Required
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 Control '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 AuditCtrls 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
For Each ctrl In AuditCtrls
Select Case ctrl.ControlType 'Only checks data entry type controls.
Case acTextBox, acComboBox, acListBox, acOptionGroup
sFrom = Nz(ctrl.OldValue, "Null")
sTo = Nz(ctrl.Value, "Null")
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.Name & "', '" & 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
Property Get AuditCtrls() As Variant
Dim ctrl As Control
'exposes an array of controls for which we want to track history of edited data
AuditCtrls = Array(Me.txtCompany, Me.txtFees, Me.txtAddress1, Me.txtAddress2, Me.txtAddress3, Me.txtAddress4, _
Me.txtPostcode, Me.OptCompany, Me.hypWebsite, Me.cboProvider, Me.txtContract, Me.txtFeeRev, Me.SubFrm_Contacts!txtForename, Me.SubFrm_Contacts!txtSurname, _
Me.SubFrm_Contacts!txtPosition, Me.SubFrm_Contacts!txtTel, Me.SubFrm_Contacts!txtMobile, Me.SubFrm_Contacts!txtFax, Me.SubFrm_Contacts!txtEmail)
End Property
Many thanks in anticipation... I will continue to try and fix it in the meantime...
