Default Value not getting applied when saving to SQL Server

[JoanneJames]

I have recently moved a DB over to SQL Server - still using Access as front-end (linked tables). 2 fields having trouble with in SQL Server are both defined to not accept null values and have default values defined as such: ie ('No Donor'). If I remove a value from one of these fields via my form, instead of putting the default value into the field upon saving the record, I get an error message: " You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)". This error msg occurs BEFORE going to any event, although it appears to originate from within Access itself. I tried the following:
1. changing from double quotes to single quotes in the forms default value
2. Made sure form will assign default values via fetch default values property
3 Verifying that default values were assigned in SQL Server
4. Changing the default value in the link (there is NO default value in design mode on the Access side for the link and it won't allow me to add it. This is where I am guessing the problem lies - I am presuming that when I establish a link, it should pass this info to Access, so it appears this is not happening.
5. Re-linked (although did not re-create the DSN)

I really need some help with this!!! Have been trying to figure out during last few days!!

 

[GolferGuy]

If I remove a value from one of these fields via my form

The action of removing the value from a field on the form is exactly that, removing the value. Access allows you (or the user) to do what ever you have allowed them to do. If you do not want this field to be sent to SQL Server as a null, then you need to check the status of the field in the BeforeUpdate event for the field. If it is null, set Cancel to True and do not allow the use out of the field. Or, if it is null you can put the default value into the field yourself (do that in the AfterUpdate event). But the default value is only acted on my Access when the blank form is displayed for input by the user. If the user changes a default, Access does not override the users change.

BTW, I tested this action with a local table within Access, not a SQL table. But I have no doubt that they act exactly the same in this situation.

 

[JoanneJames]

The action of removing the value from a field on the form is exactly that, removing the value. Access allows you (or the user) to do what ever you have allowed them to do. If you do not want this field to be sent to SQL Server as a null, then you need to check the status of the field in the BeforeUpdate event for the field. If it is null, set Cancel to True and do not allow the use out of the field. Or, if it is null you can put the default value into the field yourself (do that in the AfterUpdate event). But the default value is only acted on my Access when the blank form is displayed for input by the user. If the user changes a default, Access does not override the users change.

BTW, I tested this action with a local table within Access, not a SQL table. But I have no doubt that they act exactly the same in this situation.

Unfortunately, the error msg I get displays BEFORE the "BeforeUpdate' event, and there is no other event it could go to that I am aware of after updating the field - sorry, I forgot to mention this. Also, this HAD been working when I had an Access backend (with defaults both in form and in backend). I could remove data from the field & move to another field with no message. Then, upon saving, the default data from the Access backend would be applied to the field via the form_beforeupdate event.

 

[GolferGuy]

Could you put this form into an empty database and then post the database so we can look at it.
Have you tried to make a new form to see if it has this same behavior? Actually, just a couple of fields, including this one that is giving you the hard time.

 

[GolferGuy]

Joanne, I just reread you original post. Sorry I missed this before. But, if my just previous suggestion doesn't work, this would be a work-around. Make the field on the form an unbound field, then in the after update event you can move the data from this unbound field into the bound field. If this visible field is blanked by the user, don't move anything and the default value will stay in the bound, hidden field. Of course you will need to move the default value into the unbound field in the OnCurrent event for the form. When I saw that the error message was about a null value trying to go into a non-variant field, I'm thinking that SQL Server is enforcing the non-variant/no-nulls rule much tighter than Access does. And that sounds like SQL to me. I'm not sure, but I think it sounds logical.

 

[JoanneJames]

Thank you for the time you are putting into this - I very much appreciate it. What is this 'rule' you are referring to and how could SQL Server enforce it before any update has been applied? - I'm not even getting to BeforeUpdate for the FIELD, let alone the FORM. Also, it appears that the error mesage is coming from Access. Maybe if I could define the text portion of the field as a variant it would stop the message. How would I capture this message so I could at least provide a better message?

Since it is obviously commonplace to have fields that require values , this seems to be a bug or at very minimum, highly undesireable action in Access. While I appreciate your effort at helping me, I question having to do this kind of workaround whenever I want to have a field defined this way in SQL Server - nor would I always want to make ALL fields = nonrequired in SQL Server in order to get this to work. I did try creating a new front-end & a quick form & got same error.

 

[GolferGuy]

Many people, or so I have been told, only use unbound forms when dealing with SQL Server data. I personally do not do this unbound stuff, but I have had to work on a database at a client's location that was written that way, all unbound fields. It does create a TON of extra coding and work, plus the original author (in my humble opinion) did not use any labor saving code, but hard coded EVERYTHING instead of creating any reusable code.

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

As far as this "rule" goes, a non-variant field can not receive a null, and to do so gives us an "invalid use of null" type of error. That is basically what you are getting in your form. That is why I'm thinking that Access is much tighter on this "rule" when dealing with SQL Server, rather than dealing with an Access table.
I have not encountered this situation yet with my application that I'm converting from Access XP to SQL Server, but I have encountered other differences between Access and SQL Server and I don't remember winning any of those battles. I had to do some sort of a work-around, so I have pretty much given up on trying to make SQL Server act like Access.
Because I believe the message you are getting is because of the SQL Server/Access tie, I really don't think you can capture the message in VBA. I really think you can only change something within your table, or a work around within your own form.

 

[JoanneJames]

I discovered the fix: instead of capturing or handling the error in the beforeupdate event, (as I could do with an Access backend), I needed to capture it in the form error event, which would occur before the message appeared. Thanks for your commentary - it all helps.

 

[GolferGuy]

Joanne,
Thank you for that update. Needless to say, I had forgotten about this situation, but the knowledge you just shared I'm sure will come in handy some day soon. Thanks again for posting this fix.