Cascading Numbers in a Table

SicNeMeSiS

Registered User.
Local time
Today, 03:25
Joined
Sep 3, 2008
Messages
15
Trying to figure out how to edit a list of numbers in a table when adding a new item. For instance, a table for a checklist, each step in the check list has its own check list id number which is not set as a primary key. Say i have steps 1 through 10 and i wanted to add a new step as step number 5 which already exists as a present step. My goal is to have that number change when I add the new step 5 to step 6, and step 6 to step seven and so on and so forth untill i now have steps 1 through 11. If this is possible any help is appreciated. thank you.

Sic
 
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:
 

Users who are viewing this thread

Back
Top Bottom