Recordset processing problem

[Dorian]

I have a very complex loop that does recordset processing.
I'm getting an error on an .ADDNEW statement saying 'operation not allowed
when object is closed'.
Well... I am not closing the object except after I have exited the loop.
What could cause this?
Would it be caused by two successive .ADDNEW statements without an
intervening .UPDATE?
My code is too voluminous to post it all.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dale_Fye via AccessMonster.com]

Dorian,

I would ensure you do a .Update before doing another .AddNew

But it looks like the error is trying to tell you that the recordset is
getting closed. You may not be intentionally closing it, but if your code
encounters an error, which is not handled (could be trying to write the wrong
data type or set a field to NULL that does not allow nulls), Access could be
closing the recordset for you.

Do you have an error handler procedure in this code segment? If not, I would
create one and start the code segment with an

ON Error GoTo Proc_Error

HTH
Dale

 

[Dorian]

Hi Dale,
Yes, I do have error handling and it is not being invoked.
Here I post the basic loop:
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
lngPrevPkg = 0
strSQL = "SELECT * FROM qryRegAgenda3 WHERE StatusID <> 2 ORDER BY ID"
rs2.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
While Not rs2.EOF
' Test if package has changed
If rs2.Fields("ID").Value <> lngPrevPkg Then
If lngPrevPkg <> 0 Then
Call ResetProposed(rs1)
Call ListRules(rs1, lngPrevPkg)
Call CalcRptSeq(rs1)
rs1.Update ' Write out a report record
End If
' Deal with new package
lngPrevPkg = rs2.Fields("ID").Value
If lngPrevPkg = 2025 Then
MsgBox rs2.Fields("DateType").Value
End If
With rs1
.AddNew <============= error here
.Fields("RptSection").Value = "A"
.Fields("RptSeq").Value = 0
.Fields("PkgID").Value = rs2.Fields("ID").Value
.Fields("PkgName").Value = rs2.Fields("PkgName").Value
.Fields("PkgType").Value = ActivType(rs2.Fields("PkgType").Value)
.Fields("PkgStatusID").Value = rs2.Fields("StatusID").Value
End With
Call InitDateCols(rs1) ' Initialize date columns
End If
' Populate actual dates if present otherwise populate target dates
If NZ(rs2.Fields("ActualDate").Value, vbNullString) <> vbNullString Then
Call PopActuals(rs1, rs2)
Else
If NZ(rs2.Fields("TargetDate").Value, vbNullString) <> vbNullString
Then
Call PopTargets(rs1, rs2)
End If
End If
rs2.MoveNext
Wend

None of the routines called within the loop do any updates, they just
populate columns of rs1.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[JimBurke via AccessMonster.com]

I don't see where you're ever opening the recordset. You have it declared as
new, but you don't have an rs1.Open anywhere (unless you're doing it in one
of those subroutines). You have an open for rs2 but not for rs1.

Hi Dale,
Yes, I do have error handling and it is not being invoked.
Here I post the basic loop:
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
lngPrevPkg = 0
strSQL = "SELECT * FROM qryRegAgenda3 WHERE StatusID <> 2 ORDER BY ID"
rs2.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
While Not rs2.EOF
' Test if package has changed
If rs2.Fields("ID").Value <> lngPrevPkg Then
If lngPrevPkg <> 0 Then
Call ResetProposed(rs1)
Call ListRules(rs1, lngPrevPkg)
Call CalcRptSeq(rs1)
rs1.Update ' Write out a report record
End If
' Deal with new package
lngPrevPkg = rs2.Fields("ID").Value
If lngPrevPkg = 2025 Then
MsgBox rs2.Fields("DateType").Value
End If
With rs1
.AddNew <============= error here
.Fields("RptSection").Value = "A"
.Fields("RptSeq").Value = 0
.Fields("PkgID").Value = rs2.Fields("ID").Value
.Fields("PkgName").Value = rs2.Fields("PkgName").Value
.Fields("PkgType").Value = ActivType(rs2.Fields("PkgType").Value)
.Fields("PkgStatusID").Value = rs2.Fields("StatusID").Value
End With
Call InitDateCols(rs1) ' Initialize date columns
End If
' Populate actual dates if present otherwise populate target dates
If NZ(rs2.Fields("ActualDate").Value, vbNullString) <> vbNullString Then
Call PopActuals(rs1, rs2)
Else
If NZ(rs2.Fields("TargetDate").Value, vbNullString) <> vbNullString
Then
Call PopTargets(rs1, rs2)
End If
End If
rs2.MoveNext
Wend

None of the routines called within the loop do any updates, they just
populate columns of rs1.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Dorian,

[quoted text clipped - 25 lines]