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.

[iordanis]

Sorry for the delay.

So, you mean that both the main report and all subreports, retrieve all their records at the same time and then thw report/subreport events are executed?

[June7]

Can pass filter criteria for main report in OpenReport command with its WHERE CONDITION argument.

I have to modify my earlier comment. It is possible to set a report's RecordSource with code in report's Open event. Once report is fully open (rendered), its dataset cannot be altered (unlike forms).

However, in your case, I don't see that this provides any advantage over use of Master/Child Links along with OpenReport filter. Need more information on why that does not suit your requirements.

[iordanis]

I understand what you all wrote above. It's just that I already have created that subreport for use in several other reports, so I wouldn't like to maintain another one.

Can someone answer the order of events that occur in the main report and subreports, after DoCmd.OpenReport is called? Maybe it could help, so I can pick the write place to write the subreport retrieval for each main report's record (if it's feasible after all)

[Welshgasman]

Here is what ChatGPT offers.

https://chatgpt.com/c/685a877f-d640-...ways%20needed.
However I *think* that after the report is loaded, it cannot be changed.

I seem to recall I had that issue in my main report, where the structure of the subreport changed after a particular magazine number.

Private Sub Report_Open(Cancel As Integer)
' Change report layout for 44 onwards.
Me.srcRest_By_Ship.SourceObject = "Rest_By_Ship"
If Nz(Me.OpenArgs, 0) > 43 Then
    Me.srcRest_By_Ship.SourceObject = "rptRest_By_Ship"
    Me.srcRest_By_Ship.Left = 0
    Me.srcRest_By_Ship.Width = (Me.Width - 720)
End If
End Sub