Error Reporting 1

[davisto01]

I’m using a macro that calls a set of approximately append queries 15 in a sequence.

Does anyone know of a way to:
1) Stop the process immediately if there is an error with one of the appends, and
2) Send out an email to a specific person if and when such an error occurs

Any help with this would be greatly appreciated.

TD

 

[vbajock]

What you are trying to do is easily done through code but probably impossible to do with macros. Openquery does not return any values and macros cannot handle a returned value to trap an error. If there is a way to do it I'd love to hear about it.

 

[davisto01]

I would have sent this to you yesterday but the site was down.

Thanks a bunch for the tip.
At least now I know that I was perhaps going in the wrong direction. Any suggestions on how to do this with code? I'm pretty sure that I'm all set with translating the commands from the macro to the mod.

A sample for the error trapping would do wonders though.

Thanks!
TD

 

[hwkranger]

Well,

I use an error trap that keeps track of them in a table. This isn't always the "best" situation, but for me it works. I used to keep them in a text file, but it really seems easier to write them to a table and then view them later.

Public Function MyErrorHandler(Optional location As String)
'Exit Function 'breaks function so that Sites don't errors log anymore.

Dim db As Database
Dim myrecord As Recordset

Set db = CurrentDb()
Set myrecord = db.OpenRecordset("ErrorsInSystem&quot

'From the form object, you can get the name from the .Name property:
Dim Frm As Form
Dim strFormName As String

Set Frm = Screen.ActiveForm 'error may occur here 2475 if no active window
strFormName = Frm.Name 'form name

'makes sure location is set.
If Len(location) < 3 Then
location = &quot;Unknown&quot;
End If

'updates ErrorsInSystem table
With myrecord
.AddNew
![When] = Now()
![Who] = [Forms]![Operator]![OperatorID]
If Err.Number > 0 Then
![ErrorNumber] = Err.Number
End If

If Len(Err.DESCRIPTION) > 0 Then
![ErrorMessage] = Err.DESCRIPTION
End If

If Len(strFormName) > 0 Then
![Form] = strFormName
End If
![ErrorLocation] = location


.Update
End With
'MsgBox (&quot;There was an Error. Error has been logged, Error Number: &quot; & Err.Number & &quot;: &quot; & Err.DESCRIPTION)

End Function


Randall Vollen
National City Bank Corp.

 

[vbajock]

Its not going to help you much to convert the macro to VB. If you are using &quot;docmd.openquery&quot; etc you still have the same problem of the query returning no error values. Your choice is either to Set Warnings off or to send the errors to the screen.

You will have to convert all the queries to actual loops that populate recordsets, and use an error handler that calls a procedure that invokes Outlook or Exchange CDO to send the email whenever a key violation occurs. This isn't novice programming.

 

[davisto01]

Hey Guys,

Thanks a lot for all of your help. I'm quite sure this will work for what I'm doing but I'll try it anyway. The kind of error I'm dealing with happens when the system is running a query and it just kind of hangs. I think if this happens, the next command in the mod (or mod) will never get excecuted.

Thanks again.