qvfps
asked on
Runtime Error 2427 on Audit Trail Function
I am using the following function to track changes to a database. It was working fine until I changed the data property AllowAdditions to false on a subform. The subform is tied to a combo box and is linked to the main form based on the check box. If I select a value in the check box before an event triggers the audit trail it works fine. If I have not accessed the subform then I generate a Run-time Error 2427: You entered an expression that has no value. The error is on the line where I check to see if there is a previous value: If IsNull(frmCtrl2.oldValue) Or frmCtrl2.oldValue = "" Then
I changed the AllowAdditions property to false because if the property is set to true and you move the wheel on the mouse it takes you to a blank record on the form. This causes problems with the database and it has confused several users.
The On Error function does not trap this error. Is there a way to stop the mouse from moving you to a new record, trap the error so I can skip to the next field or check to see if the property is set?
I am using Microsoft Access 2000 on WIndows 2000.
Thanks for any suggestions
Function AuditTrail(WID As Long, OID As Long, myID As Long)
On Error GoTo Err_AuditTrail
Dim MyForm As Form
Dim frmCtrl, frmCtrl2 As Control
Dim myName As String
Set MyForm = Screen.ActiveForm
myName = MyForm.Name
'Check each data entry control for change and record
'old value of Control.
For Each frmCtrl In MyForm.Controls
'Only check data entry type controls.
Select Case frmCtrl.ControlType
Case 112
For Each frmCtrl2 In frmCtrl.Controls
If frmCtrl2.Name <> "Updates" Then
Select Case frmCtrl2.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
' If control was previously Null, record "previous value was blank."
'MsgBox ("frmCtrl2.Name = " & frmCtrl2.Name)
'THE FOLLOWING LINE GENERATES THE ERROR IF THE ALLOWADDITIONS PROPERTY OF THE FORM IS FALSE
If IsNull(frmCtrl2.oldValue) Or frmCtrl2.oldValue = "" Then
Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
' If control had previous value, record previous value.
ElseIf IIf(IsNull(frmCtrl2.Value) , "", frmCtrl2.Value) <> frmCtrl2.oldValue Then
Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
End If
End Select
End If
Next frmCtrl2
Case acTextBox, acComboBox, acListBox, acCheckBox
If frmCtrl.Name <> "Updates" And frmCtrl.Enabled Then
' If control was previously Null, record "previous value was blank."
If IsNull(frmCtrl.oldValue) Or frmCtrl.oldValue = "" Then
Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
' If control had previous value, record previous value.
ElseIf IIf(IsNull(frmCtrl.Value), "", frmCtrl.Value) <> frmCtrl.oldValue Then
Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
End If
End If
End Select
Next frmCtrl
Exit_AuditTrail:
Exit Function
Err_AuditTrail:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume Exit_AuditTrail
End Function
I changed the AllowAdditions property to false because if the property is set to true and you move the wheel on the mouse it takes you to a blank record on the form. This causes problems with the database and it has confused several users.
The On Error function does not trap this error. Is there a way to stop the mouse from moving you to a new record, trap the error so I can skip to the next field or check to see if the property is set?
I am using Microsoft Access 2000 on WIndows 2000.
Thanks for any suggestions
Function AuditTrail(WID As Long, OID As Long, myID As Long)
On Error GoTo Err_AuditTrail
Dim MyForm As Form
Dim frmCtrl, frmCtrl2 As Control
Dim myName As String
Set MyForm = Screen.ActiveForm
myName = MyForm.Name
'Check each data entry control for change and record
'old value of Control.
For Each frmCtrl In MyForm.Controls
'Only check data entry type controls.
Select Case frmCtrl.ControlType
Case 112
For Each frmCtrl2 In frmCtrl.Controls
If frmCtrl2.Name <> "Updates" Then
Select Case frmCtrl2.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
' If control was previously Null, record "previous value was blank."
'MsgBox ("frmCtrl2.Name = " & frmCtrl2.Name)
'THE FOLLOWING LINE GENERATES THE ERROR IF THE ALLOWADDITIONS PROPERTY OF THE FORM IS FALSE
If IsNull(frmCtrl2.oldValue) Or frmCtrl2.oldValue = "" Then
Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
' If control had previous value, record previous value.
ElseIf IIf(IsNull(frmCtrl2.Value)
Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
End If
End Select
End If
Next frmCtrl2
Case acTextBox, acComboBox, acListBox, acCheckBox
If frmCtrl.Name <> "Updates" And frmCtrl.Enabled Then
' If control was previously Null, record "previous value was blank."
If IsNull(frmCtrl.oldValue) Or frmCtrl.oldValue = "" Then
Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
' If control had previous value, record previous value.
ElseIf IIf(IsNull(frmCtrl.Value),
Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
End If
End If
End Select
Next frmCtrl
Exit_AuditTrail:
Exit Function
Err_AuditTrail:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
End If
Resume Exit_AuditTrail
End Function
Check out Lebans website. He has a good Mouse Wheel solution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.