Renumber lines

[Joshua A. Booker]

Hi there,

I use the following code to renumber lines on a bill of materials
AfterInsert and AfterDeleteConfirm of the form. This works very well. I'd
like to modify it to allow the user to change the sequence of the lines.
For example, they may add a new row (line 10 let's say) and want it inserted
between between lines 5 and 6. I'd like to allow the users to type 6 in the
line number field of the new row and have the code renumber the old 6
through 9 automatically. I have it working by calling this code in the
AfterUpdate of the LineNo field.

The problem is I need it to work in both directions. It should allow the
users to either increase or decrease the line number and renumber the other
lines accordingly. Surely someone as done this before.

Thanks in advance,
Josh

Original Code:

Private Sub RenumberLines_ORIG()
On Error GoTo Err_Sub

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant

Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
DoCmd.Hourglass True

varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)
rst.MoveFirst

Do Until rst.EOF
rst.Edit
intLineNo = intLineNo + 1
rst!LineNo = intLineNo
rst.Update
rst.MoveNext
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop

rst.Close
varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Exit_Sub:
Exit Sub
Err_Sub::
MsgBox Err.description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Resume Exit_Sub
End Sub


My Code:

Public Sub RenumberLines(Optional intOld As Integer, Optional intNew As
Integer)
On Error GoTo Renum_Error

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant
Dim tfStart As Boolean, tfUp As Boolean, intStart As Integer


Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
DoCmd.Hourglass True
varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)

rst.MoveFirst

If intNew > intOld Then tfUp = True

If tfUp Then
intLineNo = 0
intStart = intOld
tfStart = True
Else
intLineNo = 1
intStart = intNew
tfStart = False
End If

Do Until rst.EOF

If rst!LineNo = intStart Then
If Not tfStart Then
intLineNo = intLineNo + 1
tfStart = True
Else
tfStart = False
GoTo MoveNext
End If
Else
intLineNo = intLineNo + 1
End If

rst.Edit
rst!LineNo = intLineNo
rst.Update

MoveNext:
rst.MoveNext
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop

rst.Close
varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Me.Requery

Exit_Sub:
Exit Sub

Err_Sub:
MsgBox Err.Description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Exit Sub
End Sub

Private Sub LineNo_AfterUpdate()
Dim intOldVal As Integer
intOldVal = Me!LineNldValue
AutoSave
RenumberLines intOldVal, Me!LineNo
End Sub

 

[Klatuu]

So if a line gets deleted, you need to renumber to take out the deleted
number. Just run the code backwards

Actually, add an argument to the call to your sub so the code knows which
way to go. (Note I added a line to your code for safety reasons. If you do
not fully populate the recordset before you start the loop, it may not know
where EOF is.)

The way I have it coded is that you pass 1 if you want to move the numbers
up and -1 to move them down. Then I check the value of the argument to
determine what value to initialize the line number with. The rather than
adding using +1, I add using the argument variable so that it adds +1 to move
numbers up and -1 to move them down.

Private Sub RenumberLines_ORIG(intAdder as Integer)
On Error GoTo Err_Sub

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant

Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
If intLines = 0 Then
MsgBox "No Records To Process"
Exit Sub
End If

DoCmd.Hourglass True
varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)

With rst
.MoveLast
.MoveFirst

If intAdder = -1 Then
intLineNo = intLines
Else
intLineNo = 1
End If

Do Until .EOF
.Edit
!LineNo = intLineNo
.Update
.MoveNext
intLineNo = intLineNo + intAdder
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop

.Close
End With

varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Exit_Sub:
Exit Sub
Err_Sub::
MsgBox Err.description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Resume Exit_Sub
End Sub

 

[Peter Yang [MSFT]]

Hello Joshua ,

If I understand the issue correctly, you'd like to bel able to change
linenumber of BOM to a new value, and the other linenumber could change
accordingly in sequence.

For exmaple, you have line1 to line5

line1 a
line2 b
line3 c
line4 d
line5 e

