Handling Zero Results in a VBScript-Powered Report

If experience has taught me one thing about programming, it is this: expect the unexpected. If you write a report that asks for a number, expect the user to type in a word. If you are looking for a text string, expect someone to leave it empty. Today I'm going to tell you how to handle the unexpected situation of having a query return no results.

In a regular ActiveReports design, this is no big deal. The report just returns empty space where the data would be. However, if you have written VBScript to filter or manipulate the data in any way, then the result of a null set of query results is usually an ugly error message to the user about BOF or EOF, something the average user will blink at and then click OK with no real understanding as to what went wrong.

Luckily, adding a routine to handle a null-resultset situation is pretty easy. The trick is to use the RecordCount property of the ADO object to determine if there are results or not. If there are no results, you can tell the routine to skip all the data filtering and manipulation code and just run the "plain" report, which will still be blank because of a lack of data. As added information, you can pop up a message box to explain why the report will be blank.

Because most data manipulation is done before the report starts rendering, you will probably need to add this code only to the ActiveReports Document object and the OnReportStart event. However, if you are doing some further data manipulation in a later section of the report layout too, then add it as needed to each script in the report design.

Here is the sample VBScript code for a Shelby v5 Report design. The ADO object is called DataSource_Shelby_ (Yes the final underscore is part of the name. Why? I don't know. It's a mystery).

Sub onReportStart

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

{insert your data filtering or manipulation code here}

Else

MsgBox("There are no results to use for this report. Please check the query and try again after the query returns results.")

End If

End Sub

Once this code is in place, even when changes in the data or changes in the query mean that no results are available, the user will not be presented with a geek-speak message, only a warning and a blank report.