Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Curiosity (A97 SR2)

    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_ <gg> 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

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    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 <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    > (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 <gg>

    > (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" .. <g>

    Pat W.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Curiosity (A97 SR2)

    >>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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    Try this command in the VBA Immediate window:

    ?IIf(True, MsgBox("True"), MsgBox("False"))

    Guess what you get...

    Tom <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    > 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

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    > ?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! <g>
    Pat

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

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

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    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

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

    > 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

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curiosity (A97 SR2)

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

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curiosity (A97 SR2)

    "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 <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> for contributing to someone else's problem solving. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •