Subreport Prompting Parameters Multiple Times (2007 )

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

That will work perfectly. Thank you so much!

Reply | Quote