Solved

Access 2007 Reports and Sub Reports

Posted on 2011-09-16
8
260 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now