Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subreport Prompting Parameters Multiple Times (2007 )

    I have a main report with a subreport. Each is based on a separate query that has criteria based on a form. The form has two text boxes, the first one for the criteria for the query/main report. The second for the query/subreport. The OK button on the form opens the report in Print Preview. When I move past the first record (one page) to the next record (second page), I get a prompt for the subreport criteria. If I type criteria in again, the page previews. If I leave the parameter blank, the second report previews with no subreport data. I thought by using a form to enter parameters I wouldn't be prompted multiple times!?!

    I found the following on an earlier post and am wondering if this applies to my situation:
    <hr>I think you are getting two prompts for parameters as the query is being opened twice. Is the report launched from a form? If it is then provide an intermediate dialog that requests the parameters, these are then passed back to the form launching the report which fills in hidden field(s) on the form with these params. Then in the query instead of prompting for a parameter look it up on the form you launch the report from. This also has the advantage (should it be desired) of being able to prompt the user with the last selection should the report be opened again (assuming the form isn't closed in the meantime). If the report is not being handled through a form then supply a dummy intermediate form that is hidden after the params are collected and closes after the report is closed.
    <hr>

    If so, can someone explain how to set this up?

    I'm attaching a sample of my database, too.

    Thank you!
    Attached Files Attached Files
    egghead

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

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    Many Loungers (including me) can't open databases in .accdb format because they don't have Access 2007 yet. You might get more replies if you attached a database in .mdb format.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    I am attaching in 2003 format. Thanks for the heads up!
    Attached Files Attached Files
    egghead

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

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    The query that acts as record source for the subreport will be opened each time you move to a new record on the main report. This query refers to a control on the StmtYears form, so you should *not* close this form after opening the report. If you remove the line

    DoCmd.Close acForm, "StmtYears"

    from the OK_Click procedure, the problem will not occur. If you don't want the form to remain visible, you can hide it:

    Me.Visible = False

    The value of the text boxes will still be available to the query, even though the form isn't visible any more.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    Yes! That works really great, plus makes sense!

    This leads to another question: I have the form on a switchboard. Closing the report displays the switchboard. If I click the form button to repopen the form, the criteria from the previous use remain. What would you suggest for clearing the criteria? Thank you!
    egghead

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

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    You could close the form in the On Close event of the report:

    Private Sub Report_Close()
    DoCmd.Close acForm, "StmtYears"
    End Sub

    That way, the form will be available as long as the report is open, and closed when it isn't needed any more.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport Prompting Parameters Multiple Times (2007 )

    That will work perfectly. Thank you so much!
    egghead

Posting Permissions

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