Can For Each function be used to edit records rather than just extracting information?

[Jerry Call]

As I understand it, the For Each function works well for navigating through a set of records and recording information. But from what I read it seems that one can't use it to edit information in a particular field.
What I need is code to loop through every record in a particular table (call it MyTable) and set the value of the (numeric) CumTally field in every record to zero.

If For Each is not the best method, what is?

[Bob Fitz]

As I understand it, the For Each function works well for navigating through a set of records and recording information. But from what I read it seems that one can't use it to edit information in a particular field.
What I need is code to loop through every record in a particular table (call it MyTable) and set the value of the (numeric) CumTally field in every record to zero.

If For Each is not the best method, what is?

You could use a recordset and loop through every record from beginning to end and change field values as you progress through the recordset but it would be mus quicker a simpler to create an Update query.

[orange]

How about SQL? But before you run this make sure that is what you want/need! Update queries, like Delete queries, can be very unforgiving.
I suggest making a backup of MyTable before doing major update. Once you confirm your result, you can delete the backup.

UPDATE MyTable
Set CumTally = 0;

[moke123]

If For Each is not the best method, what is?

I cant think of one instance where I've used a for each loop with a recordset.
Generally you'd use a do loop and test for .EOF

As Bob and Orange have suggested an update query is the way to go. Its not only easier, it's faster.

[Micron]

I would venture to say that For Each is for looping over collections whose members are usually objects, not records.

[Jerry Call]

Gee, can it really be that simple? Will let you know how it comes out. Not to worry. The table in question is small, and not yet in production.
If it crashes I can easily reconstruct it.

[Jerry Call]

Well, gang, didn't get too far. Here's my code for a cmdZero button on frmSodaTallyNorthStore:

UPDATE tblSodaTallyNorthStore
Set CumTally = 0;

This produced a red second line, so obviously the syntax must be wrong.

[Bob Fitz]

Try:

CurrentDb.Execute "UPDATE tblSodaTallyNorthStore Set CumTally = 0;"

in the Click event of your button

[Jerry Call]

Bob: Your reputation is worth much more than one star in my grateful eyes! That code not only did the trick (with the addition of a requery command),
but gave me a useful model for the future.

[Bob Fitz]

Bob: Your reputation is worth much more than one star in my grateful eyes! That code not only did the trick (with the addition of a requery command),
but gave me a useful model for the future.

Thank you for your kind words. Glad you got it working

[moke123]

Dont forget the DbFailOnError!