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.

Here's a stab at it...
Private Sub Form_BeforeUpdate(Cancel As Integer)
'determine how rank is changing, increasing or decreasing
If Me.rank < Me.rank.OldValue Then
'rank is decreasing, so add one to intermediate ranks
CurrentDb.Execute _
"UPDATE Table1 SET rank = rank + 1 " & _
"WHERE rank >= " & Me.rank & " And rank < " & Me.rank.OldValue
ElseIf Me.rank > Me.rank.OldValue Then
'rank is increasing, so subtract one from intermediate ranks
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

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.