Re-Order list (Bubble Sort?) (1 Viewer)

[Kodama76]

I have a table with a field called Priority. Each record in the table has a priority except for the completed ones.

What I want is to be able to check mark my Completed box on my form and have it set that record to a priority of 0 and to reorder all of the other records' priorities to fill in the gap.

i.e.
Red 1
Blue 2
Green 3

-->if I complete Blue the table should look like this.
Red 1
Blue 0
Green 2

I know to build an expression in on click for that box but I can't get the records to reorder themselves. I've tried using someone else's code that does something like this but it doesn't quite work. Here's what I have so far:

intCurrentPriority = 1
[Forms]![Printing Data Input Form]![Priority] = 0


With rst
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    
    .Open "SELECT * from [Printing Data Input] ORDER BY Priority"

    If (.RecordCount > 0) Then
        .MoveFirst
        Do
            If (!Priority >= 1 And !Priority <> intCurrentPriority) Then
            !Priority = intCurrentPriority
            .Update
            .MoveLast
            End If
            intCurrentPriority = intCurrentPriority + 1
            .MoveNext
    Loop While Not .EOF
    End If
    .Close
End With

Set rst = Nothing
Set cnxn = Nothing

Exit_Completed_Click:
    Exit Sub
    
Err_Completed_Click:
    MsgBox Err.Description
    
End Sub

Any help would be appreciated. I'm up for scrapping this code and going a different route if you have any suggestions.

 

[Fornatian]

I'm a bit rusty but it seems to me that you shouldn't be storing the ranking priority at all because it does not describe the key, only it's precedence, which can be calculated by other means.

Here' some SQL you can use which I cannibalised from a qry examples database called smpQRY97:

SELECT Prod1.CategoryID, Prod1.CountOfProductID, (Select Count(*) from qryDistinctProductCount Where [CountofProductID] >[Prod1].[CountofProductID])+1 AS Ranking
FROM qryProductCount AS Prod1
ORDER BY Prod1.CountOfProductID DESC;

Category ID CountOfProductID Ranking
3 13 1
8 12 2
2 12 2
1 12 2
4 10 3
5 7 4
6 6 5
7 5 6

If you need the database send me an email and I'll zip it for you.