locked
Report - Filter Main Report if Subreport is null RRS feed

  • Question

  • Is there a simple way to hide rows in a main report if the subreport is blank?

    Main Report Record Source = table

    Subreport Record Source = query

    Tuesday, July 18, 2017 9:43 PM

Answers

  • Hi FK_1234,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    • Marked as answer by FK_1234 Thursday, August 3, 2017 2:06 PM
    Thursday, August 3, 2017 8:46 AM

All replies

  • Hi,

    You might try something like the following in the Format or Print event of the Detail section. For example:

    Me.Detail.Visible = Me.SubreportName.Report.Recodcount>0

    I don't remember the correct syntax, but I hope you get the idea.

    Hope it helps...

    Tuesday, July 18, 2017 9:53 PM
  • Assumimg the subreport is located in the parent report's Detail section you can cancel the instance of the section if the subreport has no data with the following in the Detail section's Format event procedure:

        Cancel = Not Me.[NameOfTheSubreportControl].Report.HasData

    NameOfTheSubreportControl is the name of the control in the parent report's Controls collection which houses the subreport.  This might or might not be the same as the source report object.



    Ken Sheridan, Stafford, England

    Tuesday, July 18, 2017 11:22 PM
  • I have tried both of the following in the Detail section of the Main Form, and neither are working.  Any other ideas?

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Detal.Visible = Me.SubCage.Report.RecordCount > 0
    End Sub

    Also tried:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = NotMe.[SubCage].Report.HasData
    End Sub

    Wednesday, July 19, 2017 4:06 PM
  • Hi,

    The Format event only fires in certain views. Did you try the above suggestions while actually printing the report?

    I think the Format event fires if you Preview the report but not if you use Report View.

    Wednesday, July 19, 2017 4:13 PM
  • The main report will eventually be placed into a form.  Is using the Format Event still the best route to go?
    Wednesday, July 19, 2017 4:17 PM
  • Probably not... Try the Load event... Or, the Paint event.

    And... you may have to use a different set of code if we can't use the Detail property. For example, try something simple first like:

    Me.ControlName.Visible = Me.SubReportControlName.Report.HasData

    • Edited by .theDBguy Wednesday, July 19, 2017 4:25 PM
    Wednesday, July 19, 2017 4:21 PM
  • Hi FK_1234,

    As Ken and DBguy said, I also suggest you use Detail_Format event to hide the row in print preview.

    Here is the example.

    'ID is control ID of a field in main report source and SubReport is the sub report.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Not Me.ID Is Nothing Then

    If Not Me.SubReport.Report.HasData Then

    Cancel = True

    End If

    End If

    End Sub

    For inserting such report to a form, I suggest you change record source of the main report. You could use right join to combine the table and query to a new query so that the main report record will only contains rows which is not blank in sub report.

    Best Regards,

    Terry

    Tuesday, July 25, 2017 8:50 AM
  • Hi FK_1234,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    • Marked as answer by FK_1234 Thursday, August 3, 2017 2:06 PM
    Thursday, August 3, 2017 8:46 AM