Report opense even if there is no data

[Rmcta]

It says "Sorry! No records were found with the result you have selected" but it then opens an empty report anyway. How can I fix that?

This is my code:


Public Function DisplayResult(VarResultVal) As String
'converting Option group values from integer to string value
On Error GoTo Err_Function
Const conErrorNullField = 3021

Select Case VarResultVal
Case Is = 1
DisplayResult = "No record"
Case Is = 2
DisplayResult = "No further action"
Case Is = 3
DisplayResult = "Final set not accepted"
Case Is = 4
DisplayResult = "Response required "

End Select

Err_Function:
If Err.Number = conErrorNullField Then
MsgBox ("Sorry! No records were found with the result you have selected&quot
End If
End Function

-------------------------
Private Sub cmdViewReport_Click()

Dim strName As String
Dim strDocName As String

strName = Me.cboMgr
strDocName = "rptMgr"
dteValFrom = Me.txtFrom
dteValTo = Me.txtTo

If strName = "All Mgr" Then
DoCmd.OpenReport strDocName, acPreview, , "[RecordCreationDate] Between (#" & dteValFrom & "#) and (#" & dteValTo & "#)"

Else

DoCmd.OpenReport strDocName, acPreview, , "Mgr= '" & strName & " ' and [RecordCreationDate] Between (#" & dteValFrom & "#) and (#" & dteValTo & "#)"

End If
End Sub

 

[CosmoKramer]

In the On No Data event of your report, put code like this:

MsgBox ("Sorry! No records were found with the result you have selected")
Cancel = True

This will cancel the generation of the report when there is no data......

 

[Rmcta]

I inserted the Cancel=True (see below) but it still opens the empty report.

Err_Function:
If Err.Number = conErrorNullField Then
MsgBox ("No Record Found&quot
Cancel = True
End If
End Function

 

[CosmoKramer]

Make sure the code is in the On No Data event. The procedure should look something like this:

Private Sub Report_NoData(Cancel As Integer)
   MsgBox ("Sorry! No records were found with the result you have selected")
   Cancel = True
End Sub

 

[Rmcta]

Would that work even though I am using the public function?

Do I insert the On NO Data Event in my Private Sub cmdViewReport_Click() ?

 

[CosmoKramer]

If that function's only purpose is to determine if the report has any data, then you don't need it.

In your report's design view, under Properties, find the On No Data event and copy & paste the above code. The whole purpose of this event to cancel printing of a blank report.

 

[Rmcta]

Thank you