Hi... in my ongoing task of migrating our in-house database to Access 2010 I've come across some behaviour that's messing up bits of my VBA code that use the IsNull() function.

In previous versions of Office (including 2007), when a form first loads, calling the IsNull() function on any unbound combo box or text boxes that have nothing entered would return True. However I'm finding that in Access 2010 this isn't happening unless I specifically set the default value of the control to NULL.

This only seems to happen when the form has a RecordSource set.

Is anyone aware of whether this is a bug, or is it deliberate behaviour? Like I say, I can remedy the problem by making sure I've set the default value for each control to Null where this should be the case, but my project has several hundred forms and I don't particularly want to go through this laborious task unless I have to!

Thanks

I am not seeing this behavior. Both combo and text boxes that are un-bound and don't have a default return true for isnull() and do so on forms that are bound to a table. I not aware that there is a change from 2007 in this regards.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

I should have mentioned that I'm using an ADP with a SQL Server 2005 backend... I've just tried with a normal .accdb file and it worked fine in that case, looks like the problem may be restricted to ADPs?

What you're seeing is an "empty string".

There is a difference between an empty string and a null. In fact, not all datatypes support nulls.

The best way around this is to test for the empty string using the '&' concatenator.

IIF([Expression] & "" = "", TruePart, FalsePart)

When using the '+' to concatenate you get propagation of nulls, but when you use '&' to concatenate you don't. This method will allow you to test for an empty string or null in the same statement.

i mention this before
access 2003 adp +sql server 2008,work fine
access 2010 adp+sql server 2008,as you say, do not returen null

I am having this problem too.It seems with access 2010 what is happening is that if you assign the value null to a control which has a default value, it just automatically sets the value of that control to what the default value is, instead of actually assigning null that to that control.

This is causing a few problems in various areas of my programs as this behaviour is different from access 2003-2007.

It must be a bug.

I'm having a similar problem. We just got upgraded to Access 2010, and things that used to work don't work anymore. Our database has this code:



In 2007, everything works. In 2010, once we tab into this field we are incapable of tabbing out. It doesn't seem to be recognizing "null" the way it used to, so it's running the code in that "if" statement whether the combobox is empty or not. Like Dave's, our database links to a SQL server backend. It's definitely a bug in 2010, and I can't figure out a good way to fix it.

I concur, this is a problem.

I had a related problem, which didn't occur until I changed to Access 2010. I've got an adp against SQL Server 2005
I have a bound form with a bound combo box cboContainer. The form also had an unbound text box with control source
=GetContainerCategory([cboContainer]).

The function called was
Public Function GetContainerCategory(vContainerID As Variant) As Variant
GetContainerCategory = ExecuteScalar("procGetContainerCategory", Array(vContainerID))
End Function

where ExecuteScalar is a utility function i wrote to execute stored procedures that may or may not take parameters.

This used to work fine when the combo box had no selection (as when the form opened), because its value was null and the execute scalar was fine with that.

After the 2010 conversion, it failed because the combo was an empty string. My experience with previous versions of Access is that the bound column was null if there was nothing selected in the combo, and that other columns were empty strings if they "had no data" (that is, cboContainer would be null, cboContainer.Column(3) would be "".

The work around is a standard one for dealing with times when you don't know if you've got a null or an empty string -- maybe in future versions this thing is going to be null again!

Public Function GetContainerCategory(vContainerID As Variant) As Variant
Dim v As Variant

If Len(vContainerID & vbNullString) = 0 Then
v = Null
Else
v = ExecuteScalar("procGetContainerCategory", Array(vContainerID))
End If
GetContainerCategory = v
End Function

On the other hand, I've had no problems with setting the value of an unbound combo on an unbound form to null, as in clearing choices in a search form.

I'm an Access developer since v2.

Another problem, not new to 2010 -- after updating a record in a subform, getting an error message that states that the change was saved to the database, but won't appear in the form because it doesn't meet the criteria of the underlying record source, although I know it does, and in fact it appears if I requery the form.