Solved

Access 2007 Reports and Sub Reports

Posted on 2011-09-16
8
309 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 48

Expert Comment

by:Dale Fye
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 48

Accepted Solution

by:
Dale Fye 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

628 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