Recordset processing problem

  • Thread starter Thread starter Dorian
  • Start date Start date
D

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".
 
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".
 
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".
 
Back
Top