How-to: Error: "You tried to assign the null value to a variable that is not a variant data type"

This error is often produced when working with 'Required' database fields, if the required item is missing/NULL it will raise an engine-level error.

Here is an example of trapping this error, the Form_error routine should be assigned to the On Error event of the Form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim strControl As String
strControl = Screen.ActiveControl.Name
If DataErr = 515 Then
    If MsgBox("This field cannot be empty" & vbCrLf & "Press OK to continue (undo) or Cancel to exit completely", vbOKCancel, "Error") = 2 Then
    End If
Response = acDataErrContinue
End If

End Sub

This error can also be generated by VBA code which assigns values of the wrong data type.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight” ~ Bill Gates


NULL values - Dealing with NULL values.
Error trapping ODBC errors
Nz - Detect a NULL value or Zero Length string.

Copyright © 1999-2023
Some rights reserved