Looping Through Table, Changing Field Values

Question

I'm trying to write a piece of code that will run through a table and replace every field that has a certain value with another value.

Private Sub Form_Load()


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT Profile3 FROM Bank WHERE 'AB'"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rst.EOF

With rst

If .RecordCount > 0 Then

  .MoveFirst
  .Edit
  !Profile3 = "AA"
  .Update     
  .MoveNext


End If

End With

Loop

End Sub

That's what I'm currently using, however, when it runs it crashes horribly. I know the base code works because when I pull out the loop it works, but only on the first entry.

Like most of the issues I seem to have with VBA, it's probably an absurdly simple fix that I am overlooking.

Thank you for your help.

Fionnuala · Accepted Answer · 2013-01-02 18:47:11Z

1

You are constantly moving first. You need to get on at some stage. Just get rid of MoveFirst.

Do Until rst.EOF
    With rst
      .Edit
      !Profile3 = "AA"
      .Update     
      .MoveNext
    End With
Loop

In addition, I guess you mean WHERE somefield:

strSQL = "SELECT Profile3 FROM Bank WHERE somefield='AB'"

However, in this case, I suspect what you need is:

strSQL = "UPDATE Bank SET Profile3 ='AA' WHERE Profile3 ='AB'"
CurrentDB.Execute strSQL, dbFailOnError

edited Jan 2, 2013 at 18:47

answered Jan 2, 2013 at 18:42

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1706975 Over a year ago

Aha! See, I knew it was something simple. Now I just need to figure out how to make it not overwrite EVERY entry, but just the ones that say AB instead of AC or AD, etc. Thanks!

user1706975 Over a year ago

That worked absolutely perfectly. Thanks, I really appreciate it.

Collectives™ on Stack Overflow

Looping Through Table, Changing Field Values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related