Error ‘Update of CancelUpdate without AddNew or Edit’

[Liz James]

In A97:
There is a form which selects a category to export. A subform opens to the
selected category and you can select which records to export for the selected
category.

To clear out selected records left from a previous export I have the code
below, but it just throws the above error when I try to open the form.
The help file doesn’t help me at all.

Private Sub Form_Load()
SetMarks (False)
End Sub


Public Function SetMarks(bolSelect As Boolean)

Dim dbs As Database
Dim rstAssessment As Recordset

'Clear out Track Records table for previously marked items
Set dbs = CurrentDb
Set rstAssessment = dbs.OpenRecordset("TrackRecords", dbOpenTable,
dbOpenDynamic)

With rstAssessment
Do Until rstAssessment.EOF
.Fields("Selected") = bolSelect
.MoveNext
Loop

.Update dbUpdateBatch
End With

rstAssessment.Close
Set rstAssessment = Nothing
Set dbs = Nothing

End Function


Private Sub cmdClearAll_Click()
SetMarks False
Me.ExportSubform.Requery
End Sub

 

[Allen Browne]

You forgot to use the .Edit and .Update methods:
With rstAssessment
Do Until .EOF
.Edit
.Fields("Selected") = bolSelect
.Update
.MoveNext
Loop
End With

BTW, it may be easier and faster to avoid the loop, and execute an Update
query statement:
strSql = "UPDATE TrackRecords SET Selected = False WHERE Selected =
True);"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[Liz James]

Hi Allen, Thanks so much. The loop is slow, thanks for the tip about the
update query.
Liz James