When an sql query makes a call to a vba function and that function raises an error, the error handling code fails to handle the error.
See example below. The call to GetId() in the strSql generates an error when the Set rst = db.OpenRecordset(strSql) is executed.
This error is not handled by the On Error GoTo Err_Test error handler!
Public Function GetId() As Long
Err.Raise 11 'Divide by zero error
End Function
Public Function Test() As String
On Error GoTo Err_Test
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Set db = CurrentDb()
strSql = "Select * FROM MyTable WHERE MyTable.Id = GetId()"
Set rst = db.OpenRecordset(strSql)
Test = rst!name
Exit_Test:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Set db = Nothing
Exit Function
Err_Test:
MsgBox Error$
Resume Exit_Test
End Function
Why does the error escape the error handler and is there some way to handle it gracefully when the sql makes a call to a vba function that generates an exception?
I know that removing the function call from the sql string as shown below, will enable the error handler to trap the error.
Dim id as Long
id = GetId()
strSql = "Select * FROM MyTable WHERE MyTable.Id = " & id
Is this the only way? If so, should one avoid using function calls in sql query strings to avoid unhandled exceptions?
