How to not to print a report with its subreport if they have NoData? 2

[surotkin]

Hello everyone,
I have a report with a subreport. They are not linked. Both are bound.

I need to show a message instead of showing an empty report, if both (main and sub) reports have no data.

I use NoData event of main report:

Private Sub Report_NoData(Cancel As Integer)
If Not Me!SubReportControl.Report.HasData Then
MsgBox "No records were found.", vbInformation, msgtitle
Cancel = True
End If
End Sub

I am getting an error:
“You entered an expression that has an invalid reference to the property Form/Report.”

Does anybody know why?

Thanks.
surotkin

 

[THWatson]

surotkin
Try this...

1. In the No Data event for the main report, put

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records were found."
Cancel = True
End Sub

That should take care of situations where there is no data for the report and subreport.

2. If you want something to be printed where the subreport goes, in those situations where the subreport has no data but you still want a main report to show, put a text box that has something such as the following as its control source...
=IIf([YourSubreport].[Report].[HasData]=-1,[YourSubreport].[Report]![TextBoxFromSubreportToShow],"There is no data in the subreport")

Tom

 

[surotkin]

Hi Tom,
thanks for response.

I tried what you suggested. It doesn't work the way I want it.

Because, if you put the following code in the No Data event for the main report, it doesn't show the report at all even though there are data in subreport.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records were found."
Cancel = True
End Sub

I need to show a message instead of showing an empty report, if both (main and sub) reports have no data.

surotkin

 

[THWatson]

surotkin
If the report and subreport are linked, I'm not clear how there can be data in a subreport for which there is no data in the main report.

Wouldn't that be like having data in an Orders subreport but having no Customer in the main report?

Maybe I'm not understanding something in your setup.

Tom

 

[surotkin]

Hi Tom,
You probably did not pay attention to what I wrote in the initial message:

I have a report with a subreport. They are not linked. Both are bound.

The reports are not linked.

Any idea?

surotkin

 

[THWatson]

surotkin
Well, you're right...I missed that little word "not" in your original post, and proceeded on the basis that the report and subreport were linked.

Can you say more about your setup and what you are trying to do? I need to understand more about why you have a report and subreport but they're not related.

Tom

 

[surotkin]

Hi Tom,
yes, sure.
I have two reports combined in one, since it was requested by business analysts. Logically, there should be two reports, but it was requested by a Client as one object.
There is not a lot to describe. However, I want to mention that there is a subreport included in ReportFooter section of the main report.
Whenever both reports are empty I get as a result a page with zeros as totals. In this case I don't want to show the report at all.

Any idea?

surotkin

 

[THWatson]

surotkin
Okay, here's what I did and it worked at my end.

On the Activate event for the main report, I put the following code...

If Me.HasData = 0 And [SubreportName].Report.HasData = 0 Then
MsgBox "There is no data"
DoCmd.Close
End If

If both main report and subreport are empty, you get the message box.

If the main report is empty, but there is something in the subreport, it will show.

See if that does it.

Tom

 

[surotkin]

Tom,
thanks for your response again.

I tried your code under Activate event.
Unfortunatelly, DOCMD.CLOSE doesn't stop execution of the code and next routine (in my case the code under GroupHeader3_Format) gets control of execution. As far as you can predict, the code tries to manipulate with objects that are already closed.

Any idea, if are not tired yet?

surotkin

 

[THWatson]

surotkin
I'm not sure what the processes in GroupHeader3 are, so don't know for sure what to say.

But, since the processes are on the Format event, I'm assuming they have to do with either layout, or the assigning of some value to a text box. In that event, could you add code, to what I offered, that cuts off those things from happening?

Tom

 

[surotkin]

Hi Tom,
I tried to add the code you offered in the Format event. Unfortunatelly, I got the same result - DOCMD.CLOSE doesn't stop code from execution.

The following code is what I have as code for the main report (Including Activate event as you offered):

Option Compare Database   'Use database order for string comparisons
'--------------------------------------------------
Private Sub GroupHeader3_Format(Cancel As Integer, FormatCount As Integer)
    If IsNull([CIType]) Then
    GroupHeader3.Visible = False
    Else
    GroupHeader3.Visible = True
    End If
End Sub

'--------------------------------------------------

Private Sub Report_Activate()
If Me.HasData = 0 And Me.ClaimsPaid_sub.Report.HasData = 0 Then
    MsgBox "No records were found.", vbInformation, msgtitle
    DoCmd.Close
End IfEnd Sub

'--------------------------------------------------

Private Sub Report_Close()
  On Error Resume Next
  If TDC_ShowForms(True) Then
  End If
End Sub

Does it help?

surotkin

 

[THWatson]

surotkin
I notice that in the Activate code you posted you have EndIf and EndSub run together on the same line...but that may just be a typing error.

In a little test sample, I added a Group Header and included the code you have in your Group Header, and things still worked properly.

Is that the full code for the Close event? I'm wondering if that is the block.

What you might do is comment out the code lines in the Close event and see if it works properly then. If that doesn't do it, comment out the lines in the Group Header code and see if that helps.

Tom

 

[surotkin]

Tom,
You are wright, EndIf and EndSub was a typo when I pasted the code (otherwise it would not be compiled).

I tried to comment Close Event code and GroupHeader Format code. Close Event code is not executed as I expected. GroupHeader Format code is executed, but the cause of the error is a routine that calls (opens) the report. It tries to maximize the report after it has opened it. In my case, it tries to maximize the closed report. That's why I am getting the error# 2489 telling that "The object <Report Name> isn't open.

Thanks.
Your code works!
You are the man!

 

[THWatson]

surotkin
Glad you got it working!

Tom