User-defined function error handling when in queries

[chris klein]

How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.

It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time. Are there guidelines for this?

 

[jdraw]

What do you think should happen when/ if there is an unacceptable input ?

 

[chris klein]

If possible, display the query results but with some sort of error code in the field that contains the function. I tried adding a line that simply assigns Null to the function result, before the Exit Function, but that does not prevent ending up in the debugger.

 

[chris klein]

Here is an example of the code:
...
Select Case htunits
Case Is = "jou"
Htjg = ht
Case Is = "btu"
Htjg = 2.326 * ht
Case Is = "cal"
Htjg = 4.1876 * ht
Case Else
Response = MsgBox("Missing or improper htunits (must be jou or btu or cal)", 48)
ClresSilAdEx = Null
Exit Function
End Select
ClresSilAdEx = cl * ((HvRes - HlRes) * (HvSmp - Htjg)) / ((HvRes - Htjg) * (HvSmp - HlSmp))

Else
ClresSilAdEx = Null
End If
End Function

When this is used in the Immediate Window, it displays the Message, and on OK it then assigns Null to the result and exits. When used in a Query, it displays the Message and goes to the de-bugger. When I exit the de-bugger it tries to process the next record of the Query input and the Message gets displayed again, etc....

 

[Galaxiom]

Depends on where the error happens.

If it is an error because it is receiving an incompatible input (eg a Null when expecting anything other than a Variant) then catch that before the function is applied.

If the function has an internal error have it return a value that reflects this.

 

[chris klein]

I think this is the second situation (internal error). In the example code, the function is looking for "jou", "btu" or "cal" and if none of these are found, it assigns Null and Exit Function. It is beginning to look to me that the Acess query processing system is not happy with Exit Function, and instead of that I should jump to the end of the function and allow a normal End Function.

 

[Galaxiom]

What is the full declaration line of the function?

You will get an error if you have something like this because a Null can only be returned as a Variant:

Public Function ClresSilAdEx(htunits As String, ht As Double) As Double

 

[chris klein]

Simply
Public Function SilAdEx(silphase, sio2, sepcond, sepunits, ht, htunits, tc_jou_qsep_qres)
with no As statement

 

[Galaxiom]

When you hit End, the VBA terminates but the next record in the query calls it again.

BTW The other thing that happens when you hit End is ALL variables in ALL the VBA in the WHOLE Project are reset, potentially compounding the problem.

I suggest you show us the whole function and the values passed when it fails. If it is not displaying the error message then the fault is elswhere

 

[chris klein]

Unfortunately, the function is quite long and contains proprietary code that I can't distribute. However, when it should, the error message does show up and then Null is assigned to the function. All that is OK. My real problem is, as you say, "When you hit End, the VBA terminates but the next record in the query calls it again." So then I need to hit OK on the Error Message for each record (many times) to get to the end of the query.

Is there any way to make the query simply abort after the error message appears the first time?