report with subreports having parameters

[iordanis]

I have a master report that has a subform for details. The detail subform, is based on a query that takes SQL parameters, using a global function in the WHERE clause for the criteria values.

So, how (in which event) can I send the value from the each master report's row to the subreport, so it can retrieve its own detail records to be displayed on page. I use the PrintPreview view for all my printouts.

MSAccess2013

[Micron]

I'd try Open event for the main report. You could retrieve a report field value, or if it's not there and can't be included, the OpenArgs property for the main report.

[iordanis]

No, what I want is, for every master record in the report, its details to be displayed, using a query with SQL parameters in the subreport. Is there a way (in some of the report's events, like in "currentrow" for forms) for the subreport, to use the master record's value, to retrieve the subreport records?

[Micron]

using a query with SQL parameters in the subreport.

That doesn't change my answer. Your query would look to one or more main report controls to get the parameter values. I think most/many developers would set the Link Master Fields and Link Child Fields on the subform/subreport control and let that do the heavy lifting. If nothing suggested solves your problem, I think an explanation of how the main and sub reports are related.

[iordanis]

Well, Master Fields and Link Child Fields work when both the master report and the subreports have retrieved ALL respective records, but I don't want to retrieve that many records at once. For simplicity, my report has a main report object (let's say 100 master records on paper) and for each record, a subreport on the right, has to retrieve only the details for that record, so I used SQL parameters for the subreport's query. My question is, how (in which event and with which commands) I can syncronize the detail retrieval for each master record displayed.

[June7]

Seems to me what you describe should be accomplished with Master/Child Links or Sorting & Grouping arrangement. Perhaps you should provide db for analysis.

[Welshgasman]

Well, Master Fields and Link Child Fields work when both the master report and the subreports have retrieved ALL respective records, but I don't want to retrieve that many records at once. For simplicity, my report has a main report object (let's say 100 master records on paper) and for each record, a subreport on the right, has to retrieve only the details for that record, so I used SQL parameters for the subreport's query. My question is, how (in which event and with which commands) I can syncronize the detail retrieval for each master record displayed.

I think you are confusing reports with forms.
Once the data is generated, that is it.
So the Master and Child fields are where anything is synchronised.

[iordanis]

I just want to know, if I can retrieve and display data for a subreport, using the master id value being formatted for display in the main report. I don't want to retrieve the whole detail table into the subreport, just to create filter links with the Master Fields and Link Child Fields properties. Too much in memory. Is this feasible with Access?

[davegri]

Why can't you create a query with the master report table and the subreport table? You could use a subquery that extracts all the data from the subreport table using criteria from the main table. Then you could scrap the subreport as not needed. All the required data is in the main query with its subquery.

[June7]

If you pull in all records on main report, then how do you expect each to show its related child records in subreport? This is what Master/Child Links properties manage. Report's RecordSource cannot be dynamic. Either the data is retrieved when report opens or it isn't, dataset cannot be modified after report renders. So, simple answer to your question is "can't".

If there is only one related child table involved, subreport is likely not needed. Join tables in report RecordSource and use Sorting & Grouping features.