Simple Loop Recordset Question

[bhammer]

Access2003
A cmdCheckAll button is supposed to put checks in all the checkboxes of the
records visible (with filter, too, if applied) on a subform. I have this
working, but because the OnCurrent event of the subform loads a corresponding
path of a JPG image, the execution takes a while to go through all the
records, pausing slightly to load the image. I don't care to see the image,
just to check all the boxes. So I simply changed the Recordset to a
RecordsetClone but it won't work. Why?

Here's my code:
-----------------------------
Private Sub cmdCheckAll_Click()

DoCmd.OpenForm "mbxPleaseWait"
DoEvents

Application.Echo False
DoCmd.Hourglass True

With Me.Parent.frmDefectListRight.Form.RecordsetClone
.MoveFirst
Do While Not .EOF
Me.Parent.frmDefectListRight.Form.chkIllust = -1
.MoveNext
Loop
End With

Application.Echo True
DoCmd.Hourglass False

DoCmd.Close acForm, "mbxPleaseWait"

End Sub
----------------------------------------------

 

[bhammer]

That's odd. I got it to work with the clone by changing to the field name,
rather than the control name, and changing 0 to False. (by the way, I didn't
explain that the command button is on a subform, too--a sibling form).
----------------------------------------------------------
With Me.Parent.frmDefectListRight.Form.RecordsetClone
.MoveFirst
Do While Not .EOF
.Edit
!Illustrated = False
.Update
.MoveNext
Loop
End With
-----------------------------------------------------

 

[Graham Mandeno]

You are modifying the value in the current record on the form, not the
current record in the RecordsetClone.

Instead of:

Me.Parent.frmDefectListRight.Form.chkIllust = -1

you need:

.Edit
!chkIllust = -1
.Update

Instead of looping through the RecordsetClone, you might like to consider
using an update query:

strSQL = "Update YourTable set chkIllust = -1 where " _
& <insert criteria to filter records in subform>
CurrentDb.Execute strSQL, dbFailOnError
Me.Parent.frmDefectListRight.Form.Requery