I'm trying to loop through all records and clear a field. I've tried this:
Dim bOk As Boolean
Dim StrForm As String
Dim cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim StrDb As String
Dim sngTask As Single
Set cn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
StrDb = "SELECT * FROM tblProjectManager;"
Rst.CursorLocation = adUseClient
Rst.CursorType = adOpenKeyset
Rst.LockType = adLockOptimistic
Rst.Open StrDb, cn
Do Until Rst.EOF
Rst.Fields("Update") = ""
But that doesn't work.
This works well until I get to the end, and I get an error:
Do Until Rst.EOF = True
txtUpdate.Value = ""
DoCmd.GoToRecord , , acNext
the loop until means, if I remember correctly that you will always attempt the code block once, which is fine if you know there are rows there. if you dont then you need to place your conditional as part of the start of the block.
my stylistic beef is more about ease of reading.. I read the first line and know immediately what are the conditions that trigger the blocks exit. So In my books I don't have to rely on code indentation (something you dind't always have the luxury of) or going down the code to work out what conditions trigger the exit. Its not a problem on very samll code blocks.. say 5..10/20 lines, but if you have a significant chunk int he that code block it can be easy to miss inforamtion jumpoing backwards and forwards... it also saves grubhby fingerpirnts on the screen trying to work out which biut of code aligns with which other bit
but as you rightly point out everyone is different, and long may that continue
Thanks for the responses. I used the action query and it works, but sometimes doesn't clear all the records. The loop while doing rst.("Update") = "" says that too many records were changed during update. This usually works for me. The acnextrecord never seems to aknowledge EOF and says that I can't update a record that doens't exist when it hits the end. My other way is a bit more complicated than the action query, so I've used that for now and run it twice with a me.refresh between them. Thanks again everyone!
I think the only way the update query could miss a record is if it wasn't saved yet. You might try to explicitly save the record first, if you're running this from a form with a record from that table on it.
And just to throw another looping method out there, mine is
The one problem I think should be mentioned here with the emphasis on running update queries is that at least looping through in code you can trap errors for when records are locked by another user or whatever and can't be changed.
I've also run into situations where an update query fails but running code to do the same thing succeeds... under the same conditions.