Error when referencing a field using a variable

[JustinP]

I keep getting a Run-time error '2465' when trying to run the code
below. This is usually associated with a misspelt field. The variable
'easeNum' produces the correct result, however it doesn't seem to like
using it in 'Me![easeNum]'. Anyone have ideas how I can get around
this?

Dim easeNum As String

For i = 1 To 10

easeNum = "EASEMENT" & Replace(Str(i), " ", "")

If Me![easeNum] Is Null Then 'ERROR OCCURS ON THIS LINE

'stuff happening

End If

Next

 

[Rick Brandt]

I keep getting a Run-time error '2465' when trying to run the code
below. This is usually associated with a misspelt field. The variable
'easeNum' produces the correct result, however it doesn't seem to like
using it in 'Me![easeNum]'. Anyone have ideas how I can get around
this?

Dim easeNum As String

For i = 1 To 10

easeNum = "EASEMENT" & Replace(Str(i), " ", "")

If Me![easeNum] Is Null Then 'ERROR OCCURS ON THIS LINE

'stuff happening

End If

Next

To make a control reference with a string (as in your variable) you need a
slightly different syntax...

Me(easeNum) instead of Me![easeNum]

BUT...you have bigger problems with your code. easeNum will never be null
because a) it is a String variable Type and only Variants can be null and b) it
will always at least have the Str(i) value in it.

Also Is Null is valid in SQL, not in VBA code. You need to use the IsNull
function.

If IsNull(Me(easeNum)) Then...

And...why are you replacing a space with a zero-length-string in a string that
will never have a space in it in the first place? Str(i) will never have
anything but the digits 1 to 10.

 

[RoyVidar]

And...why are you replacing a space with a zero-length-string in a
string that will never have a space in it in the first place? Str(i)
will never have anything but the digits 1 to 10.

I think you are discussing the CStr() function here, not the Str()
function (which reserves the first character for the sign of the
number)

? "Blah" & CStr(1)
? "Blah" & Str(1)