How to change subreport source dynamically? (1 Viewer)

Zakman

New member
Local time
Today, 19:59
Joined
Nov 27, 2019
Messages
4
I just cannot figure out how to create a dynamic reporting so that the recordsource of the subform change based on data in the source table.


I have one report with some general stuff in the header section. I have 10 different "subreports" and I want all kind of combinations of those reports. The best way to do it would is to put those in "dynamic" report.



Now, the source of this main report (parent) is a table which includes 52 combinations of those subreports. The fields in the records includes SQL-clauses or report name for the recordsource for the subreport. But I cannot get this work. I have tried days to get this work but no success.



It seems that after opening the report the subreport recordsource info cannot be changed.



I would really like to prevent making 52 reports by making this report generator based on data in the field.



How can I do this?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:59
Joined
Jul 9, 2003
Messages
14,515
Reading your question, I get the impression that your data structures are incorrect. I realise that you have not provided your data structure, so my comment might seem a bit strange to you.

When you say 52 Subreports, it sounds like you are trying to to manipulate a report, when you should actually be looking at the raw data and approaching the problem in that perspective.

The following link demonstrates a different scenario than yours, but it might hint at what I'm trying to say:-

https://www.niftyaccess.com/generate-multiple-reports/

Sent from Newbury UK
 

vba_php

Forum Troll
Local time
Today, 11:59
Joined
Oct 6, 2019
Messages
2,884
It seems that after opening the report the subreport recordsource info cannot be changed
of course it can't! that's the same concept as when you open like a word document, you see that white icon next to the document's icon in windows explorer indicating that you have locked the file for editing. I don't think Tony understands what ur saying Zakman. I might have a better idea....if uve got the sql statements that are meant to used in the query statement which serves as the source for the subreport, there is nothing wrong with that as that kind of thing is done all the time. that's the same thing as the "code that writes code" concept and it saves lots of time, so no issue there.

the things u have to ask i think is:

> are my report and subreport linked via a 1-many relationship?
> when i change the source for the subreport, do i still need to link the subreport's data to the main (parent) report?
> are the sql statements that are stored in the table actually valid, in terms of their field reference syntax and field name existence?

perhaps in scenario like urs zakman, it would be best to show tony (uncle gizmo) and the rest of ur database so we can see better what ur talking about instead of trying to read an explanation from u, cuz this is difficult for anyone to explain.
 

June7

AWF VIP
Local time
Today, 08:59
Joined
Mar 9, 2014
Messages
4,376
I have passed an SQL statement to a report via OpenArgs then code in report Open event sets its RecordSource property. But OpenArgs would not be available to a subreport so the event would have to get SQL statement some other way.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
18,970
Hi Zakman. Welcome to AWF! I am having a hard time picturing what you're trying to do. Why isn't linking the subreports to the main report isn't enough? Did you also want to change the Control Sources for each Textbox in the subreports?
 

Zakman

New member
Local time
Today, 19:59
Joined
Nov 27, 2019
Messages
4
Thank you very much for your interest in my beculiar problem. It was very hard to explain what I meant but as someone suggested I created a total fictionary example database of my problem.


The real deal is MUCH bigger and much more complicated with all the zillion forms and queries but this is a fast done example of the problem I am facing. I call it as dynamic reporting.


In my original database there are now 14 different subreports and 104 combinations of those. In the example there is only 2 reports and 4 combinations. You can imagine my dilemma; Do I really create 104 reports more or can I make a one smart report.


Anyone willing to help me out here?
 

Attachments

  • ForTesting.accdb
    616 KB · Views: 425
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
18,970
Hi. I appreciate you posting a sample db to help us understand your request better. I would just like to ask one more favor to please zip up your file and repost it, so I can download it now. Otherwise, I'll have to wait until later to download it (due to bandwidth limits). Thanks.
 

Zakman

New member
Local time
Today, 19:59
Joined
Nov 27, 2019
Messages
4
As requested included is the same file zipped.
 

Attachments

  • ForTesting.zip
    36.5 KB · Views: 431

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
18,970
As requested included is the same file zipped.
Hi. Thanks for that. So, do I understand it correctly that, in this sample db, there is only one subreport control, but you want to display two different subreports in them?
 

Zakman

New member
Local time
Today, 19:59
Joined
Nov 27, 2019
Messages
4
Hi. Thanks for that. So, do I understand it correctly that, in this sample db, there is only one subreport control, but you want to display two different subreports in them?


That's right- The other option (maybe) is to add all of the subreports there and there show/hide them but still i have problem feeding the recordsource.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
18,970
That's right- The other option (maybe) is to add all of the subreports there and there show/hide them but still i have problem feeding the recordsource.
Hi. This might be a better approach. I'll give it a try too.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:59
Joined
Jul 9, 2003
Messages
14,515
I don't think it's possible to do what you're trying to do. You've only got one subform, and you want to change it Record Source on each generation of its display in the main report. I have on many occasions said something is not possible, and I have often been proved wrong! So I didn't say that OK!

What I meantin my earlier reply is, you want to approach it from the actual data. I've shown an example attached...
 

Attachments

  • Uncle_1a.zip
    73.8 KB · Views: 285

Users who are viewing this thread

Top Bottom