Its a bit long winded but heres how to do it.
Ok your list has 10 steps 1-10
You want to add a new step at position 5 so everything above 5 is incremented by one to give you 11 steps
And lets assume you keep these steps in a table with the step number and description
Create a function call InsertStep with a parameter
Public Function InsertStep(Position As Integer,Description As String)
First Dim an Array
Dim nCount As Integer
Dim nIndex As Integer
nIndex = 0
Next Open up the Steps table as a recordset
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From Steps Order By StepNo")
Rs.MoveLast
nCount = Rs.RecordCount
Rs.MoveFirst
Dim TmpArray(nCount-1,1) ' set the upper bounderies of the array
Do Until.EOF
If Position <> nIndex Then
TmpArray(nIndex,0) = Rs("StepNo")
TmpArray(nIndex,1) = Rs("StepDesc")
Else
TmpArray(nIndex,0) = Position
TmpArray(nIndex,1) = Description
End If
nIndex = nIndex + 1
Rs.MoveNext
Loop
Rs.Close
What we have so far is to place all the original steps into an array along with new step at the correct insertion point.
What we need to do next is to update the Steps table
It is far easier to delete the whole contents and re append from the array. So.
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From Steps"
DoCmd.SetWarings True
Next we re populate the table using the array contents
Set Rs = CurrentDb.OpenRecordset("Steps")
For nIndex = 0 To nCount -1
Rs.AddNew
Rs("StepNo") = nIndex +1 'Incremental number starting at 1
Rs("StepDesc") = TmpArray(nIndex,1) 'description relating to the step
Rs.Update
Next
Rs.Close
Set Rs = Nothing
If we then look at the steps table you will see the new step in the correct position
This is all aircode and has not been tested.
CodeMaster: