The Variant is the only VBA data type that can accept the Null value. (It is
also the only type that can be Error, or Missing, or ...)
That is one of 6 issues we discusse in article:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
(#4 in the list, actually)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Hey! Great!
Checking for IsNull was sufficient to fix my problem. Great. I didn't know
about this.
Do you know why I have to pass in as a variant? I looked at you link but it
really didn't appear to address thsi particular matter.
I found that if I coded my Conditional Formatting as follows:
Public Function CheckTheValue(var1 As Variant) As Boolean
Debug.Print var1.name
If IsNull(var1) Then Exit Function
Debug.Print var1.value
'do your stuff in here.
End If
and my DataSheet is displaying n=3 rows whose var1.value are, in order, a,b
and c, then the CF procedure is called n+3 = 6 times, with debug.print
output of:
myField 'once for the first value.
a
myField 'once for some value that IsNull - mystery - what is this?
myField 'again for the first value - looks like we're starting over from
the top
a
myField 'then for the 2nd value
b
myField 'then the last value
c
myField 'then for some other value that IsNull - are we 'in' the
NewRecord area?
And if I then set .AllowAdditions to false, the calls where IsNull returns
true go away but it still gets called twose for the first value (regardless
of current record going in), viz:
myField
a
myField
a
myField
b
myField
c
So, it looks like it is getting called twice when the 'current record' is in
the new record area (BTW, anyone know a name for this area?)
Well my problem is solved, so HOORAY.
But, still, getting a better explanation of what is going on would be good,
and MS might like to tell the Access developers that they really don't need
to call the CF procedure for the 'new record' pseudo-row.
Agreed? Better understandings?
I have seen a number of posts talking about problems with conditional
formatting that I secretly chalked up to this behaior, but have not posted
cause I had not done the experiments to prove my thoughts. Finally, I know.
Next time I help!
....
On a related topic (should this be a new post?): I found that if I declare
the function with byRef var1 then some error occurs and CF turns itself off
for all controls on the entire form. If I call it with byVal var1,
everything is fine. If I omit it, everything is fine. BUt I thought byRef
was the default if left unspecified? Go figure... Any ideas?
--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA
Allen Browne said:
The issue may be with the function rather than with the conditional
formatting.
Set up the function so it accepts a variant. Then test if the value is Error
(as it may be if there is no control to pass in), or Null (as it will be at
the new row).
This kind of thing:
Public Function CheckTheValue(var1 As Variant) As Boolean
If Not IsError(var1) Then
If Not IsNull(var1) Then
'do your stuff in here.
End If
End If
End If
When you call the function in the Conditional Formatting, you do not need to
specify the form. Just call it as:
CheckTheValue([myField])
OK, sounds good. Especially now that my forms are instanced.
If that does not fix the problem, uncheck the boxes under
Tools | Options | General | Name AutoCorrect
For details, see:
http://allenbrowne.com/bug-05.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Malcolm Cook said:
Sometimes I get runtime error 2424, "The expression you entered has a field
... that access can't recognize". when accessing the controls .value
property inside a conditional formatting formula.
I would like a way to detect this without using On Error.
Is it possible?
Or, maybe you can suggest a way of not needing this.
Here's what i'm doing. I have a conditional formatting formula on a
control, say , which calls a custom VB function, liek this:
CheckTheValue([MyForm]!myField)
However, I think that it is when the last row on my subform containing the
control is displayed, when there is no current record, and thus no value for
the underlying control, that this error is generated.
Make sense?
Any workaround or better approaches in the wings?
Thanks!
