自动编号字段仅用于标识记录。不多不少。
你需要的是一个
优先事项
(或
等级
)菲尔德。
在显示记录的表单中,对该字段运行如下代码:
Private Sub Priority_AfterUpdate()
Dim rst As DAO.Recordset
Dim lngId As Long
Dim lngPriorityNew As Long
Dim lngPriorityFix As Long
' Save record.
Me.Dirty = False
' Prepare form.
DoCmd.Hourglass True
Me.Repaint
Me.Painting = False
' Current Id and priority.
lngId = Me!Id.Value
lngPriorityFix = Nz(Me!Priority.Value, 0)
If lngPriorityFix <= 0 Then
lngPriorityFix = 1
Me!Priority.Value = lngPriorityFix
Me.Dirty = False
End If
' Rebuild priority list.
Set rst = Me.RecordsetClone
rst.MoveFirst
While rst.EOF = False
If rst!Id.Value <> lngId Then
lngPriorityNew = lngPriorityNew + 1
If lngPriorityNew = lngPriorityFix Then
' Move this record to next lower priority.
lngPriorityNew = lngPriorityNew + 1
End If
If Nz(rst!Priority.Value, 0) = lngPriorityNew Then
' Priority hasn't changed for this record.
Else
' Assign new priority.
rst.Edit
rst!Priority.Value = lngPriorityNew
rst.Update
End If
End If
rst.MoveNext
Wend
' Reorder form and relocate record.
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "Id = " & lngId & ""
Me.Bookmark = rst.Bookmark
' Present form.
Me.Painting = True
DoCmd.Hourglass False
Set rst = Nothing
End Sub
只要给任何记录分配一个等级,记录就会根据需要重新编号。