Error: You tried to assign NULL value to a variable that is not a Variant data type

[tylerg11]

Hello,

I've been encountering this issue and have attempted many different things and have not had success. Here's my problem. I do not allow NULL values to be entered into my SQL database (Zero-length strings are used instead). So I have a bound form containing data. If the data is cleared from a bound field and a user attempts to save the form, the following error locks everything up.

Start with this:



After clearing the data:



The following error locks up.



I know why this happens. What I need to figure out is how to replace a NULL submission to the database with a zero-string. I've tried using Lost Focus and Before Update and have not had any success with the methods I've constantly tried.

Help would be appreciated!



Thanks much.

[AlexHedley]

Can you not set a Default Value for the control?

[June7]

DefaultValue won't help once user has edited the field.

What code have you tried?

Try in the control's BeforeUpdate or AfterUpdate event:

If IsNull(Me.controlname) Then Me.controlname = ""

[AlexHedley]

Correct me if I'm wrong but if it's a Default Value then it'll be changed from Null so even if removed later it'll not go back to Null but instead an empty string unless explicitly set?

[June7]

I have the opposite situation. I don't allow empty strings but Null is good. This means if I try to input empty string (typing "" will do that) into bound textbox, Access gets upset and gives me an error message. I can't leave the textbox until I fix it. I can ESC to remove the "" and textbox is Null, ESC again and return the textbox to its original value (the default if new record).

That is with an Access backend, I have never done data entry to an SQL database. Access has nothing against Null and if the SQL table rejects Null, apparently Access cannot 'catch' the error the way it does the empty string in my Access table and results in lockup error.

I don't know if SQL table has property (or any properties at table level) for DefaultValue. DefaultValue is triggered only for a new record, not an existing record, and therefore will not help with this issue for an existing record. Existing or new record, the invalid entry must be corrected before attempt to save record. Hopefully my suggested code will work for either case, if not, perhaps an error handler would, maybe in the form BeforeUpdate event because it has a Cancel parameter.

[ItsMe]

.....What I need to figure out is how to replace a NULL submission to the database with a zero-string. I've tried using Lost Focus and Before Update and have not had any success with the methods I've constantly tried......

Try

In the before update event

If isnull(Me.txtField.Value) then

me.txtfield = ""

end if

I guess you can use the nz function too

Anyway, I did a test with default values in 2010 compared to 2003. There have been instances (in the past) where I would use empty strings as default values for text boxes in A2003. I tried it in A2010 and the value becomes Null when creating a new record. Not sure if the error you are getting is related to your no Null's allowed thing. But, it seems that assigning empty strings in Access 2010 as default is a thing of the past.

I was able to assign "" via VBA or type it into the bound control. The value will return as empty string. Default "" returns Null in 2010. At least in the tests I did. Same test in 2003, different result. 2003 default "" returns empty string. It does not return Null.

Edit: I thought I would add.... Default value is default value. After the default value is changed, ie a new record is created and then the value is changed, the default value becomes inconsequential. Default value is only for new records.

[AlexHedley]

Very interesting find, thanks for sharing.

[deepaksharma]

got any solution....?

[June7]

Instead of hi-jacking and resurrecting a 10-year-old thread, really should start your own. A solution was offered to the original poster but no feedback. Did you try it?