Hello,
I have a report that is filtered by a form. I am getting error 2501 when there are no results to be displayed. I would like the report to not open if there are no results to display. I tried adding error handling but it is not helping. I also added a msgbox to the "No data" event in the report and that prompts, followed by the 2501 error. Here is my code:
Private Sub cmdGo_Click()
On Error GoTo Err_Handler
Dim strWhere
Dim strDateField
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strDateField = "[Publication Date]"
If Me.txtenddate > #1/1/2000# Then
If Me.txtstartdate > Me.txtenddate Then
MsgBox ("You must enter a start date that is before the end date")
Exit Sub
End If
End If
If IsDate(Me.txtstartdate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtstartdate, strcJetDate) & ")"
End If
If IsDate(Me.txtenddate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtenddate + 1, strcJetDate) & ")"
End If
If Me!cboStatus = " ALL" Then
DoCmd.OpenReport "rptPipeline_byState", acViewPreview, , strWhere
DoCmd.Maximize
Else
DoCmd.OpenReport "rptPipeline_byState", acViewPreview, , "St = '" & Forms!frmDialog_for_rptPipeline_byState!cboStatus. Value & "' AND " & strWhere
DoCmd.Maximize
End If
Exit Sub
Err_Handler:
Select Case Err
Case 2501 ' Report canceled
Case Else ' All other errors
MsgBox Err.Description, vbExclamation
End Select
End Sub
And for the report:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records for this filter"
Err.Clear
End Sub
I have tested the "Cancel = True/False" function in the NoData report but have not had any luck. Any help will be greatly appreciated!