Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: Can't loop and change all records

    Hello,

    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
    Rst.MoveFirst
    Do Until Rst.EOF

    Rst.Fields("Update") = ""
    Rst.MoveNext

    Loop


    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



    Loop


    Thanks in advance for your help

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This would be much more efficient:

    CurrentDb.Execute "UPDATE tblProjectManager SET [Update] = ''"
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed. The code is overly complex for such a simple operation.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    besides which we dont actaully know what error you hit.....

    Code:
    Do Until Rst.EOF = True
    txtUpdate.Value = ""
    DoCmd.GoToRecord , , acNext
    Loop
    so Im guessing.. because you dont tell us the error is either...

    becuase rst is already at the end of file.. it shouldn't even get to this code block in..
    Code:
    Do Until Rst.EOF = True
    OR you are assigning a value to a control called txtupdate, why?. In
    Code:
    txtUpdate.Value = ""
    OR you then try to move elsewhere in the forms current recordset, not the recordset rst in
    Code:
    DoCmd.GoToRecord , , acNext
    frankly its as mess

    BTW stylistically I prefer to use
    do while rst.eof=false rather than
    Code:
    Do Until Rst.EOF = True
    ..
    the reason.. its doens't affect the code path.. I just htink its easier to read.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting... I find it easier to read to:

    Do
    Commands
    Loop Until EOF

    Everyone is different Thank GOD!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  7. #7
    Join Date
    Mar 2008
    Posts
    4
    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!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the most effective technique I suspect would be to issue an update query
    then requery the underlying recordset for the form.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

    Do While Not rs.EOF
    ...
    Loop
    Paul

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    the most effective technique I suspect would be to issue an update query
    Come on healdem, give it some clout!
    You know that is the most effective technique
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    CityBlue.... Mark - have you stumbled upon a kindred spirit?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Come on healdem, give it some clout!
    You know that is the most effective technique

    to change the emphasis a leetle...
    the most effective technique I suspect would be...
    to issue an update query then requery the underlying recordset for the form.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •