Repost: Cancel Print if Subreport not Visible

[Guest]

I'm sure this is simple but I'm missing it........

Hi

I have a subreport that is set to be invisible if it does not contain any
data. This had to be done because of another report that the sub is used on.

Anyway, how do I tell a main report to show a message box and cancel
printing if this particular subreport is not visible.

The code I have is as follows, but is not working:

Private Sub Report_Activate()
If (Me!rsubPumpsInStock.Visible = False) Then
MsgBox "No pumps in stock, cancelling report printing",
vbExclamation, ""
DoCmd.CancelEvent
End If
End Sub


I'm not sure which report event to use either.....

Thanks
CJ

 

[Guest]

Instead of checking if the sub report is visible, check if it has any data

Private Sub Report_Activate()
If Me!rsubPumpsInStock.Report.HasData = False Then
MsgBox "No pumps in stock, cancelling report printing",
vbExclamation, ""
DoCmd.Close acReport, "ReportName"
End If
End Sub
This code should be on the On Activate event of the Main Report

 

[CJ]

Brilliant, thanks very much.

I had to make one adjustment.....the End If statement had to come out.

Thanks again!

 

[CJ]

Oooops, actually, not quite. If the report does have data, then it just
cancels the print preview.

I have tried adding an ElseIf to make it work but no luck.

Any suggestions?

 

[Guest]

Can you post the code?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

The If has to be there, so I don't understand how did you remove the End If

If there is no if, the report will be always close
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[CJ]

Hi, sorry for the delay.

I solved it with the following code:

If Me!rsubPumpsInStock.Report.HasData = False Then
DoCmd.Close
MsgBox "No Pumps In Stock. Report Preview Cancelled",
vbInformation
Else
DoCmd.Maximize
End If

I just changed the order of the events, making sure that the report closed
first. I'm not sure what happened with the End If thing, I don't remember
anymore.

Thanks for all of your help.

 

[Guest]

I'm happy you got it sorted out.
Good luck with your project
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck