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]
Tidak ada komentar:
Posting Komentar