Overflow error - only occurs when running queries with code.

[jojones]

I have a few queries which I run using come code...

.RunSQL "Delete * FROM tblOS"
.OpenQuery "qryAppendOS" 'appends data to tblOS
.RunSQL "Delete * FROM tblOutstandingsMovements"
.OpenQuery "qryOSMovements" 'appends data to
tblOutstandingsMovements

If I manually run the above using the two append queries and two delete queries I have created I do not get any errors... BUT...

in the code I have an error handler which does this:

Set mydB = DBEngine.OpenDatabase & _
("C:\Financial_Reporting\Error_Log\Error_Log.mdb&quot
Set myError = mydB.OpenRecordset("tblErrors", dbOpenTable)
With myError
.AddNew
!ErrNum = Err.Number
!ErrDesc = Err.Description
!Step = Err.Source
!ErrDate = Now()
!ErrDatabase = "SCM"
.Update
End With
Set mydB = Nothing
Set myError = Nothing
Resume Next

When I run the code it spits out two errors. These are:

Err Num Err Desc
20 Resume without error
6 Overflow

I cannot see how the Err 6 is causing any problems or how the data differs from when I run it manually, and I do not know why the Err 20 is occurring. Can ayone help? Perhaps I have done something wonky in the code... here is the whole thing... THANKS

Sub SCMUpdate()
Dim mydB As Database
Dim myError As Recordset

On Error GoTo ErrorLogging

With DoCmd
.SetWarnings False
.RunSQL "Delete * FROM tblOS"
.OpenQuery "qryAppendOS" 'appends data to tblOS
.RunSQL "Delete * FROM tblOutstandingsMovements"
.OpenQuery "qryOSMovements" 'appends data to
tblOutstandingsMovements
.SetWarnings True
End With
'Compact_Database (taken this out for a bit to see if
'errors still occurred - they did!)

ErrorLogging:
Set mydB = DBEngine.OpenDatabase
("C:\Financial_Reporting\Error_Log\Error_Log.mdb&quot
Set myError = mydB.OpenRecordset("tblErrors", dbOpenTable)
With myError
.AddNew
!ErrNum = Err.Number
!ErrDesc = Err.Description
!Step = Err.Source
!ErrDate = Now()
!ErrDatabase = "SCM"
.Update
End With
Set mydB = Nothing
Set myError = Nothing
Resume Next

End Sub

 

[Ed2020]

Try:

Sub SCMUpdate()
Dim mydB As Database
Dim myError As Recordset

On Error GoTo ErrorLogging

With DoCmd
.SetWarnings False
.RunSQL "Delete * FROM tblOS"
.OpenQuery "qryAppendOS" 'appends data to tblOS
.RunSQL "Delete * FROM tblOutstandingsMovements"
.OpenQuery "qryOSMovements" 'appends data to
tblOutstandingsMovements
.SetWarnings True
End With
'Compact_Database (taken this out for a bit to see if
'errors still occurred - they did!)

ExitHere:
Exit Sub

ErrorLogging:
Set mydB = DBEngine.OpenDatabase
("C:\Financial_Reporting\Error_Log\Error_Log.mdb&quot
Set myError = mydB.OpenRecordset("tblErrors", dbOpenTable)
With myError
.AddNew
!ErrNum = Err.Number
!ErrDesc = Err.Description
!Step = Err.Source
!ErrDate = Now()
!ErrDatabase = "SCM"
.Update
End With
Set mydB = Nothing
Set myError = Nothing
Resume ExitHere

End Sub