Help me resolve that problem I want to edit all the records only that meets the condition. The problem I'm is that my codes bellow behaves as though there no condition hence it is editing all the records in the table.

Dim a As Boolean
rs.Open "SELECT* FROM pupils", con, adOpenKeyset, adLockOptimistic - 1

rs.MoveFirst
 Do While Not rs.EOF

 If rs!grade = Me.txtoldtxtgrade And rs!CLASS = Me.txtolclass And rs!years = Me.txtoldyear Then
 a = True
 Exit Do
 Else
 rs.MoveNext
 End If
 Loop
 If a = True Then
rs!grade = Me.txtnewgrade
 rs!CLASS = Me.txtnewclass
 rs!years = Me.txtnewyear
 rs.Update
                rs.Close

                MsgBox "Records update", vbInformation
                Else
                rs.Close
                MsgBox "Sorry something went wrong record not edited"
                End If

End Sub

That's a mess to read but line 2 has this SELECT* that appears odd since I would have a space after SELECT.

Why are you looping through records? SQL lets us select a set of records and we can apply the change to that set.
This looks like what a first quarter SQL student would write. And no I don't write your code for you.

In short, you need to select your set of records to change. No looping required for this one.

PS. Another lesson your class should cover is to not use "SELECT *". If you only need those columns, select just those columns.
Read https://gertjans.home.xs4all.nl/sql/dont-use-select-star-in-production-code.html among others.

Edited 6 Months Ago by rproffitt

This article has been dead for over six months. Start a new discussion instead.