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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|