Enumerating Options using VBScript

ActiveReports is limited in the ways that a user can input parameters at run-time. At least it is limited in the way it has been implemented for Shelby v.5's ShelbyQUERY application. In fact the only way to capture a parameter at run-time is to use VBScript's InputBox() function. This is serviceable for many contexts, but it can be a problem when the parameter is a long text string, such as the name of an Event from the Registrations application or a company name from the General Ledger. In these cases the user must remember the exact wording of the text and then type it in without any mistakes in order to match the value in the query results exactly.

Error checking can alert the user that the value has been typed incorrectly, but it would be best to help the user along by prompting him or her with what the acceptable values are. In fact it would be ideal to give the user an enumerated list of options, so the user only has to choose the number of the desired value and enter one number instead of typing a long text string. The script below will do just that.

This script should be associated with the ActiveReports Document object and the OnReportStart event. That way it will pop up at run-time and provide a filter for the contents of the report.

After you paste the script into your VBScript editor window, be sure to update the value of the strColumnName variable so that it corresponds to the desired column in your actual query results. Also change the strPromptMessage and strPromptTitle variables so that the InputBox() prompt is worded appropriately for your report.

Sub OnReportStart

Set oDict = CreateObject("Scripting.Dictionary")

Set rsQuery = rpt.DataSource_Shelby_.RecordSet

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

intCounter = 1

strCurrItem = ""

strTestItem = ""

strColumnName = "QueryColumnName"

strPromptMessage = "Enter the prompt message here."

strPromptTitle = "Enter the Prompt Title here"

Do

strCurrItem = rsQuery(strColumnName)

If strCurrItem <> strTestItem Then

oDict.Add intCounter, strCurrItem

strList = strList & vbCR & intCounter & " - " & strCurrItem

strTestItem = strCurrItem

intCounter = intCounter + 1

End If

rsQuery.MoveNext

Loop Until rsQuery.EOF

Do

intChoice = InputBox(strPromptMessage & vbCr & strList, strPromptTitle)

If intChoice <> "" and IsNumeric(intChoice) Then

strItemChoice = oDict.Item(CInt(intChoice))

rsQuery.Filter = strColumnName & " = '" & strItemChoice & "'"

If rsQuery.RecordCount > 0 Then Exit Do Else rsQuery.Filter = ""

End If

If intChoice = "" Then Exit Do

Loop

rsQuery.MoveFirst

Else

MsgBox("There are no results to use for this report. Please check the query and try again after you have a set of results.")

End If

End Sub

With this code in your ActiveReports document, your end-user will have a much easier time filtering the report without have to double-check the spelling of a long text value and will avoid frustration of locating a tiny typo in the string.