By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

try to capture Null in vba when writing to a table

P: 2
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
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
!FieldName = strField
End If
!recordid = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(varNew)
End With

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

thanks for any help

Mar 6 '17 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,430
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

Mar 6 '17 #2

P: 2
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

Mar 7 '17 #3

Expert 100+
P: 1,430
Two options

Expand|Select|Wrap|Line Numbers
  1. !NewValue = CStr(Nz((varNew))
Expand|Select|Wrap|Line Numbers
  1. if Not IsNull(varNew) then
  2.     !NewValue = CStr(varNew)
  3. End If
Mar 7 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.