Single Report, Many Search Criteria activated by a button for each.

[Robbyp2001]

Hello folks



I have a database that at the moment contains 11 reports. In design, each report is identical, just the underlying queries are different. Each report is opened from a common form and a button for each.

Although the reports all work well, it seems to me that it is unnecessary to replicate the report format 11 times and that one report could be generated if the underlying data could change depending on what buttn was pressed and what criteria was applied. This, I’m sure, would require some programming, which is where I am stuck.

Here’s an example of four of the queries reports that I use currently:

ReportsMidTerm (report) ReportsMidTermqry
This would return all of the mid term records for Yeargroups 7. 8, 9 and 10.

ReportsMidTerm (report) ReportsMidTermByFormClassqry
This would return all of the mid term records for a particular form class selected from a drop down list.

ReportsMidTerm (report) ReportsMidTermByStudentqry
This would return all of the mid term records for a particular student selected from a drop down list

ReportsMidTerm (report) ReportsMidTermFormTutorqry
This would return all of the mid term records belonging to a form tutor selected from drop down list.

So, kind folks, is it possible to construct some code that will contain each of the query search criterias that will be linked to individual buttons on a form? Each search criteria will populate the same report ‘ReportsMidTerm’.

Since I know that each of the queries that I have now works correctly, can the SQL Code behind each be used when programming?

I think, if I could see an example of two or three separate queries using the same report, I could build this up to contain all 11….I think.

Is it possible?

Rob

[pbaldy]

You could set the record source of the report in its open event, using the form to determine which query to use. That would require the 11 queries. You could also use the SQL of those queries in the same process, saving the 11 queries. I'd probably dynamically build a wherecondition and use this:

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

which would be the most flexible, allowing any combination of criteria.