run-time error 3021 no current record help

[stuckagain1]

Can anyone explain why the code below bombs out at the ** (** is not in the code). the error message is run-time 3021, no current record. there are two records in the table with different ids...thanks--

Function Petlib()

Set db = CurrentDb

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

rs1.MoveFirst

Do While Not rs1.EOF
' get data from first record
mmid = rs1.DONOR_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

**error! Do While rs1.DONOR_ID = mmid
Do While mcat = rs1.Expr2 And rs1.DONOR_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.DONOR_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

 

[genomon]

Looks like you query only returns one record - when you do the .MoveNext there is nothing to move to.

 

[stuckagain1]

Thanks, but the query does return two records different donor_ids.....any other ideas?

 

[RoyVidar]

I think you should have gotten a method or member not found error prior to that line, since recordsets doesn't (usually) have a donor_id method. Try using bangs (!) in stead of dots when qualifying/referencing fields in the recordsets.

The line where it errors out, is dangerous, as it attempts to test on a record after moving to the next record without testing for .eof.

Roy-Vidar

 

[DougStevens]

Just guessing, and a shot in the dark.
Let's say you have two records 5 and 6.

Outer most loop, you loop through the recordset.
In the first pass you start at 5.
You set "mmid" to 5.
Then you move to the next record. (Not sure yet).
Now you're on record 6.

Then you're in the first inner loop, which tests if the RS!mmid equals the variable mmid. In this case it does not (6<>5). So this loop is not entered and therefore the inner loop of that is not entered either.


Which takes us towards the end, where you MovePrevoius.
The current record is back at 5. You update it, and then MoveNext and now you're on record 6.

You set the variable "mmid" to 6.
You MoveNext, which is not in the recordset, and then it bombs out when you test again if RS1!mmid equals the variable "mmid".

Is that what seems to be happening?

In other words, it should bomb out when you're on the last record in your recordset.

************************************************
With the "Updates" in the code, its seems if a donor has multiple categories and then multiple contributions, you're only going to update various records (not all the records for that category or donor).