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
        Me.Undo
    Else
        Me(strControl).Undo
    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.

“Don’t die old, die empty. That’s the goal of life. Go to the cemetery and disappoint the graveyard” ~ Myles Munroe

Related:

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