Problems With Conjtrols On Form

[rnutts]

Hi

I am trying to create an audit trail of changes to control values on forms within a database.
I have found a method on the internet, which has a module to insert the relevant information into a table called Audit.
The problem I have found with the code is that if the control is 'blank' or 'Null' then the record change was ignored.
I have modified the code slightly to check if the .OldValue was null to use " " as a filler
However when I try this for the .Value portion of the code, I still get no record.
Basically if the user clear the field with the backspace key or the delete key the code believes there is no change in the field values.
Can someone give me a pointer as to how to sort this.
Code is below

Sub AuditTrail(frm As Form, recordid As Control)
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox
If .Value <> .OldValue Then
If Not IsNull(.OldValue) Then
varBefore = .OldValue
Else
varBefore = " "
End If
If Nz(IsNull(.Value), 0) = 0 Then
varAfter = " "
Else
varAfter = .Value
End If


Many thanks

Richard

 

[pr2-eugin]

Hello Richard, I think the problem is here

Nz(IsNull(.Value), 0)

Nz() is used to replace any Null values.. IsNull will return a True or False Boolean value.. Try this instead..

If Nz(.Value, 0) = 0 Then

 

[rnutts]

Thanks for the answer

The problem is occuring further up the code. The code is not recognising that there is any change in the value (old v current)when the user removes the value from the field(backspace or delete)
I have tried putting various message boxes in the code to see what is happening and the code is not seeing the change and is therefore skipping to the end of the code
Any further thoughts greatfully received

Richard

 

[pr2-eugin]

From which event is this Procedure called?

 

[rnutts]

The module is called from the before update event of the control.

Call AuditTrail(Me,EnquiryNumber)