What is Error 0, and need I worry about it ?

[rufus_the_tailgunner]

When I've finished stepping through a recordset (using rst.movenext etc.) the
last thing that happens is that the error handler kicks in with Error 0.
Doing ?AccessError(0) in the immediate window just returns a blank line, so
is this error insignificant or is there some underlying problem I should be
debugging ?

The full code I am using is (as yet unfinished and a little untidy, so
apologies for that) :
Dim db As Database
Dim rst As Recordset
Dim fldTitle As Field
Dim wrkCurrent As Workspace
Dim strSQL As QueryDef
Dim tabname As String
Dim strColumn As String
Dim rcdCount As Integer
Dim i, r As Integer
Dim response As Variant

On Error GoTo ErrorHandler

Set wrkCurrent = DBEngine.Workspaces(0)
Set db = CurrentDb()
tabname = "Involvements"
Set rst = db.OpenRecordset(tabname)
Set fldTitle = rst![Actual DOB]
wrkCurrent.BeginTrans
rcdCount = rst.RecordCount
rst.MoveFirst
For r = 1 To rcdCount
If fldTitle < #1/1/1900# Then
'Debug.Print rst![First Name] & " " & rst!Surname & " " & fldTitle
If MsgBox("Update " & rst![Person's DetailsSwift ID Number] _
& " (" & rst![First Name] & " " & rst!Surname & ") from " & fldTitle
& " to 1/1/1900 ?", vbYesNo, "Caselist Plus") = vbYes Then
rst.Edit
fldTitle = #1/1/1900#
rst.Update
End If
End If

For i = 0 To (rst.Fields.Count - 1)
If rst.Fields(i) = "#EMPTY" Then
rst.Edit
MsgBox rst![Person's DetailsSwift ID Number] & " contains
'#EMPTY'"
rst.Fields(i) = ""
rst.Update
End If
Next i
rst.MoveNext
Next r

If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
wrkCurrent.CommitTrans
Else
wrkCurrent.Rollback
End If

Set wrkCurrent = Nothing
Set db = Nothing
Set rst = Nothing
Set fldTitle = Nothing

ErrorHandler:
'some error has occurred
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub
End If
End Sub

I might also add that I'm having to use Access 200 (with the v9.0 Object
Library)

TIA

 

[Douglas J. Steele]

Error 0 means there's no error.

The problem with your code is that you're always going to fall into the
Error Handler at the end of the routine.

Try changing the end from

Set wrkCurrent = Nothing
Set db = Nothing
Set rst = Nothing
Set fldTitle = Nothing

ErrorHandler:
'some error has occurred
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub
End If
End Sub

to

EndIt:
Set wrkCurrent = Nothing
Set db = Nothing
Set rst = Nothing
Set fldTitle = Nothing
Exit Sub

ErrorHandler:
'some error has occurred
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Resume EndIt

End Sub

Incidentally, you should consider changing

Dim db As Database
Dim rst As Recordset
Dim fldTitle As Field
Dim wrkCurrent As Workspace
Dim strSQL As QueryDef

to

Dim db As .DAO.Database
Dim rst As DAO.Recordset
Dim fldTitle As DAO.Field
Dim wrkCurrent As DAO.Workspace
Dim strSQL As DAO.QueryDef

The issue you're trying to avoid here is that some of those objects
(specifically Recordset and Field) exist in more than one model. If you move
to another database, DAO may not be sufficiently high in the list of
References, so you may run into issues with the wrong data type being
assigned. Even for those objects which only exist in a single model,
disambiguating like that is always a good idea.

 

[rufus_the_tailgunner]

Thanks a lot Doug, that's very helpful. I have another question about nested
transactions, but I'll put that in a seperate post.