Newbee Question

[danbl]

I have a report which I want to print when its preceeding report has no data.

I used the following code but nothing happens. Can someone point this newbee in the right direction???

Option Compare Database
Option Explicit

Private Sub Report_NoData(Cancel As Integer)
Dim Nodata1 As Access.Report
Dim strReportName As String
MsgBox "There is no data for this report."
Cancel = -1
strReportName = "Nodata1"
DoCmd.PrintOut.Nodata1

End Sub

 

[DCrake]

You can do this from a different angle.

Your report will have an underlying recordsource, so do a dcount on the recordsource and respond accordingly

If Nz(DCount("*","ReportSource") ,0) = 0 Then
   DoCmd.OpenReport "RptNoData"
Else
   DoCmd.OpenReport .....
End If

 

[danbl]

Okay I will try this but first a another question?

In the If statement what does "Nz" refer to???

How does this generate the printed report? It seems that this would only open the report.

 

[DCrake]

Nz() works on the prinicple that if there is nothing to count it will not return an error.

Also does not openning the report generate it?

 

[danbl]

Here is what I entered and I get a runtime error saying "You cancelled the previous operation".

the If statement line is highlighted in the debug window.

Option Compare Database
Option Explicit
Private Sub Report_NoData(Cancel As Integer)
If Nz(DCount("*", "q_Temperature_Monitoring Query 2"), 0) = 0 Then DoCmd.OpenReport "NoData1"
Else
DoCmd.OpenReport "r_Summary_Temperature_Preop_Monitoring Analysis Report"
End If
End Sub

 

[vbaInet]

Just wondering, are you recounting the number of records in the report's recordsource? If that's the case, then you don't need to because the No Data event has already done that, hence, the reason why the event fires.

 

[danbl]

I am using the "On No Data" event in the report properties.

Can you tell me what the code should be in order to generate the second report when there is no data in the first?

 

[DCrake]

On the form where you have a buton that you click to generate the report is where you need to place the dcount test. Take any code you have out of the report. as the report will only be run if there is anything to print.

 

[vbaInet]

Yes, it looks like you're trying to reopen that same report so maybe also try removing the second OpenReport line.

 

[danbl]

Let me backup a minute ... the first report works fine when there is qualifing data. If there is no data all the calculated fields show #Error. (Assum this is due to division by 0.) Data is based on "Yes/No" or -1,0. The second report is has the same headers but has a lable box which states there is no data to report, which is why I was using the On NO Data event to trigger the second report. There are a number of other reports which are generated by a macro which is another reason I am attempting to do as mentioned.

There is no button on the report or any of the other reports.

 

[vbaInet]

Instead of using a second report, create a textbox on that report that will display the no data message, or simply close the report and display an msgbox (in which case you would need to trap for the 2501 error).

If you're going to go for the textbox option then put this in the Control Source:

=IIF(HasData, Null, "The report has no data")

 

[DCrake]

Why are you not performing calculation in your underlying recordsourcev ie Query?

Also you can use IIF() to test for null or zero values prior to the calculation.

 

[danbl]

Okay the text box worked however all the calculated fields in the Date Footer still show #ERROR. Is there a way to not display the date footer when there is no data??

 

[vbaInet]

Do one of the following:

* perform the calculation properly using IIF() to avoid the zero division

OR

* use IsError()

OR

* perform the check in the section's On Format event or On Print event

 

[danbl]

As I said the text box works fine and I can display the necessary message.
I could not get the error to go away. I used IIf function but unfortunately there is still an issue which I cant figure out but will keep working at it.

Thanks, I am sure I will be back for more help.

 

[vbaInet]

True the calculation works but to eliminate the error you're better off not performing a calculation if no data exists. Here's an example of what I mean:

=IIF(HasData, PERFORM CALCULATION, Null)

Or try IsError:

=IIF(IsError(THE CALCULATION), Null, PERFORM CALCULATION)

 

[danbl]

Great news the IIF(HasDat .... works, so here is another question??

I have a Text box where the date (month and year) are entered as text. Control Source has the formula =[Enter Report Period]. When there is data, and you enter the month and year, it appears as expected. When there is no data once, and you enter the month and year, again I get #ERROR. I believe this is in someway due to the fact that there is no data. As this is text not a calculation formula is there a way to force that information to be entered even when there is no data??

 

[vbaInet]

Hmm... danbl, are we talking about a form now?

If a textbox has a control source of that nature, i.e. prefixed with "=", then that textbox is read only. I don't see how users are able to enter text into it. Could you elaborate?

 

[danbl]

No this is a report. When you request the report it asks for a start date, end date, and the report period. The text box for the report period is in the Report header. In the report period box you enter say "October 2010". When there is data the report header shows what you enter. However when there is no data you get the #ERROR.

 

[vbaInet]

And you're saying that both IIF(HasData... ) and IIF(IsErr()) doesn't work?