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

Selasa, 02 Agustus 2011

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

Yep, there was a better way:

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

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

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

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