sorting a ranking

[adaniele]

Hi guys. Here is my problem...
I have a form called ranking. It has a subform with a list of wines where each wine has a ranking number.
What i would like to do is to change the ranking of a specific wine and then rerank the rest of the wines.

example:

wine rankink
a 1
b 2
c 3
d 4

If i modify wine c to be 1, then as a result of that change the subform should shows..

wine rankink
a 2
b 3
c 1
d 4

any suggestion? thx, max.

 

[MarkK]

Here's a stab at it...

Private Sub Form_BeforeUpdate(Cancel As Integer)
[COLOR="Green"]   'determine how rank is changing, increasing or decreasing
[/COLOR]   If Me.rank < Me.rank.OldValue Then
[COLOR="Green"]      'rank is decreasing, so add one to intermediate ranks
[/COLOR]      CurrentDb.Execute _
         "UPDATE Table1 SET rank = rank + 1 " & _
         "WHERE rank >= " & Me.rank & " And rank < " & Me.rank.OldValue
   ElseIf Me.rank > Me.rank.OldValue Then
[COLOR="Green"]      'rank is increasing, so subtract one from intermediate ranks
[/COLOR]      CurrentDb.Execute _
         "UPDATE Table1 SET rank = rank - 1 " & _
         "WHERE rank <= " & Me.rank & " And rank > " & Me.rank.OldValue
   End If
End Sub

Private Sub Form_AfterUpdate()
   Me.Requery
End Sub

 

[adaniele]

lagbolt,
your code worked beutifully but only if i change the RECORD LOCKS to no locks.
Otherwise, it shows me a violation error. is that 'cos the data in the subforms comes from a query?
thx, max.