Results 1 to 10 of 10
  1. #1
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Multiple filtered previews of same report? (Access 2002 SP-2)

    Is there any (relatively straightforward) way to use VBA to open several previews of the same report simultaneously (i.e., in separate windows that will all remain open), with a different filter in effect for each?

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

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    That depends on what you mean by straightforward. It can be done, but it involves classes and public collections and the codes to handle same.
    Charlotte

  3. #3
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    Thanks for the quick response. When I was first learning about class modules (from the Developer's Handbook), I set up a pair of classes (an instance class and a collection class) to keep track of multiple instances of a form. I assume you're talking about more or less the same approach for reports, and I think I'll pass for now, because the added value of having them all open at once is pretty small in this particular case.

    The one specific element I'm most curious about is what code I'd use to apply a filter to a particular instance of the form. I was messing around with code like this:
    <pre> For x = 1 To UBound(astrStuff, 2)
    strWhere = astrStuff(2, x)
    Set rpt = New Report_rptStuff
    rpt.Filter = strWhere
    rpt.Visible = True
    Next x</pre>

    I could get multiple instances of the report to open at the same time with this code (and stay open as long as my code was in break mode), but none of the reports were filtered (i.e., my rpt.Filter line had no effect). Not seeing a Requery or Refresh method for rpt, I threw in the towel and headed for the Lounge.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    After rpt.Filter = strWhere, you must insert rpt.FilterOn = True. Without that line, the filter is not applied.

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

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    Yes, you can handle the reports the same way as the forms. It is possible to open multiple instances without the class handling, but you can't control them very well without the collection to keep track of each specific instance so that you can manipulate that specific report without affecting the others.
    Charlotte

  6. #6
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    Sorry. I knew that once. (More than once, actually, although always in the form context.) <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    Armed with Hans's reminder, I couldn't help messing around a bit more and am pleased (but also somewhat bewildered) to report that the following code is working just fine:
    <pre> Dim rpt As Access.Report
    For x = 1 To lngFilterCount
    Set rpt = New Report_rptStuff
    rpt.Filter = astrFilters(2, x)
    rpt.FilterOn = True
    rpt.Visible = True
    Set rpt = Nothing
    Next x
    </pre>

    (The fact that I can't go on to manipulate particular instances with more VBA code doesn't matter in this case.)

    What's bewildering me is why the instances are staying loaded after the rpt variable is set to Nothing.

    Extra bonus question: This particular glitch seems to have cleared up, but for a long time the .Filter = line was causing an "Unknown Jet error" (which closed the report), and the error only occurred (but consistently) if the VBE window was open. Any insight?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    Setting rpt to Nothing releases the memory occupied by the rpt variable. If you want to close the report, use DoCmd.Close ...

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

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    And without a collection to manage the open report instances, you really have no idea which one will be closed by DoCmd.close. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple filtered previews of same report? (Access 2002 SP-2)

    I'm not going to be closing the reports with code, and it's worked out well (in this case) that the reports stay open.

    I'm just puzzled because I thought that when you used the New keyword to instantiate a form or report and assign it to an object variable, the form or report automatically unloaded (like it or not) after there were no more object variables pointing to it. But it looks like reports and forms are different in this regard. (If I run my same code with a form instead of a report, the form unloads as soon as the frm variable is set to Nothing.)

    Thanks again for all the input.

Posting Permissions

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