Run Multiple Reports Through Form

[STEVENCV]

Hi,

I have an access database that is going to require around 30 different reports running off it on a regular basis. A lot of the reports are similar with slight changes.

Is it possible to create a form where a user inputs all the filters they wish to apply and then it runs a report? I need help creating the queries also.

For example, here are 3 reports:

1) The query looks for the 'Start Date on Service' field, and request a start date and end date, displaying records within those dates. (Note, I have created this query and it works)
2) Same as above, but also only shows those within the dates that also have the drop down field 'Client Type' option of 'Self Pay'. (How do I add this so it only displays those records?)
3) As above, but change 'Self Pay' to 'Response Only'

There are a few more like that, and then some other similar ones. If I know how to do multiple filters (i.e. date range AND drop down box option) I can transfer this across multiple queries.

I am able to create reports using my queries as a record source.

However, listing 30 or so reports to be ran will be difficult to browse through. Is there a way of creating a form whereby the user selects which 'type' of report i.e. ("Start Date Report", or "End Date Report") and it then allows them to either 1) show all records within the dates and 2) choose a further criteria based on a drop down list (each report type would be filtered on a different drop down list).

In total, I have 4 report 'types' each with between 4-8 different variations of options within the drop down boxes.

If I have not explained this very well, please don't hesitate to ask for clarification.

 

[pbaldy]

I'm on an iPad right now so it's hard to type code, but you could use this technique

http://www.baldyweb.com/wherecondition.htm

But instead of the simple filter you'd build a string conditional on the user selections.

 

[Pat Hartman]

I attached a database with some forms and a table I used a long time ago in an app with a complex reporting requirement.

Take a look at the table since it defines the reports and their arguments.

You can select 1 or more reports. When you select run, the code determines which arguments are required and unlocks only the text boxes required by the reports that were selected and pops up a second form to collect them.

PS - this code is really old and I didn't write it originally so it is not quite up to my standards but it will give you the concept and get you going. The report description has a "grouping" code which helps people find the report they need.

 

[STEVENCV]

Thank you very much for your help.

However, I have just had a meeting with the boss, and this has all changed now (sigh).

What they require now is ALL the reports running at once, taking a count of the number of records each report outputs, and then exporting those record counts into an excel template.

I am sorry to have wasted your time with the above query, but I was unaware that this would be changing.

I assume this change would make things simpler though?

 

[Pat Hartman]

Actually not. Now you need code in each report to count the records and update a table with said count at the end of the report (use the report's ReportFooter event to update the table with the final count since that will be the end of the report. Then you'll need a report to print the counts from the table where they were saved or code to export the table to Excel (use TransferSpreadsheet to export the count data).

You'll need to decide if you are keeping history or are overlaying the counts each time the reports run. If reports are run individually should the count overlay the stored count. Lots more decisions to make

Since you always run all the reports, you can create a parameter collection form that includes all parameters and has code to require that they all be present rather than the code from the sample I posted where the app locks and unlocks fields depending on whether or not they are required by at least one of the seleted reports.

 

[STEVENCV]

Ah, I was hoping it would be simple.

I have uploaded a sample copy of my database with my queries. If it's ok with you, please can take a look and give me an idea on just how much work it would be to count the records for each report?

There are further queries to be added, but I am working on them. You will notice I have created 1 report, which takes it's source from one of the queries. This was me testing how I could do a record count on a report. If you know of a better way of counting records I would be grateful to hear it.

All of the reports are ran on date ranges (between Start Date and End date). However, the field that is looked up for those dates differs depending on the report. Each time the reports are ran, those dates will be the same though.

For example, if we want figures for May 2012, we have queries that search for records based on 'Start Date On Service' (plus other criteria), some that search for 'End Date of Service' and some that search for 'Review Completed Date'.

Is there a way whereby you can enter this date once, and it will run all of those queries?

 

[Pat Hartman]

Use a form to run the reports. The form will have start and end dates and an option group or combo to select the report to run. The report's RecordSource query will reference the form fields:
Where MyDate Between Forms!frmRunRpts!txtStartDate and Forms!frmRunRpts!txtEndDate;