Pass Parameter to multiple subreports

Question

Hi Everyone,

I have a report with 4 SubReports, each of the 4 Sub Reports are made a 4 seperate queries that each need the parameter [Enter Festival Year:], now all the subreports and queries run fine individually.

When I run the Report that has the 4 sub reports I have to enter the festival year 4 times. Since the parameter will be the same for each query or subreport is there a way to enter it one time and pass it to each subreport?

Thanks,

Phil

Answer 1

Create a form frmParameters with a text box txtFestivalYear.

Change the parameter [Enter Festival Year:] to a reference to the text box:

[Forms]![frmParameters]![txtFestivalYear]

Of course, the form must be open when you run the report. You could place a command button cmdOpenReport on frmParameters to open the report:

Private Sub cmdOpenReport_Click()

    If IsNull(Me.txtFestivalYear) Then

        Me.txtFestivalYear.SetFocus

        MsgBox "Please enter a year", vbExclamation

        Exit Sub

    End If

    DoCmd.OpenReport "rptMyReport", acViewPreview

End Sub

where rptMyReport is the name of the report.

Answer 2

Thanks as always the people over here on the access forums are very helpful with quick replies

Have a great weekend.

Phil

Answer 3

Yes - simply delete [Enter Festival Year:] and enter [Forms]![frmParameters]![txtFestivalYear] (using the actual name of the form and text box or combo box that you created) where it used to be.

Answer 4

OK that all is exactly what I wanted, but I have one question

Where in the Query do I put:

[Forms]![frmParameters]![txtFestivalYear]

just where [Enter Festival Year:] used to be ?

Answer 5

System generated parameter prompts like this are cheap and cheerful, but a far better solution is to reference a control on an unbound dialogue form as the parameter, in which case you only need enter the value once, e.g.

Forms!YourFormName!txtFestivalYear

Then add a button to the form to open the report, or maybe two buttons, one to preview and one to print the report.  You'd then open the form, enter the date and click the button rather than opening the report directly (though there is a way you can open the report directly and get the report to open the form, but we can come back to that if you wish).

You can go one step further, however, and make the form more user friendly by using a combo box instead of a text box, so the user can select from a list of years.  If you have a table which has a column of the festival years then the RowSource property for the combo box would be like this:

SELECT DISTINCT [FestivalYear] FROM [YourTable] ORDER BY [FestivalYear];

If on the other hand the table includes full dates rather than the years it would be:

SELECT DISTINCT YEAR([FestivalDate]) FROM [YourTable] ORDER BY YEAR([FestivalDate]);