avoiding the no current record error

[atadbitslow]

I get a "run-time Error 3021 No current record" when the code below is run......which just means it is at the end of the file---how do i get it to not error out? thanks-

Function PS()
Set db = CurrentDb

Set rs1 = db.OpenRecordset("SELECT [collections thank you batch].ID, [collections thank you batch].Expr2, [collections thank you batch].NAME_LAST, [collections thank you batch].NAME_FIRST, [collections thank you batch].expr1, [all].tempstring, [all].num_mergerecords FROM [all] INNER JOIN [collections thank you batch] ON [all].ID = [collections thank you batch].ID" _
& " ORDER BY [collections thank you batch].ID, [collections thank you batch].Expr2;&quot

rs1.MoveFirst

Do While Not rs1.EOF
' get data from first record
mmid = rs1.ID
mcat = StrConv(rs1.Expr2, vbProperCase) 'category
mtitle = rs1.Expr1 'TITLE
mcontb = StrConv((rs1.Expr2) & "(s)", vbProperCase) + Chr(13) + Chr(9) + Chr(9) + rs1.Expr1
rs1.MoveNext ' move to second record

Do While rs1.ID = mmid
Do While mcat = rs1.Expr2 And rs1.ID = mmid
mcontb = mcontb + Chr(13) + Chr(9) + Chr(9) + rs1.Expr1
rs1.MoveNext
If rs1.EOF Then Exit Do
Loop

rs1.MovePrevious
rs1.Edit
rs1.tempstring = mcontb
rs1.Update
rs1.MoveNext
If rs1.EOF Then Exit Do
mmid = rs1.ID
mcat = StrConv(rs1.Expr2, vbProperCase)
mtitle = rs1.Expr1
mcontb = Nz(rs1.tempstring) + Chr(13) + Chr(9) + StrConv((rs1.Expr2) & "(s)", vbProperCase)

Loop
rs1.MovePrevious
rs1.Edit
rs1.tempstring = mcontb
rs1.Update
rs1.MoveNext 'back to where we were
Loop
' handle the very last id sequence
On Error Resume Next
endjob:
rs1.MovePrevious
rs1.Edit
rs1.tempstring = mcontb
rs1.Update
On Error Resume Next
End Function

 

[Steve101]

Well, if you placed the line:

On Error Resume Next

near the top of the code, you would subvert the error trapping, however whether the code does what you would like it to do is another question altogether. One should not have to rely on ignoring errors to accomodate looping through recordsets. I think you need to review what it is you're doing, and the logic associated with getting it done. At the very least, put some simple comments in the code which explain the nested iterations.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[rkasnick]

I believe that when you want to use the EOF property you must first set the file recordset boundaries. In this case you should use the MOVELAST then MOVEFIRST commands before you test for EOF in a loop.

ex: rs1.movelast
rs1.movefirst

do
.
loop until rs1.EOF

You may also get an error using MOVExxx if the recordset is empty so condition the commands with a RECORDCOUNT variable and only execute code if that variable is greater than zero. I have always conditioned the construct on the LOOP end rather than the DO end, but then it is a personal preference.


Regards
Rod