How to set Source Object of a Sub Report in VBA?

I have an Investor report, which has a coversheet sub report.  I need to set the source object of my sub report based on the Investor of the main report.  How can I set the source object using VBA?

Thank you,

Troy
T1080Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"You can't set the source object property in print preview or after printing has started." "

Well, the message in this case is pretty self explanatory. If the Source Object is not hard wired, you can only set it in the Open event of the report.

mx
0
 
tbsgadiCommented:
Hi Troy,

Use the Child /Parent Link


Good Luck!

Gary
0
 
tbsgadiCommented:
Select the Subreport & go to properties Data>Link Child Fields ...


Gary
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
T1080Author Commented:
My linking is all setup, let me explain my issue better.
I have a subreport named rptCoversheet, which has a source object of Report.rptCoversheet.  I have 25 different investor coversheet reports, which leaves me the need to change the source object of my sub report depending on the investor.  

I've tried to use Me.rptCoversheet.SourceObject to change the source object, but I've been unsuccessful. I receive error 2191 which says "You can't set the source object property in print preview or after printing has started."  I could be trying to use the code in the wrong event location.  

Any suggestions on how to accomplish setting the source object?

Thank you,
Troy
0
 
T1080Author Commented:
mx
My report is grouped by Investor.  Each Investor has a different subreport (which is a coversheet).  Is there a way to get a different subreport to apprear for each Investor?  What I've done in the past is stacked multiple subreports on top of each other, but I'm at the point where I need to find a more efficient approach.  It looks like the source object may not be feasible, any Ideas on how to accomplish my objective?
Thank you,
Troy
0
 
tbsgadiCommented:
Is the whole subreport different or just some graphics?
0
 
T1080Author Commented:
The subreports are very different.
0
 
tbsgadiCommented:
I think you should build them separately & run code that opens each one in order.
0
 
T1080Author Commented:
What code would accomplish that?
0
 
tbsgadiCommented:
Docmd.openReport Report1
Docmd.openReport Report2 etc

Add that to a command button on your form

0
 
T1080Author Commented:
That's not going to work as I need all the investors in 1 report.
0
 
tbsgadiConnect With a Mentor Commented:
Then it's 25 subreports!
0
 
T1080Author Commented:
That's the answer I was trying to avoid, and as of now I'm at a loss for alternatives.  My brain has been going in circles over this issue.
Thanks,
Troy
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can you put up a screen shot of the expected layout ...

mx
0
 
T1080Author Commented:
My screen shot is attached in a word doc.

DB-Screen-Shot.docx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
sorry ... I don't have Office 2007 loaded.  Can you send a .DOC file or a JPG.

thx.mx
0
 
T1080Author Commented:
I've converted it to 2003.

DB-Screen-Shot---2003-format.doc
0
 
T1080Author Commented:
Thanks for your suggestions.
0
All Courses

From novice to tech pro — start learning today.