Automatically replace NULL with zero string

[tylerg11]

I don't allow NULL values in my database. So if a textbox is cleared of its datea on a bound form record how do I automatically "switch" this null to a zero string so that is what gets submitted to the database? Otherwise I get a null error. Seems like I need to catch this in the BeforeUpdate event?

[Bob Fitz]

Try:
If IsNull(Me.TextboxName) then
Me.TextboxName = ""
EndIf

[tylerg11]

I tried that, and that didn't work either.

[Bob Fitz]

"didn't work" does not really help us.
What actually happens.

[ItsMe]

Since you are trying to validate the field after the user makes a change maybe you can try the AfterUpdate event.

[tylerg11]

It says it can't update the table with a NULL value. So it obviously isn't catching the Null and changing to a zero string.

If I check using IsNull(TextboxName) it is checking the bound value which obviously can't be NULL.

[June7]

I have the opposite view. I don't allow empty strings in tables.

Did you try the form BeforeUpdate? Textbox BeforeUpdate?

[tylerg11]

One of the messages I get is:

"You tried to assign the Null value to a variable that is not a Variant data type."

[tylerg11]

I have the opposite view. I don't allow empty strings in tables.

Did you try the form BeforeUpdate? Textbox BeforeUpdate?

I just tried the textbox BeforeUpdate. If I used the form BeforeUpdate I would have to identify each control, right?

[June7]

Yes. But I don't know that will even work. Never been an issue for me because I like null, don't want empty string.

[ItsMe]

What about adding a validation rule in the field's property at the table level?

Maybe
>0 Or Is Null

[June7]

Except OP does not want to allow null, so:

>0 And Not Is Null

And maybe set DefaultValue property to: ""

But how would user input an empty string if they wanted to eliminate other input?

All this is probably one reason I prefer Null in fields over empty string. Then handle nulls in queries and code.


Clarify: How are you presently setting the field to not allow Null? Is the Required property set to Yes?


Null indicates a value is not known. If you want the field set as Required then should not be an empty string either, should be known data input.

Data control has Value and OldValue properties. The Value is the last entered value. I did a quick test with a textbox set as Required and used its BeforeUpdate event to capture the Null and change to "". It reads Null as the Value but errors and won't allow me to change the textbox or field value to anything. However, AfterUpdate seems to work after all:
Private Sub TEST_AfterUpdate()
If IsNull(Me.TEST) Then Me!TEST = ""
End Sub


Do you also not allow null in other field types?

[ItsMe]

June,

You nailed it. It won't take a value or empty string in the BeforeUpdate event. I tried the same thing, only not until after your post.

I was able to change the control's control source to a "Temp" field within the same table/query in the Enter and Exit events. The Exit event I would change the source back to the original field. It surprisingly would transfer values between the two fields but, this won't transfer a null value. Might work by changing the ControlSource to Unbound too. Moot point now.

[ItsMe]

Hmmm...... If IsNull(Me.TEST) Then Me!TEST = "" didn't work for me in the afterupdate. Validation rule is at the table.

The only way I see it maybe working is to change the control properties (entering and exiting) and store the values or non-values public within the form. I guess my original >0 or Is Null validation is THE BEST fix afterall :Laughing