Return Errors to Table

[BrokenBiker]

I've been working on some update utilities which I've figured out pretty well, but I'd like some better error trapping. These utilities are being e-mailed out to the various locations (not connected by a common LAN & not web-based) and I'd like to create a table listing any errors received during the update so I can see exactly where the problem lies.

I found this thread which looks like what I need, but I can't can't find how to do it.

Funtion ImportTextFiles() --this would be whatever your function name is
On Error Goto Err_ImportTextFiles

'Below is a snippet of my code--the whole utility uses this delete/import design
DoCmd.DeleteObject acQuery, "Employee Query"
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, "Employee List TableXXX", "Employee List TableXXX", False

Exit_ImportTextFiles:
Exit Function

Err_ImportTextFiles:

Select Case Err.Number

Case 3011

*do something here if you want to log the error
*don't use a msgbox as it will stop the code and wait for the
*user to click OK

Resume Next

Case Else

Resume Exit_ImportTextFiles

End Select

End Function

I purposely created some errors to receive the error numbers (7874 & 3011), so I'll have the Case for the code. I just can't figure out how to transfer the object which caused the fail to a table.

If, for instance, Access can't find the object "Employee Table" I receive the error# 3011 (or error# 7874 for the DeleteObject line), can I have Access enter the object name into a table?

If this can be done, then I can simply make some code to export the table and then create an email for the user to send the file back to me.

 

[Oldsoftboss]

You need to do 3 things.
1.. Create a table
2.. Add a function
3.. Alter code in each form error code.

1..
tblErrors
fields:
ErrorID -> AutoNumber
ErrorTime -> Date/Time
ErrorNumber -> Text
ErrorDescription -> Memo
SubRoutine -> Text
FormName -> Text

2..
Create a new module.

Public Sub ErrorHandler(ByVal lngError As Long, ByVal strError As String, _
ByVal strSub As String, ByVal strForm As String)

On Error GoTo Err_ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblErrors")

With rs
.AddNew
!ErrorTime = Now()
!ErrorNumber = lngError
!ErrorDescription = strError
!SubRoutine = strSub
!FormName = strForm
.Update
.Close
End With

Set rs = Nothing
Set db = Nothing

'If you dont want a message comment out the following line
MsgBox "Error " & lngError & " has occured. " & strError, vbCritical, " Error"

Exit_ErrorHandler:
Exit Sub

Err_ErrorHandler:

MsgBox "An error has occurred in the Error Handling Routine." & vbCrLf & vbCrLf & _
Err.Description, vbExclamation, " ASSETSonTRACK"
Resume Exit_ErrorHandler

End Sub

3.. Alter the forms error handling
Called from the form routine cmdSales_Click as follows:
'Call ErrorHandler(Err.Number, Err.Description, "cmdSales_Click", Me.Name)


Dave

 

[Oldsoftboss]

PS

You will also need to reference Microsoft DAO 3.6

 

[BrokenBiker]

That looks like it'll return the error number and description to a table. That's not quite what I'm looking for.

The update database I made uses a LOT of delete/import lines:

DoCmd.DeleteObject acQuery, "NameOfQuery1"
DoCmd.DeleteObject acQuery, "NameOfQuery2"
DoCmd.DeleteObject acQuery, "NameOfQuery3"

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, "NameOfOldQuery1", "NameOfNewQuery1a", False
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, "NameOfOldQuery2", "NameOfNewQuery2a", False

If I've goofed and listed an incorrect object (either in the delete line or the import line) I need to know which object caused the error, or at what line the error occurred. Since it runs down the list of code, if an error occurs at the second delete-line, then the remaining bits of code won't run.

So, I'm basically looking for a way to return the object name or the whole line of code where the error occurred. That way it's easy to tell where the code stopped, and therefore, which objects remain to be updated.

I've tested, pretty thoroughly, the updates w/ copies of the off-site databases, but I'm trying to error on the side of caution.