Update calling all rows if clause in vba

Question

i am writing a macro to convert the zeros in the access table to "0000"

Zero is text data type so i cast it to int in the if condition to update the records which is only zeros and preventing it in updating records which are non zeros

but now all the records are getting updated ..if clause is calling all the time even there is a value 2 in the particular row

please help

Dim db As Database
Dim rst As Recordset
Dim strData As String
Set db = CurrentDb()

Dim qryString As String
qryString = "SELECT * FROM tblECodes "

Set rst = db.OpenRecordset(qryString)
rst.MoveFirst

Do While Not rst.EOF

  Dim testid As String
  Dim Sch1 As Integer
  Sch1 = CInt(rst.Fields("Scheduled"))
  Testid1 = rst.Fields("Testid")

  If Sch1 = "1" Then
    strSQLUp = "Update tblECodes set scheduled = '0000' where testid = 148"
    CurrentDb.Execute strSQLUp
  End If

  rst.MoveNext

Loop
rst.Close
db.Close

Answer 1

Should this be "testid = Testid1"? Also, you are doing Sch1 = "1", you may want to change that to 1 (without quotes).

Do While Not rst.EOF

  Dim testid As String
  Dim Sch1 As Integer
  Sch1 = CInt(rst.Fields("Scheduled"))
  Testid1 = rst.Fields("Testid")

  If Sch1 = 1 Then
    strSQLUp = "Update tblECodes set scheduled = '0000' where testid = " & Testid1
    CurrentDb.Execute strSQLUp
  End If

  rst.MoveNext

Loop

Also, instead of looping RBAR (row by agonizing row), you want to execute a single query for better performance:

docmd.RunQuery ("Update TblECodes Set Scheduled = '0000' Where Scheduled = '1'")

Answer 2

The docmd.RunQuery suggestion from Raj should work well. But if you'd like to stay in VBA:

Do While Not rst.EOF
  If rst!Scheduled = "0001" And rst!testid = "148" Then
    rst.Edit
    rst!scheduled = "0000"
    rst.Update
  End If
  rst.MoveNext
Loop

Answer 3

Your question includes some very confused code.

You define this recordset:

  Set rst = db.OpenRecordset("SELECT * FROM tblECodes")

and then walk through it row by row testing whether the row matches certain criteria and then execute a SQL string that updates rows in the very same table.

This makes absolutely no sense.

This SQL string ought to do the job (assuming I've understood the problem with non-zero fields being updated when they shouldn't be):

  UPDATE tblECodes
  SET Scheduled = "0000"
  WHERE Scheduled = "0" AND Sch1 = "1" AND TestID = 148;

So far as I can tell from your code, the SQL statement above is absolutely equivalent to your procedural code, with the added caveat that it will only update records where Scheduled=0.

Execute this with CurrentDB.Execute and the dbFailOnError option and you should be successful, at least, insofar as you've clearly described your problem.