Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Access 2007 Reports and Sub Reports

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
centralmike
Asked:
centralmike
  • 4
  • 2
  • 2
2 Solutions
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Dale FyeCommented:
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
 
centralmikeAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
centralmikeAuthor Commented:
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
 
Dale FyeCommented:
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
 
centralmikeAuthor Commented:
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
 
centralmikeAuthor Commented:
the solution worked great
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now