Using Multiple Forms to run the Same Report with Different Parameters 1

[ShellyL]

Hi! Here is what I am trying to accomplish:

I have a query (Main_Query) as a record source for multiple reports. One of the fields on this query is Activity_Dt. One of the reports is Activity_Report, and users generally run this based on a specified activity date range.

Currently, the Activity_Report can be run from a form called Menu_One. I simply added boxes to the form named "RptStartDt" and "RptEndDt". Then I added a report filter: [Activity_Dt] Between [Forms]![Menu_one]![RptStartDt] And[Forms]![Menu_one]![RptEndDt]

Now I want users to be able to run the report from a second form as well(Menu_Two). When users run the report from Menu_Two, the Menu_One form may not be open (thus, they get prompted for parameters).

What's the best way to code the report so it is looking at the dates/parameter on whatever form happens to be open? Then I could just add a [RptStartDt] box to the second form as well. I'm not a programmer, so don't know if there is a standard reference to the "Open" form vs. a specific form.

Note: I don't want to have pop-up boxes prompt the user to enter the dates. Nor do I want to add the parameter to the query since the query is also a source for other reports that should not be filtered by date.

Any advice is greatly appreciated!

 

[MrTBC]

Hi ShellyL,
You can achieve this by writing some VBA code, but if you want a really quick fix then I would just make a second copy of the report for the second form, and then amend the Filter criteria.

 

[FancyPrairie]

Check out this FAQ faq181-5497 It will build the Where Clause for you. It works for date ranges, single and multi-select list boxes, combo boxes, option groups, and check boxes. And you don't have to know how to code. Just create a new module, highlight and copy the code from the FAQ, paste it in the new module, and name the module something like basBuildWhere.

The header in the function BuildWhere will explain how to use it.

Consequently, you don't have to worry about the parameter issue. Your report will be based on a Select Query and you open the report like this:

Docmd.OpenReport "YourReportName",,,BuildWhere(Me)

 

[TheAceMan1]

How are ya ShellyL . . . . .

[ol][li]Add the same two textboxes to the second form.[/li]
[li]In the [blue]Declaration Section[/blue] of a [blue]module[/blue] in the [blue]modules window[/blue], add two public variables:

[blue]Public BegDate As Date, EndDate As Date[/blue]

[/li]
[li]In the [blue]same module[/blue], copy/paste the following two functions:

[blue]Public Function RptStartDate()
   RptStartDate = BegDate
End Function

Public Function RptEndDate()
   RptEndDate = EndDate
End Function[/blue]

[/li]
[li]Change the [blue]filter[/blue] to:

[blue][Activity_Dt] Between [purple][b]RptStartDate()[/b][/purple] And [purple][b]RptEndDate()[/b][/purple][/blue]

[/li]
[li]What ever event your using to open the report, [blue]set the public variables[/blue] [blue]before you do[/blue]:

[blue]   BegDate = Forms![purple][b]FormName[/b][/purple]!RptStartDt
   EndDate = Forms![purple][b]FormName[/b][/purple]!RptEndDt
   Docmd.OpenReport . . . . .[/blue]

[/li][/ol]
[purple]Thats it! . . .[/purple]

See Ya! . . . . . .

 

[ShellyL]

Thanks AceMan1! This works EXACTLY as I hoped. Thanks for the clear instruction.