05-28-12, 09:36 #1Registered User
- Join Date
- Feb 2012
Unanswered: Run Multiple Reports Through Form
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.
05-28-12, 11:29 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
What I've done in the past is have 2 forms
one the menu / switchboard whch displays the current parameters, and always stays open when the report suite is being run. from that form you either call the report (with the current parmeters OR open another form which allows the user to set those parameters.
the reason. the sub form has all the logic required to validate inputs, make certain all values are 'sane'. There's ne reason why you cannot do all that in the menu form, except there is a risk that the user may inadvertantly, or even deliberately adjust something. by making the changing of parameters it focuses their 'mind'* on the task in hand. it also makes each form less cluttered and simpler to explain. when you first load the menu form define known good defaults (eg a specific time period plus whatever else is the default set up). then that means you know the report suite always has known good / sensible values.
you can 'push' or 'pull' values between forms (and reports) using:-
assumign your two forms are called mysourceform and mydestinationform
forms!mydestinationform.thisvariable = thisvariable
thisvariable = forms!mysourceform.thatvariable
you can replace the varaibel with a control using the appropriate properties
you can set a variable or control from another forms variable or control
you can then pick up those parameters int he report and dispaly them on the report
Financial Year 2012, Month 3
Cost Centres exceeding budget by n %
.. and so on
*assuming your users may have a mind that isI'd rather be riding on the Tiger 800 or the Norton
05-29-12, 07:10 #3Registered User
- Join Date
- Feb 2012
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?