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

 

Nope. The code first gathers all the Mandate and Hours data from matching rows
and then writes out one summary record.

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 giorgio_rovelli
Sent: Wednesday, August 03, 2011 4:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: getting 3021 error even with testing for EOF

John, shouldn't the INSERT INTO ZSN query also be put inside a loop?
Giorgio

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> 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@...>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
>

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

Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 6
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___