none
Re order a set of records that has an index RRS feed

  • 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


    • Edited by Alu_g Wednesday, May 10, 2017 9:24 AM
    Wednesday, May 10, 2017 6:58 AM

All replies

  • 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)

    Wednesday, May 10, 2017 12:07 PM
  • Are you viewing the records directly in the table?   If so that is wrong - use a query, sorted.

    Build a little, test a little


    Wednesday, May 10, 2017 5:30 PM
  • 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

    Thursday, May 11, 2017 2:48 PM
  • 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)

    Thursday, May 11, 2017 3:36 PM
  • 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.

    Thursday, May 11, 2017 3:39 PM