updating multiple rows in a loop

[looloo]

The annoying thing about this code is that it performs a separate update for each item in the list box. How could I use the loop to accumulate the records to be updated and then perform all the updates at once (ask for confirmation just once instead of multiple times)?

Dim SQLMasterUpdate As String
Dim iRow As Variant

If Me!RestaurantListBox.ItemsSelected.Count <> 0 Then
For Each iRow In Me!RestaurantListBox.ItemsSelected

SQLMasterUpdate = " (SQL Update statement) "

DoCmd.RunSQL SQLMasterUpdate



Next iRow

Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

[pbaldy]

If you use

CurrentDb.Execute SQLMasterUpdate, dbFailOnError

it won't ask for confirmations. You could also use SetWarnings before and after, but Execute is more efficient anyway.

[looloo]

I kind of do want a confirmation though, just ONE. Any ideas for that?

[stmoong]

See your other post, which I have replied. It contains the approach you can use.

https://www.accessforums.net/queries...ror-17471.html

Basically, you need to wrap it in a transaction. Then, prompt the user once with MsgBox, if user accepts, then you commit the transaction.

[pbaldy]

I'd probably code my own with a Yes/No message box, but to use the native one you'd build an IN clause for the update query:

WHERE FieldName IN(1,4,7)

[looloo]

Thanks, stmoong and pbaldy. I ended up going with the Yes/No box and the gears seem to be turning smoothly. Now for some cleanup and we'll see what's next...