CODEWITHSUNDEEP
sql-servervbams-accesssql-server-2012ms-access-2010
Rominus
Rominus

Reputation: 1221

IsNull always returns false

I'm using MS Access as a front-end for SQL Server 2012, testing for null values in a field before passing the update to the server, and displaying a helpful message to the user.

I have it working fine for one field, using If IsNull in the BeforeUpdate event, along with some fancy duplicate checking. However, when implementing the same code for another variable, IsNull is always evaluating to false.

I've tried stripping out all the useful code and just using IsNull with a message box:

If IsNull(Me.M_RN) Then
    MsgBox "null!"
Else
    MsgBox "Not null!"
End If

This code always returns "Not null!". If I reverse it and use :

If not IsNull(Me.M_RN) Then
    MsgBox "Not null!"
Else
    MsgBox "null!"
End If

It still returns "Not null!" every time. I also tried other Event states e.g. Change, KeyUp, and Dirty. No matter the Event, IsNull comes back false.

The Form objects for these fields have identical properties. The only differences I can see are that the working variable is in numeric(18,0) format and not indexed, while the non-working variable is in int format and has a unique index.

If I remove all the code I get the You tried to assign the Null value to a variable that is not a Variant data type error on update.

Upvotes: 0

Views: 793

Answers (2)

Rominus
Rominus

Reputation: 1221

The issue was based on the not null setting on the SQL back end table. Access was checking the validation rule it derived from that restriction immediately before the BeforeUpdate event.

It was then popping the You tried to assign the Null value to a variable that is not a Variant data type error, which apparently prevents the BeforeUpdate event from firing at all.

Removing the not null restriction allowed the code to function, while retaining the unique index.

Using Form_Error couldn't work around it, even with Response set toacDataErrContinue. In that case it would pop an actual runtime error about validation rules preventing changing the value on the line trying to set the field back to .OldValue.

Upvotes: 0

Minty
Minty

Reputation: 1626

I think this part of your information is critical;

the non-working variable is in int format and has a unique index.

If it has a unique index then I'm pretty sure you won't be able to assign a null value anyway. Do you have a default value set in the table?

Upvotes: 1

Related Questions