Solved

Access 2007 Reports and Sub Reports

Posted on 2011-09-16
8
298 Views
Last Modified: 2012-05-12
Is it possible to have a main report field call a subreport.  I have a type field the comes back in my main report. The type field = A.  or type field = B. or type field C. If type field = a display sub report 1 inside main report . I f type field = b display sub report 2 inside main report.  Which report event should this take place in?
0
Comment
Question by:centralmike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36550710
Sure,
But is unclear what *Exactly* you need help with or how your current report is designed.
Everything or just the swapping of subreports...?

In the most basic sense, typically, the subforms will all be the same, only the Recordsource will be "Swapped"

So in a very general sense, you would need code that says:
If me.Type="A" then
    me.MySubreport.Recordsource="SomSQLOrQueryForA"
elseif If me.Type="B" then
    me.MySubreport.Recordsource="SomSQLOrQueryForB"
elseif If me.Type="C" then
    me.MySubreport.Recordsource="SomSQLOrQueryForC"
End if

Depending on what section the "Type" is in, and what section the subreport is in, will determine what event, this will run on...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36550754
Assuming you have a subreport control in the Details section of your report, and that section also contains your [Type] field, you should be able to use the Detail Sections "Format" event to do what you want.  In Jeff's example above, he is assuming the same report, but different recordsources.  If you want to use different subreports, then it might look like:

Private Sub Detail_Format

   if me.Type = "A" Then
       me.mySubReport.SourceObject = "rpt_subReport1"
        me.mySubReport.visible = false
   elseif me.Type = "B" then
       me.mysubreport.sourceobject = "rpt_subReport2"
        me.mySubReport.visible = false
    else
        me.mySubReport.visible = false
    end if

end sub
0
 

Author Comment

by:centralmike
ID: 36550766
It happens in the details section. Its not the recordsource I am changing.  I am chaning 3 separate sub reports depending on the value thats in the type field.  The sub reports have different detail depending on the type.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36550812
But remember here that in "typical" situation, you may be able to create a "Grouped" report  that  displays the data in this manner.
Or in another case the Subrport itself should handle the "Filtering"
In a true Main/subform (Customers/Orders)
The "Linking" field (CustomerID) handles the Filtering.
So simply having CustID 1 in the main form will only display Orders for that customer.
Hence, no need to load separate subforms...

So, as you can see, sometimes it is best to state your ultimate goal, instead of the way you think it should be done...

;-)

JeffCoachman
0
 

Author Comment

by:centralmike
ID: 36551674
The code works when you preview the report.  But I try an print the report I get runtime error 2191?.Any thoughts why this showing up?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 36551723
Which code works?


Sorry, I don't have runtime error 2191 memorized.  What is the message?

Rather than change the SourceObject, you may have to put three separate subform controls on the form, and change their visible properties. based on which one you want to show:

Private Sub Detail_Format

    me.subform1.Visible = (me.Type = "A")
    me.subform2.Visible = (me.Type = "B")
    me.subform3.Visible = (me.Type = "C")

End Sub
0
 

Assisted Solution

by:centralmike
centralmike earned 0 total points
ID: 36551745
runtime error 2191 you cant set the source object in print preview or after the printing has started.  Here is the code I used:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 If Me![ADF_TYPE] = 1 Then
       Me.Child94.SourceObject = "rpt_sub_aso"
        Me.Child94.Visible = False
   ElseIf Me![ADF_TYPE] = 2 Then
       Me.Child94.SourceObject = "rpt_sub_txt"
        Me.Child94.Visible = False
    Else
        Me.Child94.Visible = False
    End If



End Sub
0
 

Author Closing Comment

by:centralmike
ID: 36908495
the solution worked great
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question