Access - Modular reusable subreport

1
votes

I would like to create a report which I can use as a sub-report multiple times on the same parent report. However, each occurrence of the subreport should have different values.

For instance, there is a table called DailyReport. Records in this table contain: Date, member, team, description

The sub reports should be for each team within a certain date range. However, the date range per subreport/team will not be the same.

So, if the date range for all teams was consistent, then I could create a single subreport, and do some Ordering on the resulting records to separate things out into teams. However, with inconsistent date ranges, I can't utilize a single query, so the most straight forward solution I see is to create separate subreports and queries for each range of each team. The problem with this solution is that if I decide to change the format of the subreports I must do so in each specific subreport--a lot of duplicate work.

I would like to create a generic query and subreport. The query and sub report would call VB functions which would return the relevant value. This means my parent report has the same generic report on it multiple times. As each subreport is rendered, I would like to increment a value behind the scenes so that the functions which the generic query and subreport call know to return a different value.

However, it seems that's not how things work in Access. The subreports on a report are not rendered linearly. A subreport is created, and then "stamped" onto a report where ever required. This means that all of my generic subreports have the same data.

How can I define a generic report and query? Then plug in different values into the report and query while the report is being reused multiple times on the same parent report.

2
ms-access
If you put multiple instances of the same sub report, they can each have their own filter criteria. The underlying query may need multiple columns representing the needed date ranges for each report. – JeffO
You need to look into the LinkMasterFields and LinkChildFields property of reports. They are designed for exactly this purpose -- to filter a subreport based on current data in the main report, without needing any code or even queries. – pteranodon
@pteranodon Yup, I see now. This will do exactly what I need. Thanks. – kimbokasteniv
@pteranodon Actually, this seems to work for filtering just on a value, such as the team name. But I don't see how this could work for a date range. Am I missing something? – kimbokasteniv
@JeffO This seems like a viable solution. However, I don't seem to be able to set the filter per subreport instance, but only on the report itself. Or trying to do this via vb; it appears I can't use Report.Filter in the parent reports Open or Load event. Is there something else you had in mind? – kimbokasteniv

2 Answers

0
votes

You need to look into the LinkMasterFields and LinkChildFields property of reports. They are designed for exactly this purpose -- to filter a subreport based on current data in the main report, without needing any code or even queries.

You are correct that LMF/LCF do not work on date ranges, only values. So use LMF/LCF for the team filter.

For the date range filtering, you can use an unbound form that launches the report as two parameters defined in the base query. Create frmLaunch, and add two text boxes minDate and maxDate. Set their Format property to Short Date so Access with interpret them correctly and provide the date pickers. Now modify the base query, adding two Date/Time parameters [Forms]![frmLaunch]![minDate] and [Forms]![frmLaunch]![maxDate]. Now, find your date field and set its criterion to Between [Forms]![frmLaunch]![minDate] and [Forms]![frmLaunch]![maxDate]. Add a button to frmLaunch that runs the code DoCmd.OpenReport "YourReportName", acViewPreview.

0
votes

So, the goal was to make it possible to re-use the same sub-report multiple times on the same parent report, with full flexibility on how the subreport retrieves data.

I placed multiple instances of the same subreport on a parent report. On the subreports Open event I placed a line like

Me.Report.RecordSource = "SELECT * FROM someTable WHERE " & getCriteria()
nextCriteria()

Maybe its possible to pass a value that identifies which instance of the subreport is opening to the getCriteria function. Probably like a getCriteria(Me.Report.Name). But in this case I kept track of how many subreports had been produced in vb. Unfortunately, if your subreport has controls which have a data source which is a vb function, all reports will show the same value for that control. To get around this I added something like getSomeValue() & "As [Some Value]" into the SELECT of the SQL statement above. Don't forget to add single quotes or hashes around getSomeValue() if you are passing a String or date.

That's basically it, it's a pain. But I couldn't find a more elegant way to do it.

Edit: One major caveat I experience with doing this, is that although the print preview works correctly, when actually printing or exporting to PDF, some subreports would not be included. Maybe there is something else causing this...