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

 

[Linq Adams via AccessMonster.com]

You can use the Form_Error event. Here's an example for a
single error, in this case the one you cited, I believe.

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

Select Case DataErr

Case 3314
MsgBox "your error message goes here"
Resonse = acDataErrContinue

Case Else
MsgBox Err.Number & " - " & Err.Description

End Select

Resonse = acDataErrContinue

End Sub

This code can be expanded to include other errors, and can even be modified
to pop up different messages for the same error, reflecting the actual
control that caused the error..

 

[Tiana Arylle]

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