Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 Reports and Sub Reports

Posted on 2011-09-16
8
Medium Priority
?
311 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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