try to capture Null in vba when writing to a table

[Kevcar]

Hi i found the following code on the web to allow me to track changes. It works fine if there is
1 initially no data in the field
2 the field value changes to something else(text)

The Problem i have is when a user deletes the text in the box it crashes the code.
How can i capture the delete command and stop the code from crashing?
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
Application.Echo False

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
' strFormName = Screen.ActiveForm.Name
strFormName = Screen.ActiveControl.Parent.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("Audittrail").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(varNew)
.Update
End With

'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Application.Echo True
End Function

thanks for any help

Kevin

[PhilOfWalton]

Where does it crash?

You may need to check that LngID has a value and that VarNew has a value, and for that matter, all other variables that you are trying to add to the table.

The use of the Nz() function may help

Phil

[Kevcar]

thanks for reply Phil.
The code crashes on !NewValue = CStr(varNew) line.
I was thinking of putting a value in the cell that had been deleted, that way I would still have a record of the change.
if it better to put NZ into code
would I wrap the NZ function around the newvalue line?

thanks again

Kevin

[PhilOfWalton]

Two options

1)

!NewValue = CStr(Nz((varNew))
 

2)

if Not IsNull(varNew) then
    !NewValue = CStr(varNew)
End If
 

Phil