The Importance of VBA’s On Error With Reports, and Run-Time Error 2427

So I’m happily cruising along testing the functionality of my system for a presentation to the board next week, and *BOOM!* a report crashes. It was working fine recently, what the [blank] happened?!

I got the oh so informative VB dialog box saying:

Run-time error '2427'
You entered an expression that has no value.

The Help button was, of course, no help at all. Searching online didn’t help much either. The report is fairly straightforward: dump a table, but it has one serious twist: conditional formatting. I’m calculating if an evaluation is overdue in the view that provides the data, and if it is, I draw a box around the date of the previous eval and the date it is due. The box also goes up if the initial eval is blank.

Well, actually, it’s the reverse: the code in the _Format actually turns on all the boxes and turns them off if they’re current, but you get the gist. I’m not sure why I did it like that, I really should reevaluate that as I don’t like reversed logic.

Anyway, here’s the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me!boxIEP.Visible = True     'the box around the fields

    If Me!IEPLate < 1 Then Me!boxIEP.Visible = False
End Sub

This system is sort of a student tracking system for a district (the ‘sort of’ is important: it’s a complicated explanation and very complicated system).  I remembered that I’d been playing with the data, and I’d created a school with perfect data: every record validated, every eval was current.

And that was the problem: technically there was no data, and that’s what the error 2427 was trying to tell me.

The solution was in two parts.  First, adding an On Error Resume Next to the top of the code.  The next was adding a MsgBox to the On No Data of the report as follows:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Your report is blank because all of your student" _
       & " evaluations are current." _
       & "  Press the <Escape> key to close the form."
End Sub

Staff will still see a blank report, but at least they get an explanation that it is a good thing.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s