title: Delete record out of form and update the field numbers
body: <p>In the form I am working with it has a list of steps with the step number in there. Users can move the steps around using two buttons. If the want to move a step, they click on the record selector and then press a button. If they pressed the down button, it will move the step down one and change the number to what it's supposed to be and the same goes for the record that moved up the list. However, there are times we need to delete a step. The problem with this is that it doesn't renumber the fields and needs to be done manually. </p>
  
  <p>I have tried modifying the down button code and it will move the record down one, but I run into the original problem. In essence, I need it to move to the bottom of the list and renumber the rest while doing so. I have tried using the code with a Do and loop command, but it will just number the step numbers but won't move the step and addition, it will just increase the count. For example, if I were to move step 3 out 7, it would read 1,2,4,5,6,7,8. And this will continue everytime I press the button. If I select a record and press the down button repeatedly it will move the record down without a problem. </p>
  
  <p>This is the code for the button down. </p>
  
  <pre><code>On Error GoTo ErrHandler
      If Me.frm_Steps_Listing.Form.CurrentRecord = Me.frm_Steps_Listing.Form.RecordsetClone.RecordCount Then
          MsgBox "This record cannot move down anu more!"
          Exit Sub
      End If
      Call MoveCurrentRecord(Me.frm_Steps_Listing.Form, 1, "tbl_Steps", "Step_ID", "Step")
  Exit_cmdMoveUp:
      Exit Sub
  ErrHandler:
      MsgBox Error$
      Resume Exit_cmdMoveUp
  
  Public Sub MoveCurrentRecord(Current_Form As Form, intMove As Integer, Current_Table As String, Primary_Key As String, Sequence_Field As String)
      Dim booSomethingMoved As Boolean
      Dim lngCurrentPosition As Long
      Dim lngNewPosition As Long
      Dim rstComponents As Recordset
      Dim rstTable As Recordset
      Dim lngCurrentRecordID As Long
      Set rstComponents = Current_Form.RecordsetClone
      Set rstTable = CurrentDb.OpenRecordset(Current_Table, dbOpenDynaset)
      booSomethingMoved = False
      'If there are no records then exit
      If rstComponents.RecordCount &lt;&gt; 0 Then
          With rstComponents
              'Set the current record of the clone to the currently selected record
              .Bookmark = Current_Form.Bookmark
              lngCurrentRecordID = .Fields(Primary_Key)
              lngCurrentPosition = .Fields(Sequence_Field)
              If intMove = 1 Then
                  .MovePrevious
                  If Not .BOF Then
                      lngNewPosition = .Fields(Sequence_Field)
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; lngCurrentRecordID
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngNewPosition
                      rstTable.Update
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; .Fields(Primary_Key)
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngCurrentPosition
                      rstTable.Update
                      booSomethingMoved = True
                  End If
              End If
  </code></pre>
  
  <p>For the delete button the code is the same but I use this to enter. </p>
  
  <pre><code>Call MoveCurrentRecord(Me.frm_Steps_Listing.Form, 1, "tbl_Steps", "Step_ID", "Step")
  </code></pre>
  
  <p>The initial portion of the Public sub is the same except for when I get to the section where I'm calling. </p>
  
  <pre><code>If intMove = 2 Then
  
                     Do While Not .EOF
                      .MoveNext
  
  
  
                      lngNewPosition = .Fields(Sequence_Field)
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; lngCurrentRecordID
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngNewPosition
                      rstTable.Update
  
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; .Fields(Primary_Key)
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngCurrentPosition + 1
                      rstTable.Update
                      booSomethingMoved = True
                      .MoveNext
  
  
                   Exit Do
                  Loop
  
              End If
  </code></pre>
  
  <p>I'm really not sure why the Do command doesn't work, but it will work as an If command. I appreciate any feedback on this. </p>
  
  <p>After looking at the suggestion made I was able to come up with the solution. </p>
  
  <pre><code>If intMove = 2 Then
                      Dim i As Integer
                      i = 0
                     Do Until .EOF
                      .MoveNext
                     If Not .EOF Then
  
                      lngNewPosition = .Fields(Sequence_Field)
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; lngCurrentRecordID
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngNewPosition
                      rstTable.Update
  
                      rstTable.FindFirst "[" &amp; Primary_Key &amp; "] = " &amp; .Fields(Primary_Key)
                      rstTable.Edit
                      rstTable.Fields(Sequence_Field) = lngCurrentPosition + i
                      rstTable.Update
                      booSomethingMoved = True
  
                      i = i + 1
                    End If
                 ' Exit Do
             Loop
              End If
  
  
              'End If
  </code></pre>