Trapping errors from RowSource query

[jordanbk]

Hi folks,

How does one trap run-time exception errors which occur as a result of RowSource settings (which were set using the Properties window in Design View)?

I'm working with a large codebase of VBA written in Access 2003. In over 100 instances, the developer used RowSource to enter a SELECT query, from the Properties window (not at runtime), with RowSourceType = "Table/Query". I'm tasked with adding error handling throughout the code, but I can't find a way to trap errors which might happen when the RowSource query executes.

I've considered moving the query from the Properties window into the Form Load event, setting the control's RowSource property there at runtime. But I still can't think of a way to trap it using ON ERROR, because when the RowSource query executes, VBA/Access is outside of any of my procs/functions, thus ON ERROR doesn't apply.

If I can't trap the errors, Access will show a message box and my error logging/handling code won't run. (For example, I'm expecting some possible timeout errors on these SELECT queries.)

Any thoughts?

Thanks,
Jordan

 

[MarkK]

Hey there and welcome.
I believe that you can open a recordset first, and then assign it to the recordset property of a control as a way to populate that control with data. Then you can handle errors that may occur when the recordset opens and decline to show the form, or the control as the case may be.

sub form_open(cancel as integer)
on error goto handler
  set me.mylist.recordset = currentdb.openrecordset("MyTable")
  exit sub
handler:
  msgbox err & " " & err.description, vbinformation
  cancel = true
end sub

Making sure, of course, that any RowSource SQL is not present when the form opens.

 

[jordanbk]

Yes of course, there are multiple ways to read the data other than through RowSource, including your suggestion. However, as I pointed out, I have over 100 forms with RowSource and I really don't want to refactor all that code unless it's necessary.

Fortunately, I learned the answer from someone on another MS Access forum. The Form_Error event handler can trap a form-wide error (such as one triggered when querying for RowSource) and prevent a message box, as follows:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "Error trapped! " & DataErr
' Log the error here
Response = acDataErrContinue ' don't show the error dialog box
' Response = acDataErrDisplay ' fall through, Access will show error box
End Sub

You don't get the description, just the error number. But it's good enough.

Regards,
Jordan

 

[MarkK]

Thanks for posting back with your solution.