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

Selasa, 02 Agustus 2011

RE: [MS_AccessPros] Re: getting 3021 error even with testing for EOF

Shay-

The problem is you move off the last record, then try to compare values in your
record in the Do While - that generates the error. Yes, it's spaghetti code! I
would have done it like this:

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)

If Not rstZeros.EOF Then

strDistID = rstZeros!distid
strDistName = rstZeros!distname
strFName = rstZeros!fname
strLName = rstZeros!lname
strTitle = rstZeros![Title]
strSite = rstZeros!sitename
strNamTit = rstZeros!nametitl

Do While Not rstZeros.EOF
If strNamTit = rstZeros!nametitl _
And strSite = rstZeros!sitename Then
strMand = strMand & (", " + rstZeros!Mandate)
dblHrs = dblHrs + rstZeros!hours
Else
Exit Do
End If
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
End If

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

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

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Shay Holmes
Sent: Tuesday, August 02, 2011 8:36 PM
To: _List_MSAccessPros
Subject: [MS_AccessPros] Re: getting 3021 error even with testing for EOF

Ha! it took a bit of spaghetti code, but my fix was to wrap the nested loop
like so:

On Error GoTo JumpLoop
Do While strNamTit = rstZeros!nametitl And strSite = rstZeros!sitename
And Not rstZeros.EOF
strMand = strMand & (", " + rstZeros!Mandate)
dblHrs = dblHrs + rstZeros!hours
rstZeros.MoveNext
Loop
JumpLoop:
On Error GoTo ErrHandler

The last record is now updating correctly - and I'm wondering if a more
elegant solution would be to add a test for EOF around the movenext command
...

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

On Tue, Aug 2, 2011 at 11:02 AM, Shay Holmes <shaybellaholmes@gmail.com>wrote:

> 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]

------------------------------------

Yahoo! Groups Links