Replace "assign the null value" message

[Tiana Arylle]

Hi -

On my Access forms, I have several fields that are marked as NOT NULL in the
underlying DB2 database. The "not null" is a table requirement, not a
workflow requirement.

If the fields are blank when they are loaded in the form, Access fills them
with spaces, which is fine. If, however, someone types in one of those
fields and then changes their mind, blanking the field, the moment they try
to advance to the next field you get the "You tried to assign the Null value
to a variable that is not a Variant data type." message.

I've tried overriding this with my own text replacement, or just a message
box prompt (code below) but regardless of what event I try to trigger it
from, the access message always overrides it. How do I replace the Access
message with my own, and better yet, replace an empty text box value with
spaces?

Thanks for your help!

Private Sub DMGPH_NM_Exit(Cancel As Integer)
Dim demoName As String
demoName = Nz(Me.DMGPH_NM, "Empty")

If demoName = "Empty" Then
Me.DMGPH_NM = Space(25)
End If
End Sub

A similar problem with a date field, for another example:

Private Sub START_DT_BeforeUpdate(Cancel As Integer)
Dim startDate As String

startDate = Nz(Me.START_DT, "Empty")

If IsNull(startDate) Then
MsgBox "You must enter the start date."
Me.START_DT.SetFocus
End If
End Sub

 

[Tiana Arylle]

Thank you so much, that worked perfectly and saved me hours of frustration!