RE: Report No Data

[jefrat72]

RE: Report No Data

Thanks for the resonces from the original post however I have already done what was suggested. Let me explain again. I created a form called frmDateRange to search a date range specified by the user and display this chosen range on a report (rptDateRange). All the user has to do is enter the beginning and ending dates and click on the Ok button. Behind the Ok button is this code:

Private Sub cmdOk_Click()

If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
MsgBox "Both dates are required for this search!", vbInformation

Else
DoCmd.OpenReport "rptDateRange", acViewPreview, , _
"[RequestDate] Between #" & Format(Me.txtStartDate, "mm\/dd\/yyyy") _
& "# And #" & Format(Me.txtEndDate, "mm\/dd\/yyyy") & "#"
DoCmd.close acForm, Me.Name

End If

End Sub

This will open the report just fine. However if there is no data to display I would like a message box to appear and tell the user there is no data to show and then close the report. I tried this by going to the Reports No_Data and entering this code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Records found for this Date Range." & vbNewLine & _
"Report will now close. Please try another.", vbInformation
Cancel = True
End Sub

When I do this I get this Error:

Run-time error '2501':
The OpenReport action was canceled.
You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that askes if you want to save the changes you made to the form.

Then it highlights the Else: code in my frmDateRange. I understand what the error is saying, but I don't know how to get around it. Thanks in advance.

 

[AccessUser]

I get this all the time. You need to trap this error in cmdOK_Click. Try this.

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
MsgBox "Both dates are required for this search!", vbInformation

Else
DoCmd.OpenReport "rptDateRange", acViewPreview, , _
"[RequestDate] Between #" & Format(Me.txtStartDate, "mm\/dd\/yyyy") _
& "# And #" & Format(Me.txtEndDate, "mm\/dd\/yyyy") & "#"
DoCmd.close acForm, Me.Name

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & Err.Description
End Select

Resume Exit_cmdOk_Click

End Sub

 

[MarionD]

Hi there,

I do more or less the same , perhaps just a bit easier.


Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
if err = 2501 then exit sub
MsgBox Err.Description
Resume Exit_cmdOk_Click
End Sub

This exits the sub with no further msg if the error is 2501 (which is what I want to happen) and continues with the error description if the error is not 2501

Works fine for me.


[This message has been edited by MarionD (edited 04-03-2001).]

 

[Rich]

Private Sub cmdOk_Click()
On Error Resume Next

If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
MsgBox "Both dates are required for this search!", vbInformation etc.
HTH

 

[MarionD]

Hi Rich,

I think Jefrats problem is that the dates are there - there just isn't any data to print for the relevant dates.

Marion

 

[Fornatian]

Put ON ERROR RESUME NEXT before the report opens. Essentially you're in a vicious circle where you ask the report to open then tell it not to.

Private Sub cmdOk_Click()

If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
MsgBox "Both dates are required for this search!", vbInformation

Else
****On error resume next****
DoCmd.OpenReport "rptDateRange", acViewPreview, , _
"[RequestDate] Between #" & Format(Me.txtStartDate, "mm\/dd\/yyyy") _
& "# And #" & Format(Me.txtEndDate, "mm\/dd\/yyyy") & "#"
DoCmd.close acForm, Me.Name
End If
End Sub

 

[jefrat72]

Thanks everyone, works fine.

 

[Rich]

OnError Resume Next actually relates to Access generated errors and won't interupt jefrat's custom validation, the advantage of putting it at the start of the procedure is that if jefrat had included a vbCancel, Resume Next would have prevented that you cancelled the etc. as well as the report's no data event. Two birds for one stone.
I actually use Marion's method but declare the const. however jefrat didn't post any error handling so Resume Next is just fine for him.
HTH

 

[Fornatian]

So do I and find it is much better to test for appropriate info before taking dependent action, saves on processing times, resources...but a quick and dirty answer is what I've given. The only problem I can see is if there are other errors because these too will be ignored.