Update query isn't updating everything I need.

[mrmetal]

Hi everyone. After trying to find my answer on this forum, and seeing how many people get confused by incomplete explanations, the beginning of this post is explanation. The code I need help with begins with 'HELP HERE.'

I've been trying to learn about ADO. To do so, I've been building what I thought would be a simple database for my fantasy football league. So far, I've been able to connect to an external database (also created with access) and connect to a recordset within it.

Now, what I am trying to do is to re-rank individual players during run-time. I've created a continuous form that is populated through SQL:

SELECT PlayerID, Ranking, LastName, FirstName,
Selected FROM tblPlayers IN 'C:\Documents and
Settings\emongold.CLASSROOM\Desktop\Fantasy
Football\football stats.mdb' WHERE Position=1 ORDER
BY Ranking;

The selected field is a checkbox with an underlying yes/no column in the table. If the user checks the box next to the players name and clicks a command button, the form should rerank the player:
1. Set the selected player's ranking to be one less
than it is currently
2. Set the player's ranking above the selected one to
be one more than it is currently
3. Reload (or requery, repaint) the form so the next
values are present

Both the form and the underlying recordset are not updating properly, however. I have placed a breakpoint in the code below and went through step by step and right before the end sub, I checked the underlying table. The values were changed. I closed the table, finished the procedure, went back to the form to find that only the first player's ranking changed. I went back to recordset just to find the same thing.

Any help or ideas you could give me to fix this, I'd appreciate it.

'HELP HERE

Dim PlayerUPID As Integer
Dim PlayerDOWNID As Integer

Call GetConnection

Select Case Forms!frmPlayers!cmbPositions

Case "Select Position"
MsgBox "Please select a position before attempting to re-rank players.", vbOKCancel, _
"Fantasy Football"

Case "View Quarterbacks"
Call GetRecordset("qryQB", cn)

'Find the playerid for the player you are moving up
rs.MoveFirst
rs.Find ("Ranking = ") & Me.txtRank
If rs.EOF Then MsgBox ("Couldn't find the player")
PlayerUPID = rs!PlayerID
rs.MoveFirst


'find the playerid for the player you are moving down
rs.Find "Ranking = " & (Me.txtRank - 1)
If rs.EOF Then MsgBox ("Couldn't find the player")
PlayerDOWNID = rs!PlayerID
rs.MoveFirst

cn.BeginTrans

cmdText = "UPDATE qryQB " & _
"SET Ranking = " & Me.txtRank - 1 & " " & _
"WHERE PlayerID = " & PlayerUPID

cn.Execute cmdText


cmdText = "UPDATE qryQB " & _
"SET Ranking = " & Me.txtRank & " " & _
"WHERE PlayerID = " & PlayerDOWNID

cn.Execute cmdText

cn.CommitTrans

End Select
End Sub


mrmetal