ACCESS FEED: [MS_AccessPros] getting 3021 error even with testing for EOF

Selasa, 02 Agustus 2011

[MS_AccessPros] getting 3021 error even with testing for EOF

Hi, folks!

I need some help figuring out what's hamstringing my code (below). I put a
*** on the line that the debugger takes me to when it throws a 3021 - no
current record error. If I try to "resume next" for the 3021, the entire app
hangs.

The 3021 always occurs on the last record, which means that record isn't
getting appended to my holding table.

~~CODE~~~

Public Sub SetUpZSN()
On Error GoTo ErrHandler

Dim strSQL As String
Dim strInsert As String
Dim dbs As DAO.Database
Dim rstZeros As DAO.Recordset
Dim strDistID As String
Dim strDistName As String
Dim strNamTit As String
Dim strFName As String
Dim strLName As String
Dim strTitle As String
Dim strSite As String
Dim dblHrs As Double
Dim strMand As String

Set dbs = CurrentDb

strSQL = "Select * from ZeroSalarySummary order by distid, nametitl,
sitename, mandate;"

Set rstZeros = dbs.OpenRecordset(strSQL, dbOpenDynaset)

rstZeros.MoveLast
rstZeros.MoveFirst

If Not rstZeros.BOF And Not rstZeros.EOF Then

Do While Not rstZeros.EOF
strDistID = rstZeros!distid
strDistName = rstZeros!distname
strFName = rstZeros!fname
strLName = rstZeros!lname
strTitle = rstZeros![Title]
strSite = rstZeros!sitename
strNamTit = rstZeros!nametitl

*** Do While strNamTit = rstZeros!nametitl And strSite =
rstZeros!sitename And Not rstZeros.EOF
strMand = strMand & (", " + rstZeros!Mandate)
dblHrs = dblHrs + rstZeros!hours
rstZeros.MoveNext
Loop

strMand = Right(strMand, Len(strMand) - 2)

strInsert = "INSERT INTO ZSN (distid, distname, Fname, Lname, [Title],
sitename, Mandate, Hours) VALUES (" & Chr(34) & _
strDistID & Chr(34) & ", " & Chr(34) & _
strDistName & Chr(34) & ", " & Chr(34) & _
strFName & Chr(34) & ", " & Chr(34) & _
strLName & Chr(34) & ", " & Chr(34) & _
strTitle & Chr(34) & ", " & Chr(34) & _
strSite & Chr(34) & ", " & Chr(34) & _
strMand & Chr(34) & ", " & _
dblHrs & ")"

dbs.Execute strInsert, dbFailOnError

strMand = vbNullString
dblHrs = 0

Loop

End If

Exit_ErrHandler:
Set rstZeros = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ErrHandler
End Sub

~~~END CODE~~~

~*~*~*~*~*~*~
Shay

[Non-text portions of this message have been removed]