Answer Script (Settings/Views tab)
Answer scripts are used to modify the SQL which is produced by the ELF translator, or to detect conditions based on that SQL and re-route the system toward custom warning/error messages, or custom-designed queries or SQL. Since Answer scripts are usually used in conjunction with Question scripts, it's recommended that you read carefully through the Question script topic before beginning this section.
Answer scripts are registered in the same way as Question scripts, by adding a script into the script library accessible from the Scripts tab of Settings. The name of this script is typed into the Script Name column, while a function with the same name must appear somewhere within the script itself. Once this script is registered in the script library, it can be made active by first entering its name in the textbox below Answer (right of "Triggers Script:") on the Views tab, then checking off the Answer script checkbox.
The Question scripts topic discussed several ways of using these scripts to select Views at the time users type in their questions, or to change the text of questions before having Access ELF interpret them. This tutorial continues with the use of Question and Answer scripts in combination.
Another possible use of Question scripts is to intercept certain questions which are considered improper, for one reason or another. Of course, there's a simpler method for this; which is to catch forbidden words or phrases by listing them (with a preceding exclamation point) in the Phrases list. For instance, to short-circuit any question mentioning "sex"
When I Type: !sex
I Really Mean: Questions about gender are not supported by this system
But what if we wanted to permit questions about the sex of our customers (assuming we had that information) but deny similar requests concerning our employees. In that case, we'd need to add some code to a Question script.
if contains("sex") and contains("employee") then
PrivateErrorCode = 100
end if
Note that, in this example, the contains() function must be included or referenced just as it was in the related Question script examples.
Now, by itself, setting this variable (PrivateErrorCode) does nothing. In fact, the value 100 means nothing in particular, either. This variable is simply a means for you to store an integer which you can consult later to decide how to behave when getting ready to display an answer to the user. By "how to behave", we mean what actions will be taken by any Answer script which is registered for the View.
In this case, we could write an Answer script (let's call it LastMinuteChanges) which checks the PrivateErrorCode variable, and if it's 100, carries out a plan for preventing any SQL translations from being used as the basis of a response. Notice that here the -8 is not an arbitrary value, but a built-in code value. It means that Access ELF should display an error message box using the values embedded in the ErrorMessage variable.
function LastMinuteChanges
if PrivateErrorCode=100 then
QueryResult=-8
ErrorMessage="Questions about gender are not supported by this system;For the privacy of our employees"
end if
end function
The ErrorMessage string is comprised of two parts; the message itself, and an optional header following the semi-colon. If the optional part is not included, the default error header shown on the Advanced tab will be used (or "We regret to say:" if no custom header is provided).
It's also possible to instruct Access ELF to display a list of values in datasheet format, using the values specified in the ErrorMessage string. For instance, we might replace the above ErrorMessage definition with:
ErrorMessage ="({Human resources;To contact this department:};{555-1234;please call this number}); ({Corporate headquarters};{555-1000});"
| To contact this department: | please call this number |
| Corporate headquarters: | 555-1000 |
| Human resources: | 555-1234 |
Details about the syntax of these compound ErrorMessage strings are given in the VB ELF Compatibility section.
Rather than building a recordset response "brick-by-brick" in this fashion, we might prefer to run a query which is already prepared in our database application. This query could even refer to tables which aren't included in the natural language View. Here's an example of how LastMinuteChanges could be written to trigger a prewritten query called "Personal Info", whenever the "sex" and "employee" combination was detected.
function LastMinuteChanges
if PrivateErrorCode=100 then
QueryResult=1
SQL="Personal Info"
end if
end function
A QueryResult value greater than zero indicates a successful translation; setting it to 1 manually like this means that the system will continue as if the translation were successful (this guarantees that the Personal Info query will run regardless of whether the translator made sense of the actual question containing the words "sex" and "employee"). If the translation had succeeded, the QueryResult variable would already be a positive integer; if it had failed, the QueryResult variable would hold a zero or a negative number corresponding to these special error conditions:
| QueryResult | Meaning |
|---|---|
| -4 | unknown word |
| -5 | DLL was busy |
| -7 | activated/deactivated form |
| -8 | custom error message |
| -10 | unprocessable words |
| 666 | user pressed cancel button |
Usually, all you need to know is whether a translation was successful or unsuccessful to decide how to react. In fact, if you might not even care which, if you've already decided how this question will be handled at the point you detect the trigger conditions. If that's the case, you can speed up processing by resetting the question to something simple like "Show everything". (You'd do this in the Question script, at the same time you set the PrivateErrorCode trigger.) This would guarantee that no time was wasted trying to interpret something like "if the sex of our employees is on file, please give me that information".
We haven't exhausted the possibilities of Question/Answer script combinations yet. For instance, what if the pre-existing query we want to run in response to some trigger condition has one or more parameters? We'll need a way of passing these parameters into the query. And what if -- rather than setting the SQL variable to the name of a pre-existing query -- we write code which creates custom SQL on-the-spot, then assigns it into the SQL variable?
Let's take the second case first; it's simpler. How do we set the SQL directly?
function LastMinuteChanges
if PrivateErrorCode=100 then
QueryResult=1
SQL="SELECT LastName, Title, HomePhone FROM Employees;"
end if
end function
When the value assigned to the SQL variable is the name of a parameterized query, Access ELF expects a package consisting of each required parameter and its associated value. This "package" is the QueryParms variable, which is filled-in as in this example:
Last Updated: March, 2002
function LastMinuteChanges
if PrivateErrorCode=200 then
QueryResult=1
SQL="Employee Sales By Country"
QueryParms="[Beginning Date];#1/1/94#;[Ending Date];#1/1/99#"
end if
end function