If you want to to change line1 to line4, the result shall be

line1 b
line2 c
line3 d
line4 a
line5 e

If you you want to change from line4 to line2 (from the original table),
the result shall be

line1 a
line2 d
line3 b
line4 c

If I'm off-base, please let me know.


I have changed the code as follows and this seems works OK for me. Please
note I assume lineno is not the primary key and is not identity. You may
test this on your side to see if it meets the requirement.



Public Sub RenumberLines(Optional intOld As Integer, Optional intNew As
Integer)
'On Error GoTo Renum_Error

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant
Dim tfUp As Boolean, intStart, intend As Integer


Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
DoCmd.Hourglass True
varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)

rst.MoveFirst

If intNew > intOld Then
tfUp = True
End If
intLineNo = 1
If tfUp Then

intStart = intOld
intend = intNew

Else

intStart = intNew
intend = intOld

End If

Do Until intLineNo > intend

If intLineNo = intStart Then
If tfUp Then

rst.Edit
rst!LineNumber = intNew
rst.Update
Else
rst.Edit
rst!LineNumber = rst!LineNumber + 1
rst.Update
End If
Else
If intLineNo = intend Then
If tfUp Then
rst.Edit
rst!LineNumber = rst!LineNumber - 1
rst.Update
Else
rst.Edit
rst!LineNumber = intStart
rst.Update
End If
GoTo MoveNext

Else

If intLineNo > intStart Then
If tfUp Then
rst.Edit
rst!LineNumber = rst!LineNumber - 1
rst.Update

Else
rst.Edit
rst!LineNumber = rst!LineNumber + 1
rst.Update
End If

End If
End If

End If

MoveNext:
intLineNo = intLineNo + 1
rst.MoveNext
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop
rst.Close
varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Me.Requery

Exit_Sub:
Exit Sub

Err_Sub:
MsgBox Err.Description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Exit Sub
End Sub



Private Sub LineNo_AfterUpdate()
Dim intOldVal As Integer
intOldVal = Me!LineNumber.Value
'AutoSave
RenumberLines intOldVal, Me!LineNo
End Sub


Please note above code is just for your reference.

If you have further questions on the issue, please feel free to let's know.
Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Joshua A. Booker]

Thanks Peter...works great!

Josh

Hello Joshua ,

If I understand the issue correctly, you'd like to bel able to change
linenumber of BOM to a new value, and the other linenumber could change
accordingly in sequence.

For exmaple, you have line1 to line5

line1 a
line2 b
line3 c
line4 d
line5 e

If you want to to change line1 to line4, the result shall be

line1 b
line2 c
line3 d
line4 a
line5 e

If you you want to change from line4 to line2 (from the original table),
the result shall be

line1 a
line2 d
line3 b
line4 c

If I'm off-base, please let me know.


I have changed the code as follows and this seems works OK for me. Please
note I assume lineno is not the primary key and is not identity. You may
test this on your side to see if it meets the requirement.



Public Sub RenumberLines(Optional intOld As Integer, Optional intNew As
Integer)
'On Error GoTo Renum_Error

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant
Dim tfUp As Boolean, intStart, intend As Integer


Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
DoCmd.Hourglass True
varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)

rst.MoveFirst

If intNew > intOld Then
tfUp = True
End If
intLineNo = 1
If tfUp Then

intStart = intOld
intend = intNew

Else

intStart = intNew
intend = intOld

End If

Do Until intLineNo > intend

If intLineNo = intStart Then
If tfUp Then

rst.Edit
rst!LineNumber = intNew
rst.Update
Else
rst.Edit
rst!LineNumber = rst!LineNumber + 1
rst.Update
End If
Else
If intLineNo = intend Then
If tfUp Then
rst.Edit
rst!LineNumber = rst!LineNumber - 1
rst.Update
Else
rst.Edit
rst!LineNumber = intStart
rst.Update
End If
GoTo MoveNext

Else

