Renumber sequence like tab property

  • Thread starter Thread starter DonP
  • Start date Start date
D

DonP

I need to find the functions/routines that work the same as the tab numbering
schema in forms property. I want to start with a unique list of numbers from
a table and if I change any one of those numbers then the entire recordset
will be renumbered. Example: I have numbers 1,2,3,4,5 - I change 3 to 2. I
would like the routine to either renumber the original 2 to a 3 or change
original 2 to a 6 and leave 3 as an empty slot..
I am open to suggestions that are smarter than these simple methods.
 
DonP said:
I need to find the functions/routines that work the same as the tab numbering
schema in forms property. I want to start with a unique list of numbers from
a table and if I change any one of those numbers then the entire recordset
will be renumbered. Example: I have numbers 1,2,3,4,5 - I change 3 to 2. I
would like the routine to either renumber the original 2 to a 3 or change
original 2 to a 6 and leave 3 as an empty slot..
Click to expand...


Assuming your sequence number field is named SeqNo and it is
bound to a text box with the same name (watch out for line
wrapping):

Private Sub SeqNo_AfterUpdate()
Dim db As DAO.Database

Set db = CurrentDb()

If Me.SeqNo < Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo + 1 " _
& "WHERE SeqNo Between " & Me.SeqNo & "
And " & Me.SeqNo.OldValue - 1
ElseIf Me.SeqNo > Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo - 1 " _
& "WHERE SeqNo Between " &
Me.SeqNo.OldValue + 1 & " And " & Me.SeqNo
Else
Me.SeqNo.Undo ' don't allow null or ??
End If

End Sub
 
Thank you, that is a nifty way of doing it.

Marshall Barton said:
Assuming your sequence number field is named SeqNo and it is
bound to a text box with the same name (watch out for line
wrapping):

Private Sub SeqNo_AfterUpdate()
Dim db As DAO.Database

Set db = CurrentDb()

If Me.SeqNo < Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo + 1 " _
& "WHERE SeqNo Between " & Me.SeqNo & "
And " & Me.SeqNo.OldValue - 1
ElseIf Me.SeqNo > Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo - 1 " _
& "WHERE SeqNo Between " &
Me.SeqNo.OldValue + 1 & " And " & Me.SeqNo
Else
Me.SeqNo.Undo ' don't allow null or ??
End If

End Sub
Click to expand...
 
Back
Top