Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 29th, 2005, 05:26 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem trapping not null constraint

Hi guys i got a form and sub form. When i leave the field hours empty in the sub form and try to click on close or some where in the form i get this error:

MsgBox "Primary key violation", , "Explanation"

instead of

MsgBox "Violation of Not Null constraint", , "Explanation"

i want to trap not null error and prompt the user that he has to enter value for hours field but aparently it does not. i be happy if some one help me fix this error .Thanks

picture of form in action:

http://i5.photobucket.com/albums/y18...nullerror1.jpg
http://i5.photobucket.com/albums/y18...nullerror2.jpg

code for on error :

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


Select Case DataErr
    Case 3162
        MsgBox "Violation of Not Null constraint", , "Explanation"
    Case 3146
        MsgBox "Primary key violation", , "Explanation"
    Case 3155
        MsgBox "Trigger violation: 'Wages of the employee in this project is not existed. You must enter data of hourly wage first'", , "Explanation"
    Case Else
        MsgBox "Other errors"
    End Select


End Sub
 
Old June 29th, 2005, 06:00 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

Validate the data before running your SQL insert code.

If there is a problem with inserting nulled fields into your SQL table, either default the field to 0 using nz(field, 0), OR set your field to nullable OR pop up a msgbox informing the user of data entry issue with running the sql insert code. It is your decision, though, based on your requirements.

Why is the field not nullable, when you are trying to set it to be nullable?
 
Old June 29th, 2005, 06:17 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by leehambly
 Validate the data before running your SQL insert code.

If there is a problem with inserting nulled fields into your SQL table, either default the field to 0 using nz(field, 0), OR set your field to nullable OR pop up a msgbox informing the user of data entry issue with running the sql insert code. It is your decision, though, based on your requirements.

Why is the field not nullable, when you are trying to set it to be nullable?
Thank u for u reply. Well i am using bounded form and i wonder what u mean by Validate the data before running your SQL insert code . I did not put any insert code in my code behind for the form. The field hours in the sql server 2000 db has option no null . Which mean it does not accep null value. What i am trying to do is prompt the user that they fill that data not to leave it empty. I be happy if u tell me what is wrong with my Form_Error function. i put the code for null error and it should detect it but it does not !!Thanks
 
Old June 29th, 2005, 07:14 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

no need to repost the whole reply. I assumed you were runnning an insert rather than a direct write to a sql table, the principles stand the same though...

"validate" means to check that the data is correct before you accept it as correct. (Personally, I would worry greatly that you do not know what validate means, especially when you are designing a database.)

In this instance, it would mean checking to see if there is a suitable value in your subform's textbox, ie: a number and not null. Since you are using bounded and the field is non-nullable, you NEED to put some validation into your form somewhere prior to the user moving away from the reocrd, else the user will not be able to move away from the record.

Having said that though, mny original suggestion of defaulting the value to 0 still stands. Your users will remember to do it properly if you set everything they dont do properly to zero! You can set the default either on the table in SQL or on the form, again entirely up to you.





Similar Threads
Thread Thread Starter Forum Replies Last Post
error trapping majones@omaha Classic ASP Basics 0 January 5th, 2006 02:02 PM
How to set Not Null constraint to Null Columns arasu Oracle 1 August 22nd, 2005 10:09 AM
Need some error trapping... cpk Classic ASP Databases 4 January 26th, 2005 08:10 AM
Trapping all events rajustha Pro VB.NET 2002/2003 1 December 10th, 2003 12:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.