• Subreport Prompting Parameters Multiple Times (2007 )

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Subreport Prompting Parameters Multiple Times (2007 )

    Author
    Topic
    #455644

    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: [indent]


    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.


    [/indent]

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

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

    Thank you!

    Viewing 0 reply threads
    Author
    Replies
    • #1134993

      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.

      • #1134995

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

        • #1134998

          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.

          • #1135000

            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!

            • #1135004

              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.

            • #1135006

              That will work perfectly. Thank you so much!

    Viewing 0 reply threads
    Reply To: Subreport Prompting Parameters Multiple Times (2007 )

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: