Curiosity (A97 SR2)

Topic

New Reply

I have been bitten once again by an irritating curiosity. It seems like Access reaches a point where it can no longer “handle” certain ordinary things. Every once in a while, when I start to get flukey happenings, I close Access and reboot, and start all over again — _occasionally_ the problem was not with me or with my code, but Access’ “little grey cells” were being overloaded. At least, this is what I think.
I also realize that it’s a _possibility_ that I’m doing something that taxes Access not because it is running out of resources, but because I’m doing something _slightly_ wrong that Access can ordinarily handle.
I wonder what comments I might get from all of you in the following occurence: I had the following line:
=IIf(is_form_open(“BouncePopReportLimits”),[Forms]![BouncePopReportLimits]![RepoNfo],” “)
in the control source of a text box on a report. I used this as the method to record the criteria selected by the user on the printed report. This should have worked, I thought, so that nothing would appear if the referenced form was not open. Instead, yesterday afternoon, the infamous “#Name?” kept appearing. I finally decided that I would “think about it tomorrow,” like Scarlett, and, this morning, everything’s working like a charm — with no changes made on my part. What gives? anyone have any ideas?
It’s not vital, it’s easy enough to solve, but still sort of a bother.
thx
Pat

Reply | Quote

Replies

A couple thoughts (I suspect you’ll get a few more from others):
(1) I believe both the true and false portions of an iif() statement are evaluated, regardless of the result of the logic evaluation. Therefore, if the form isn’t open, you may still get the #NAME? error because Access still tries to resolve [Forms]![FormName]![FieldName]. You may have to resort to a VBA function call that uses the if…then…else structure. Can you include the information of interest (that field on your form) as part of the report’s record source (possibly using an intermediate query that picks up this value as the record source) and then avoid referencing the form altogether?
(2) A periodic repair and compact seems to do wonders in squashing flaky behavior.

HTH,

Tom

Reply | Quote

> (2) A periodic repair and compact seems to do wonders in squashing flaky behavior.

I’m putting this first, because that was probably it, and not the memory usage. The very last thing I did last night was compact the database, from 15 megs to 2.5 approx. megs — I had been testing my “archive this table” part of the software

> (1) I believe both the true and false portions of an iif() statement are evaluated, regardless of the result of the logic evaluation. Therefore, if the form isn’t open, you may still get the #NAME? error because Access still tries to resolve [Forms]![FormName]![FieldName].

Hmm, I thought Access and other programs were optimized to skip over those conditions it does not meet — but, thinking along similar lines as yours, although not the same, I did try to reverse the order (iif “not” my condition). I also tried to specify the result, and just not assume the “true:” — like, “iif(is_form_open = true .. ” etc.
But I’m sure, now, that it was that it needed compacting, and maybe the memory also played a part.
Thanks for your answer! I remembered the 15 megs, and went, “duh” ..

Pat W.

Reply | Quote

>>Hmm, I thought Access and other programs were optimized to skip over those conditions it does not meet <<

You have to understand how IIF works. Access must resolve everything you are giving it to pass it to the IIF function. So the error is occuring before IIF gets it.

To resolve this, you will need to create a custom function to essentially do what you want IIF to do.

Reply | Quote

> To resolve this, you will need to create a custom function to essentially do what you want IIF to do.

Yes, I have a “flexi-title” function with a static that I’ve used in many places, and I’ve decided to just use that rather than picking up the information from a possibly open (or not) form.
Thanks very much for the interesting information!

Pat

Reply | Quote

Try this command in the VBA Immediate window:

?IIf(True, MsgBox(“True”), MsgBox(“False”))

Guess what you get…

Tom

Reply | Quote

> ?IIf(True, MsgBox(“True”), MsgBox(“False”))

LOL! I see! It _does_ go through both actions. No “instant evaluations.”
So, as Mark Liquorman says, Access must resolve everything, first before the information is passed to the iif() function — your line of code above sure illustrates that!
It also demonstrated why — even though I tried a variation of
“iif(is_form_open(),” / “iif(is_form_open() = True,” / “iif(is_form_open() = -1”, that the last variation might not have worked, anyway — since the true from the iif() seems to return a “1” rather than a “-1”, which I’ve noticed happening before (I don’t know if was another “iif()” function that returned the “1”)
Still, it seems to be able to “self-recover” from the error condition if there are enough resources or the database is nicely compacted? because, what was breaking last night, is no longer breaking “publicly” like it was last night …
Thanks for all your input, I enjoyed running that code sample!
Pat

Reply | Quote

I would just like to say that this pleasant exchange of info sums up my conception of Woody

Reply | Quote

> I would just like to say that this pleasant exchange of info sums up my conception of Woody

Reply | Quote

“Unnecessary” exchanges are part of the learning process too, and this one may help someone else with a similar problem think it through for himself. For that, you deserve a for contributing to someone else’s problem solving.

Reply | Quote

One last thing: You’re getting a “1” from the code example because MsgBox(“True”) evaluates to “1” (you clicked the “1”-st button (the only one – “OK”) to exit the box — MsgBox() returns which button you clicked, and that’s the value that the iif() function returns).

If you try:

?IIf(True, MsgBox(“True”, vbOKCancel), MsgBox(“False”, vbOKCancel))

and click on Cancel, you’ll get a “2” (the 2nd button was clicked).

Logical expressions should always evaluate to -1 (True), 0 (False), or Null. What value is actually returned from iif() depends on the 2nd and 3rd arguments, respectively, of course.

Tom

Reply | Quote

> One last thing: You’re getting a “1” from the code example because …

Thanks — I’ll try the information from this most recent post later, but right now, I can’t let the day end before making this confession:

Guess what I found in the “onformat” of the report pagefooter:

If is_form_open(“BouncePopReportLimits”) Then
nfo.Visible = True
Else
nfo.Visible = False
End If

IOW, the ” = iif()” controlsource that I was using was _still_ broken, it had nothing to do with whether I’d bogged down the memory, or I’d cured it by compacting the database — I had simply and merely, out of frustration, “invisibled” my error to the outside world — and even myself, the next morning, when I forgot all about it.
Aaauuugghh!!! — I hope I haven’t led people down the primrose path.
Sorry for all this, but thanks to you and Mark for all your enlightening information!

Pat

Reply | Quote