Pbm writing to record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I open 2 recordsets. I write from one to the other.

I can move through my code once, but when it loops back to the top, I get
RunTime Error 3020, "Update or CancelUpdate without Addnew or Edit."

I don't know why I am getting this error. I am guessing that if I closed
rst2 and reopened it, I would be able to continue. I'm thinking there is a
better way of doing this.

Any Suggestions?

Brad

Here is my code

Public Sub TransferData()
Dim dbs As Database, rst As Recordset, qdf As QueryDef, rst2 As Recordset
Dim x, y As Integer
Dim CurrentPyramid As String
Dim TheRecord As Variant

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Table1")
Set rst2 = CurrentDb.OpenRecordset("tblPyramidPlanner")

rst.MoveFirst

y = 1

Do Until rst.EOF
x = 1
CurrentPyramid = rst!SowBarn
With rst2
.AddNew
!SowBarn = rst!SowBarn
.Bookmark = .LastModified
.Update
End With

With rst2
.Bookmark = .LastModified
End With

Do Until CurrentPyramid <> rst!SowBarn Or IsNull(rst!SowBarn)
With rst2
.Edit
.Fields("Barn" & x) = rst!FeederBarn
.Update
x = x + 1
End With
rst.MoveNext
Loop

x = x - 1
rst.Move (-x)
x = 1

CurrentPyramid = rst!SowBarn
Debug.Print rst!SowBarn
With rst2
.AddNew
!SowBarn = rst!SowBarn
.Update
End With

With rst2
.Bookmark = .LastModified
End With

Do Until CurrentPyramid <> rst!SowBarn Or IsNull(rst!SowBarn)
With rst2
.Edit
.Fields("Barn" & x) = rst!NumOfPigs
.Update
x = x + 1
End With
rst.MoveNext
Loop
Loop

qdf.Close
rst.Close
rst2.Close
Set qdf = Nothing
Set rst = Nothing
Set rst2 = Nothing
Set dbs = Nothing

End Sub
 
try to replace
.Bookmark = .LastModified
.Update
with
.Update
.Bookmark = .LastModified
 
Back
Top