Error Handler . .

[Evagrius]

Hi,

I have a command button on a form that filters and opens a report. In the event that the report is empty, I have this,

MsgBox "There are no records that match the " & _
       "selected criteria." & vbLf & vbLf & _
       "Please try again", vbInformation, "GSA"
Cancel = True

The problem is the command button still trys to open the report with the filtered criteria. What is the best way around this please? Thank you!

 

[pbaldy]

I would typically handle that using the No Data event of the report. It would help to see the following code, but typically I'd expect something like:

GoTo ExitHandler

if you have error handling, or simply

Exit Sub

if you don't.

 

[Evagrius]

Hi pbadly,

Thank you for your help. I have the code in the NoData Event of the report.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records that match the " & _
       "selected criteria." & vbLf & vbLf & _
       "Please try again", vbInformation, "ABC"
Cancel = True
End Sub

This is the filter part in the cmd click event. By the way, I used your website as a guide.

DoCmd.OpenReport "SomeReport", acViewReport, , "[Year] IN(" & strWhere & ")" & _
"And [Number] IN(" & strWhereB & ")" & "And [budget] IN(" & StrWhereC & ")" & _
"And [Expense] IN(" & strWhereD & ")"

I get error 2501 - open report has been cancelled . . .Thank you!

 

[pbaldy]

Ah; one caveat to using the No Data event is you have to trap for error 2501 in the code calling the report. My error trap example should help:

http://www.baldyweb.com/ErrorTrap.htm

 

[pbaldy]

I should add that my example has the message box in the error trap. It can be in either place; it doesn't matter which. You can leave it where it is and don't include it in the error trap.

 

[Evagrius]

Hi pBaldy,

If I understand correctly, I should place your code in the cmd module that opens the report. So what I did is I kept the NoData event code in the report, and placed this in the module that opens the report. Do you think this is efficient?

ErrorHandler:
  Select Case Err 
    Case 2501      
    Exit Sub
 Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
 End Select

I exit the sub since I already have a msgbox in the NoData event of the report. Thank you for any advice.

 

[pbaldy]

Presuming you also added the

On Error GoTo ErrorHandler

line, that looks okay. Typically instead of Exit Sub you'd have GoTo Exithandler, but if there's no cleanup being done there it really doesn't matter.

 

[Evagrius]

Yup - I added the Goto

Thank you for the help and for your website as well - I have used it multiple times!

 

[pbaldy]

No problemo. I'm glad the website helped you out!

 

[HartJF]

So, Paul, I've reviewed your responses to this post and your error-trapping routine, and found, with great anticipation, your report-filtering example. I'd like to merge the concepts.

I have a set of weekly reports dispatched via Automation. I have a broad query upon which the reports are based and a filter for each specific report. Occasionally, the filter returns no records. Rather than cancelling the report, I want the report to display a message like "No records were reported this week" so the recipients know the report was run. I tried to use the NoData event to place this message in the report footer, but, since that event occurs after the Format event, an error was raised. Currently, I am sending the report with "#Error" sprinkled liberally throughout. How unprofessional.

Must I re-form the system to use a parameter query as the record source and, if .RecordCount = 0, hide the detail section and push the message to the footer of the not-yet-opened report? Will I then have to make visible the detail and reset the record count footer message on every report that returns records? Isn't this inelegant and, more importantly, inefficient? Does a better method exist?

Thanks for your wisdom!

 

[pbaldy]

In a brief test, this appears to work. Does it do what you want?

Option Compare Database
Option Explicit

Dim strNoData                 As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If Len(strNoData) > 0 Then
    Me.Detail.Visible = False
  Else
    Me.Detail.Visible = True
  End If
End Sub

Private Sub Report_NoData(Cancel As Integer)
  strNoData = "no data"
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
  Me.Text32 = strNoData
End Sub

 

[HartJF]

It worked beautifully! I went a step beyond your solution: I placed a label with the "No data" message in the report footer and set its .Visible property to False. In the NoData event procedure, I set the .Visible property of the .GroupHeader(0), .Detail, and .GroupFooter(0) sections and the .txtFooter to False, and set .lblNoData.Visible to True. Problem solved!

In prior attempts, I must have incorrectly qualified my references. I continue on the never-ending learning curve.

Thanks for your guidance!

 

[pbaldy]

Glad it worked. I didn't even test setting those from the no data event, since you said it didn't work (too lazy).