A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Replace "assign the null value" message



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 04:42 PM posted to microsoft.public.access.forms
Tiana Arylle
external usenet poster
 
Posts: 6
Default Replace "assign the null value" message

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


  #2  
Old June 1st, 2010, 05:17 PM posted to microsoft.public.access.forms
Linq Adams via AccessMonster.com
external usenet poster
 
Posts: 1,474
Default Replace "assign the null value" message

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..

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

  #3  
Old June 1st, 2010, 06:49 PM posted to microsoft.public.access.forms
Tiana Arylle
external usenet poster
 
Posts: 6
Default Replace "assign the null value" message

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

"Linq Adams via AccessMonster.com" wrote:

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..

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.