how to check if a textbox in the subreport has data or not

[associates]

Hi,

I have got a question. I have a report that has a subreport. And they are linked by job_ID.

I know how to check if there is no data on the report by calling sub report_NoData (something like that).

However, because i have a subreport that has a textbox, say text68, i don't know how to check if the data. Hence, it's giving me error message saying no data.

I guess, what i want is either being able to show the report without the subreport because the subreport has no data and just show "No data" for the subreport or not showing the report at all.

Here is my code:

Private Sub CM_JobInvoice_Click()
On Error Resume Next

Dim stDocName As String

If Not IsNull(Me.CB_Industry) And Me.CB_Industry <> "*" Then
If Not IsNull(Me.CB_Client) And Me.CB_Client <> "*" Then
If Not IsNull(Me.CB_Job) And Me.CB_Job <> "*" Then
stDocName = "JobInvoice Work Hours"
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "Please select a job number"
End If
Else
MsgBox "Please select a job number"
End If
Else
MsgBox "Please select a job number"
End If

If Err = 2501 Then Err.Clear
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found! Closing report."
Cancel = True
End Sub

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

'MsgBox "text51 is" + CStr(Me.[Invoice Summary1].Report.Text68)
'MsgBox "Job name is " + Me.[Invoice Summary1].Report.Job_Name
If Not IsNull(Me.[Invoice Summary1].Report.Text68) And Me.[Invoice Summary1].Report.Text68 <> "" Then
Me.Text51 = Me.[Invoice Summary1].Report.Text68
Me.Text52 = Val(Me.Text50) - Val(Me.Text51)
End If

End Sub

Thank you in advance and look forward to hearing from you.

 

[Remou]

Hi
I do not quite get what you mean. Where is the data in text68 coming from?

 

[associates]

Hi, Remou

(Me.[Invoice Summary1].Report.Text68)

[Invoice Summary1] is the subreport that lives in the main report.

Text68 is a textbox i created in the subreport [Invoice Summary1].

So i guess what i'm trying to do here is to be able to get the value from the subreport and do a bit of calculation say profit and display it on the main report.

In another words, on the main report, i have all details of how much money we spent on the project. On the subreport, i have details of how much we bill our customer.

And then, at the bottom of the main report, i have a total profit.

So, when i print out the report, we can actually see everything about what's going on within a particular project.

But the trouble here is that on the main report, there is data i.e. shows how much we spent but no data from the subreport that shows how much we bill.

This is where i got errors when trying to open up a report. it works fine if there are data on the main report as well as the subreport. But it causes error when there is no data found in the subreport.

So, my question is either how do i tell access not to open up report that has no data on the subreport at all and prompt "No data" eventhough there is data on the main report OR to show just the main report that has data and say on the bottom of the page that "No data found in the subreport".

Sorry, not a good at explaining .... I hope this would help you understand it.

Thank you in advance.

 

[Remou]

Ok. Still guessing to a certain extent. Say Text68 (it's best to use real names, txtTotal for example) contains:
[tt]=Sum([InvoiceAmount])[/tt]
And you have a control in your main report that references Text68, you can say in that control:
[tt]=IIf([Invoice Summary1].Report.[HasData],[Invoice Summary1].Report.[text68],"N/A")[/tt]

Make sure that [Invoice Summary1] is the name of the subreport control, not just the name of the subreport.

(By the way, it would be possible to considerably shorten the block of code in your first post.)