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

And now I'm feeling smarter :) I didn't catch that I was comparing to the
EOF on that last loop thru, which totally explains why the 3021 was coming
up at that point.

As always, John V., you do a wonderful job of spreading Access Enlightenment
(tm) :)

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

On Tue, Aug 2, 2011 at 11:47 AM, John Viescas <john@viescas.com> wrote:

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

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

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

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/