Re order a set of records that has an index

Question

Hello - 

Access 2010, win7.

I have a dataset with a key field as number. 

I design it to get always the next number.

I want to allow my users to change the order of the records when they choose to do so, without the need to rewrite a whole bunch of index.

any one has an idea about how to do it ?

Edit... 

The Situation is like this:

There a set of records that the user creates. the records has an index 1-1000...

the index is editable (not a autonumber field). the problem is, that the user wants to edit the index to a different index location so the sorting order will be set accordingly

I want to set the other index according to the user changes

1,2,3,4,5,6,7,8,9,10

the number 5 is change to 9... and i want the whole set of indexs will be set according to the new changes.

Hope i made it clearer now :-)

Thanks

Alu

---

Alu_g

Answer 1

How about running a couple of simple update statements:

UPDATE TableName SET [RowIndex] = [RowIndex] + 1 WHERE [RowIndex] >= StartNumber
UPDATE TableName SET [RowIndex] = StartNumber WHERE PrimaryKeyCol = PrimaryKeyOfRowToChange

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Are you viewing the records directly in the table?   If so that is wrong - use a query, sorted.

---

Build a little, test a little

Answer 3

Hello -

For some reasone (I still don't know why) i try what you suggested... and the update wasn't succesfull

It has update only a set of values, and some of them were left as they were.

Also - there is another issue - that i need to update only a set of data - and not all of it. caues from a certain index there is no need to add+1 to the index...

Herein the solution

---

Alu_g

Answer 4

Hello -

For some reasone (I still don't know why) i try what you suggested... and the update wasn't succesfull

It has update only a set of values, and some of them were left as they were.

Also - there is another issue - that i need to update only a set of data - and not all of it. caues from a certain index there is no need to add+1 to the index...

Herein the solution

---

Alu_g

Which UPDATE did not work? Did you get an error?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

Also - there is another issue - that i need to update only a set of data - and not all of it. caues from a certain index there is no need to add+1 to the index...

Hi Alu_g,

It depends a little on the condition that the Rowindex has a key that must be unique or not.

In general you can take the following steps. Assume you want to convert to 5 to 9, thus lownr = 5 and highnr = 9.

"UPDATE TableName SET Rowindex = 0 WHERE Rowindex = " & lownr
"UPDATE TableName SET Rowindex = Rowindex - 1 WHERE Rowindex > " & lownr & " AND Rowindex <= " & highnr
"UPDATE TableName SET Rowindex = " & highnr & " WHERE Rowindex = 0"

 

I have generalized this kind of functionality with parameters TableName, FieldName, lownr and highnr. I only need to give such a control a "property" of "Autosequence", to have the Rowindex automatically adjusted after change.

Imb.