Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-26-2019, 10:17 PM   #1
Zakman
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Zakman is on a distinguished road
How to change subreport source dynamically?

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?

Zakman is offline   Reply With Quote
Old 11-27-2019, 01:20 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,539
Thanks: 596
Thanked 1,008 Times in 950 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: How to change subreport source dynamically?

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
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 11-27-2019, 01:38 AM   #3
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: How to change subreport source dynamically?

Quote:
Originally Posted by Zakman View Post
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.

vba_php is offline   Reply With Quote
Old 11-27-2019, 01:54 AM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,620
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to change subreport source dynamically?

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 11-27-2019, 08:06 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,279
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to change subreport source dynamically?

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-27-2019, 08:08 AM   #6
Zakman
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Zakman is on a distinguished road
Re: How to change subreport source dynamically?

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?
Attached Files
File Type: accdb ForTesting.accdb (616.0 KB, 6 views)

Last edited by Zakman; 11-27-2019 at 08:15 AM.
Zakman is offline   Reply With Quote
Old 11-27-2019, 08:19 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,279
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to change subreport source dynamically?

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-27-2019, 08:23 AM   #8
Zakman
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Zakman is on a distinguished road
Re: How to change subreport source dynamically?

As requested included is the same file zipped.
Attached Files
File Type: zip ForTesting.zip (36.5 KB, 6 views)
Zakman is offline   Reply With Quote
Old 11-27-2019, 08:27 AM   #9
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: How to change subreport source dynamically?

Quote:
Originally Posted by theDBguy View Post
Otherwise, I'll have to wait until later to download it (due to bandwidth limits). Thanks.
guy,

you can't download a 616KB file due to bandwidth limits? really?
vba_php is offline   Reply With Quote
Old 11-27-2019, 08:28 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,279
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to change subreport source dynamically?

Quote:
Originally Posted by Zakman View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-27-2019, 08:33 AM   #11
Zakman
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Zakman is on a distinguished road
Re: How to change subreport source dynamically?

Quote:
Originally Posted by theDBguy View Post
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.
Zakman is offline   Reply With Quote
Old 11-27-2019, 08:37 AM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,279
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to change subreport source dynamically?

Quote:
Originally Posted by Zakman View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-27-2019, 09:07 AM   #13
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,539
Thanks: 596
Thanked 1,008 Times in 950 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: How to change subreport source dynamically?

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...
Attached Files
File Type: zip Uncle_1a.zip (73.8 KB, 6 views)

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change source of combo box dynamically scubadiver007 Modules & VBA 5 12-13-2012 02:51 AM
Dynamically set subreport source object ncraigg Forms 16 02-07-2012 08:27 AM
How to change the source object of a subreport? FISHiEE Reports 3 05-25-2005 11:08 PM
Repost: How to dynamically change field data source on a form Christine Pearc Forms 8 10-14-2004 06:48 AM
How to dynamically change field data source on a form Christine Pearc Forms 5 10-11-2004 05:45 AM




All times are GMT -8. The time now is 01:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World