Cancel Report on No Data in subreport. How?

[Natha2]

Hi,

Can anyone tell me how to cancel a report containing 2 subreports when BOTH subreports have no data?
(Both subreports are linked to the main report's data, but exist independently one from each other.)

Thx,
Natha

 

[HitechUser]

Try looking at the NoData event.

The NoData event occurs after Microsoft Access formats a report for printing that has no data (the report is bound to an empty recordset), but before the report is printed. You can use this event to cancel printing of a blank report.

Private Sub Report_NoData(Cancel As Integer)

Cancel The setting determines whether to print the report. Setting the Cancel argument to True (–1) prevents the report from printing. You can also use the CancelEvent method of the DoCmd object to cancel printing the report.

Also look at HasData..

I hope this helps..

 

[dhookom]

Natha,
In most cases, you can create a totals query similar to the record source of the subreport. Group the totals query by the field used to link the main and subreports and count the records. This should result in a single record for each record in the main report.

You can then add the totals query to the main report's record source possibly with an outer join. You can use the CountOfRecordsInSubreport column to determine whether or not the main report should be canceled.

You would need to duplicate your efforts for the second subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cghoga]

Use dcount to determine if both recordsets are > 0. If so, proceed, otherwise Cancel.

Temp is my query or table.

Dim cnt

cnt = DCount("*", "Temp")

If cnt > 0 Then

DoCmd.OpenReport......

Else

'Cancel

End If

Hope this helps.