Automating multiple reports with a macro that prompts user for a date range

[Eddie]

I am a bit of a noob who needs help with a problem that I have not been able to figure out on my own.


A database that I created has a main query that includes a lot of calculations of data that it pulls from a main table. The main query prompts the user for a date range with a "Between [start date] And [end date]" request in the Criteria for the "Date" field. I have also created many secondary queries that get their data from the calculated fields in the main query. Finally, I have created about a dozen individual reports that are based upon each of these secondary queries.


I have automated the process of running these reports with a macro that executes the "OutputTo" action a dozen times to save each report as an individual Rich Text file. The problem I have is that, when I run the macro, it asks me for the [start date] and [end date] a dozen separate times.


Is it possible to modify the macro to prompt the user one time for the start and end dates and then to pass those dates on to each individual report/query? Or do I need to try to do this with a VBA module?


Thanks for any help you can give me with this matter.

 

[pbaldy]

The most common approach would be to have the user enter the dates on a form, and have the query look there:

Forms!FormName.ControlName

 

[HiTechCoach]

Here is an example showing hgow to use a form:

Report Dialog Examples

Hope this helps ...

 

[Eddie]

Thank you very much! That "Query by Form" technique does the trick. I created a form for the user to input the start and end dates and a command button to execute the macro that generates the reports. I then altered the criteria line in my main query so that it pulled the data from the form instead of prompting the user.

Thanks again!

 

[HiTechCoach]

You're welcome!

Glad we could assist.