NULLS and text boxes

[MustangPriMe]

I've been having problems with text boxes bound to a SQL field which doesn't allow nulls, and so far I've not found an answer. Hopefully someone will have a bright idea!

Basically, when using the form I've created, leaving the text box empty is fine as the default value is set to a zero length string (""). Entering text into the box is also fine, or course. The problem comes when deleting the contents of the text box once something has already been typed. Instead of going back to a zero length string, Access seems to treat it as a NULL, and when I then try to move to another field or some other action I get an error message "Non-nullable column cannot be updated to Null".

I've tried testing for Nulls (with the intention of converting to zero-length) using the Before_Update event, but the error message seems to precede the event firing.

Does anyone know of a useful way round this little problem?

Thanks,
Paul Martins

 

[mp9]

Using the BeforeUpdate event to test for null should work.

capture the error number of this error (err.number) - then use that number in the error handling of the BeforeUpdate. If that error number is triggered, set the null value to "" and then Resume rather than exiting out of the event.

http://www22.brinkster.com/accessory/

 

[MustangPriMe]

Thanks for the reply.

The error is appearing even before Before_Update, and my knowledge of using error trapping is that it must be within an event, so doesn't really help.

I've been playing around with it (for longer than I wanted to) and I think I've found a solution:

Under the Change event for the textbox control, use a command similar to:

Me!fldNote = txtNote.Text

...where fldNote is the name of the bound field from the forms recordsource, and txtNote is the name of the textbox control. The change event fires before the "Non-nullable" error appears, and fires regardless of whether the textbox was cleared as a result of a key-press or a mouse click.
Curiously, it doesn't need to be nz(txtNote.Text) as the .Text property seems to hole a zerolength string even if txtNote would have stored a NULL in fldNote.
Wierd.

One final consideration may be a performance hit. I'm hoping that the value of fldNote isn't actually written back to the SQL database until the focus is moved from that control, otherwise it's getting a hit every time a character is added or removed from the field. I'll have to check this out...

Thanks
Paul Martins