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

 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
 End If
 If a = True Then
rs!grade = Me.txtnewgrade
 rs!CLASS = Me.txtnewclass
 rs!years = Me.txtnewyear

                MsgBox "Records update", vbInformation
                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 among others.

Edited 6 Months Ago by rproffitt

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