The Linux Page

MS-Access and variables set to Nothing

In VB and MS-Access, whenever a variable referencing an object is set to Nothing, you cannot access any field since the variable does not actually point to a physical object.

This, code like this breaks:

	Dim r Is Object 
	r = Nothing 
	...
	r.Field = 5  ' Breaks if r is still Nothing

Thus, you need to test whether the variable r is defined.

If you look at the documentation, you will find the following set of functions:

	IsEmpty()
	IsError()
	IsMissing()
	IsNull()
	IsObject()

There are a few others, but you can easily guess that IsNumeric() won't be useful here.

So? You would think that if the variable is to reference an object but it is currently Nothing, the IsObject() function would return false. It is not the case! When you set a variable to Nothing to tells the Visual Basic environment that this variable represents an object. Thus, IsObject() returns true.

I looked for a solution for a little while, twice now, so I wanted to write down the solution. It simply is not logical that you need to use a special syntax for objects. What you would think would work is this:

	If r = Nothing Then
Exit Sub
End If

But somehow the compiler tells you "Invalid Use Of Object". This means Nothing is considered to be an object and the compiler cannot compare two objects with the equal (=) operator.

So? What's the solution?

There is another operator named Is. (The verb "to be" at the third person.) Like in Javascript (and I think Java) You can compare two object variables and know whether they both reference the same object. Thus you can write:

	If r Is Nothing Then
Exit Sub
End If

For more information, review the Is operator!

Note that there is no Is Not operator. Intead, you negate the whole test by adding Not before the first variable as in:

	If Not r Is Nothing Then
Exit Sub
End If

The other way around, if you try to test a standard variable (i.e. a non-object) with the Is operator, you get a "Type Mismatch" error. The Is operator should only be used when IsObject(var) returns true or you know for sure that the variable represents an object.