Allowing an Empty String "" in a textbox.

[BKSwindell]

Hello, I have an Access 2010 database where we have a SQL Linked Table with a column that is nVARCHAR(20) Not Null data type. We have created a form for data entry. Currently when the user tries to erase a value or choose not to define a value we get the following error.

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

This field should accept a blank value "" as the user may not want to set the value. We do not have control over the DB schema, so how can I work around this issue in access?

Thanks.

Brad Swindell

 

[JHB]

If you can't change the table setup to allow a Null value, can't you set the default value for the field control in the form to a space, (dummy value)?

 

[BKSwindell]

If you can't change the table setup to allow a Null value, can't you set the default value for the field control in the form to a space, (dummy value)?

I have tried using a space and "" as the default value. Neither works, I have also tried to enter a space In the field and I am still generating the same error message.

 

[Galaxiom]

Access strips trailing spaces from text.

Try entering the Blank character as the default.
Hold down ALT and key 0160 on the numeric keypad.

 

[BKSwindell]

Modifying the Default Value was not working, so after messing around with several ideas I used the OnChange Event for the text box.

Private Sub Background_Color_Change()
    If IsNull(Background_Color) Or Background_Color.Text = "" Then
        Background_Color.Value = ""
    End If
End Sub

Thanks for the help.