If intLineNo > intStart Then
If tfUp Then
rst.Edit
rst!LineNumber = rst!LineNumber - 1
rst.Update

Else
rst.Edit
rst!LineNumber = rst!LineNumber + 1
rst.Update
End If

End If
End If

End If

MoveNext:
intLineNo = intLineNo + 1
rst.MoveNext
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop
rst.Close
varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Me.Requery

Exit_Sub:
Exit Sub

Err_Sub:
MsgBox Err.Description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Exit Sub
End Sub



Private Sub LineNo_AfterUpdate()
Dim intOldVal As Integer
intOldVal = Me!LineNumber.Value
'AutoSave
RenumberLines intOldVal, Me!LineNo
End Sub


Please note above code is just for your reference.

If you have further questions on the issue, please feel free to let's
know.
Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Joshua A. Booker]

Hi Peter,

Thanks for your solution. It worked well for what I had described in my
post.

There are a few exceptions and other requirements that came up. Such as
that your code relys on the existing line numbers being accurate to begin
with. We found that some users are pasting records from one BOM to another
which pastes the line numbers with it so the code failed when duplicate
line numbers were found. Also, I run this code to renumber the lines when a
record is deleted so I had to handle the situation when the intOld and
intNew arguments were missing.

I modified what you sent and here is my solution that works...so far that
is.

Thanks for pointing me in the right direction,
Josh

Public Sub RenumberLines(Optional intOld As Integer, Optional intNew As
Integer)
On Error GoTo Err_Sub

Dim rst As Recordset, intLineNo As Integer, intLines As Integer, varRet As
Variant
Dim tfUp As Boolean, intStart, intEnd As Integer, intUpdNo As Integer

Set rst = Me.RecordsetClone
intLines = Me.RecordsetClone.RecordCount
DoCmd.Hourglass True
varRet = SysCmd(acSysCmdInitMeter, "Renumbering lines..", intLines)

rst.MoveFirst

If intNew > intOld Then
tfUp = True
End If

intLineNo = 1

If tfUp Then
intStart = intOld
intEnd = intNew
Else
intStart = intNew
intEnd = intOld
End If

Debug.Print "Old: " & intOld, "New: " & intNew, "Start: " & intStart, "End:
" & intEnd, "tfUp: " & tfUp
Debug.Print "rstNo", "intLineNo", "intUpdNo"

Do Until rst.EOF

If intStart = 0 Then
intUpdNo = intLineNo
Else
Select Case intLineNo
Case Is = intStart
If tfUp Then
intUpdNo = intNew
Else
intUpdNo = intLineNo + 1
End If
GoTo EndSelect
Case Is = intEnd
If tfUp Then
intUpdNo = intLineNo - 1
Else
intUpdNo = intStart
End If
GoTo EndSelect
Case intStart + 1 To intEnd - 1 'between start and end
If tfUp Then
intUpdNo = intLineNo - 1
Else
intUpdNo = intLineNo + 1
End If
Case Else '< start or > end
intUpdNo = intLineNo
EndSelect:
End Select
End If

Debug.Print rst!LineNo, intLineNo, intUpdNo

rst.Edit
rst!LineNo = intUpdNo
rst.Update

MoveNext:
intLineNo = intLineNo + 1
rst.MoveNext
varRet = SysCmd(acSysCmdUpdateMeter, intLineNo)
Loop

On Error Resume Next
Me.Requery

Dim stFind As String

If intOld = 0 Then
stFind = "LineNo=" & intLineNo - 1
Else
stFind = "LineNo=" & intNew
End If

rst.FindFirst stFind
If Not rst.NoMatch Then
Me.bookmark = rst.bookmark
End If

rst.Close
varRet = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

Exit_Sub:
Exit Sub

Err_Sub:
MsgBox Err.Description
DoCmd.Hourglass False
varRet = SysCmd(acSysCmdClearStatus)
Exit Sub
End Sub

 

[Peter Yang [MSFT]]

Hello Joshua,

Thank you for your sharing! Your experience shall certainly benefit the
community.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================