Close report on No data.....Complication! (Part2)

[Franky G]

Mike, you already helped me on this one; I'm trying to close a report (or not even run it) if there is no data. The problem is that in my Header section there is always the From and Until fields which prevent the On No data function from being used.

Here's what you posted last time;

Private Sub cmdPrintReport_Click()
Dim Db As Database
Dim rstTmp As Recordset
Dim strSQl As String

Set Db = CurrentDb()

strSQl = "[NB the same as the recordsource of your report]"
Set rstTmp = Db.OpenRecordset(SqlStr, dbOpenSnapshot)

'get an accurate recordcount:
On Error Resume Next
rstTmp.MoveLast
rstTmp.MoveFirst
On Error GoTo 0

If rstTmp.RecordCount > 0 Then
DoCmd.OpenReport "YourReportName", acViewNormal
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If

Set Db = Nothing

End Sub

If the recordsource for your report is a saved query (as opposed to SQL pasted directly into the Recordsource property), you may be able to simplify it right down to:

Private Sub cmdPrintReport_Click()

If DCount("*", "[YourQueryName]") > 0 Then
DoCmd.OpenReport "YourReportName", acViewNormal
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If

End Sub

(I'm assuming for both of these examples that you are running the report from a command button)
_________

To further complicate matters I only want the report to run if BOTH From and Until dates have been selected. Here's what I used;

Private Sub SDG_Report_1_Click()
If IsNull(Me.From_Date) Or IsNull(Me.Until_Date) Then
MsgBox "Both dates are required"
Else
DoCmd.RunMacro ("Run SDG Report 1 - MASTER")
End If
End Sub
_________

How would I incorporate this into your code above?

Thanks for any further help,

Regards

Franky.

 

[llkhoutx]

Get a recordcount on the recordsource for the report, if it's 0, trigger you no data message.

 

[Atomic Shrimp]

llkhoutx:

Read the question again, we HAD ACTUALLY GOT THAT FAR!

Franky:

I *think* you only need something like:

Private Sub SDG_Report_1_Click()
If IsNull(Me.From_Date) Or IsNull(Me.Until_Date) Then
MsgBox "Both dates are required"
Else
If DCount("*", "[YourQueryName]") > 0 Then
DoCmd.RunMacro ("Run SDG Report 1 - MASTER")
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If
End If
End Sub


NB: I've typed this in Notepad, so it might need a tiny bit of debugging.

Effectively, you're just saying IF the boxes are populated AND the report has data THEN print it, it could be simplified more than the above, to a single IF/THEN/AND/ELSE statement, I suspect.

HTH

Mike

 

[Franky G]

Ok Mike, I'll try that when I'm back at the office.

regards

Frank

 

[Franky G]

Hi Mike, nearly there...just one more thing!

My reports are based on multiple queries, so that the report should close if all the underlying queries return no data. I'm trying to figure out how to do this with the code you provided below,

Private Sub SDG_Report_1_Click()
If IsNull(Me.From_Date) Or IsNull(Me.Until_Date) Then
MsgBox "Both dates are required"
Else
If DCount("*", "[YourQueryName]") > 0
***and if DCount ("*", "[MyOtherQuery]") > 0***
Then
DoCmd.RunMacro ("Run SDG Report 1 - MASTER")
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If
End If
End Sub

Whats the correct syntax for the part enclosed in *** ? I hope it's clear enough what I'm trying to do!

thx

Franky

 

[Atomic Shrimp]

Hi frank

When you're using [Condition] AND [condition], you don't need another IF

It should be something like:

If DCount("*", "[YourQueryName]") > 0 and DCount ("*", "[MyOtherQuery]") > 0

I find it sometimes helps to put the individual conditions in brackets, so:

If (DCount("*", "[YourQueryName]") > 0) and (DCount ("*", "[MyOtherQuery]") > 0)

so, basically , you can do:

IF (condition) AND (condition) AND (condition) THEN
[Do something]
ELSE
[Do something else]
END IF

you can make the conditions as complex as you like, but if you find the need to mix AND/OR in the same test, you'll need to be very careful with the bracketing:

False AND False OR True = True
(False AND False) OR True = True
False AND (False OR True) = False

HTH

Mike

[This message has been edited by Mike Gurman (edited 03-12-2001).]

 

[Franky G]

thx, I was in the process of trying different combinations. It's working the way I want now!

Thanks again for your help,

Franky