Member Avatar for Robbin_1

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
  • 2 Contributors
  • 1 Reply
  • 117 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by rproffitt
Member Avatar for rproffitt

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 by rproffitt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.