Invalid use of Null

[BennyLinton]

I have an Access database that is logging changes to a specific field on a form. The code is working great unless the user clears the field... then I get 'Invalid Use of Null' even though the field in my SQL Server backend can accept nulls. Any ideas?:

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

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("ztblDataChanges").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
End Function

 

[NauticalGent]

Try wrapping the variable in the Null function: Nz()

 

[BennyLinton]

I tried this: !NewValue = !NewValue = Nz(CStr(varNew), "") but get the same error

 

[NauticalGent]

Replace the "" with 0 (zero)

 

[BennyLinton]

still same error with !NewValue = Nz(CStr(varNew), 0)

 

[NauticalGent]

Sorry, I'm out of ideas. Good thing this site is crawling with folks a lot smarter than me who have been patiently waiting for me to pass this on!

Stand by, the Calvery is coming...

 

[BennyLinton]

Molti grazii per l'aiuto comunque!, Ciao

 

[NauticalGent]

Prego! It is no problem at all. Is it possible to post a sanitized version of your DB? I would like to fiddle around because now I am curious...

 

[pbaldy]

still same error with !NewValue = Nz(CStr(varNew), 0)

CStr() will barf on the Null, you need to have the Nz() at the inner level.

 

[NauticalGent]

still same error with !NewValue = Nz(CStr(varNew), 0)

So CStr(Nz(varNew,0)) is the ticket then?

 

[pbaldy]

Yes, though I don't know if either is necessary. This might work:

!NewValue = varOld

 

[NauticalGent]

Barfing On the Null...

Funny, I can't find that term in ANY of my Access files.

 

[pbaldy]

You probably have a newer version. They took it out because it was too technical for some people.

 

[BennyLinton]

Thanks all... CStr(Nz(varNew,0)) works great now!

 

[NauticalGent]

Glad it worked out!