You tried to assign the Null value to a variable that is not a Variant data type (1 Viewer)

[cliftonx]

I'm trying to update a Description field on a input form by replacing it with a null/spaces. But when I move on onto the next field I get an error message; "You tried to assign the Null value to a variable that is not a Variant data type." I'm using a SQL Server backend. When I look at the table the field resides in and view the data type for Description field its defined as "not null". I changed the Description field within the table and made it "null" instead of "not null". But the same error keeps popping up. What am I doing wrong?

 

[June7]

Maybe need to disconnect the table and relink.

 

[Minty]

If relinking doesn't work then use "" instead of null to "empty" the field.

You could use a query to update the field value, but you can't set a VBA string variable to Null, which is where you normally see this error.

 

[cliftonx]

I've relinked the table and used a query to update the field value. But still getting the same error message.

 

[Pat Hartman]

When you view the properties of the linked table, what does Access show for the AllowZeroLength property? What does it show for the Required property?

 

[cliftonx]

After further review of the DSN names used at our shop. We are currently maintaining 3 environments (Production, Development and Test). When I Altered the table in our Development environment in SQL Server. Everything worked fine. However, I used the TEST environment for the DSN name. After relinking the table, It appeared to have worked fine on the relinking process, but upon my review, I noticed that I inadvertently used the TEST environment instead of the Development. After noticing this error, I reran the relink process with the correct Development DSN name. Everything worked as it should with no problems. The frontend form now allows me to go to the field that was altered, zap out the current data without displaying an error message. Oops, my bad. Thanks, for all your expert advice.