Manual Ordering. (1 Viewer)

[pleeson]

I think this may be impossible but I am hoping it isn't

Have any of come across a way to Manually order a continuous form.

The way I would see it working is to have a column in the table called sort_order and two buttons on the form called move up and move down. Once clicked the sort_order value would change thus changing the order on the form.

I should imagine it would be alot more complicated than my brief description, in fact it may like I said be impossible.

If any of you have an idea how to achieve this I will be very grateful

Cheers

Paul

 

[lanato]

I hope the next codes can help you:
Private Sub MoveDown_Click()
Me.OrderBy = "[sort_order] desc"
Me.OrderByOn = True
End Sub

Private Sub MoveUp_Click()
Me.OrderBy = "[sort_order]"
Me.OrderByOn = True
End Sub

 

[godofhell]

You will have to do some sort of loop through the record set so that it obtains the current value of the field, then you will make your buttons do 1 of 2 things.
If moving record up, you will have to change the value of the field above to -1 until you get to the 1st record, if you are moving it down, then you will have to change the value of the field to + 1 until you get to the last record. Someone correct the code below since I did not try it and simply pulled it on a quick thought.

Sub moveUp_onClick()
Do Until SortNumber = 1
SortNumber = Sortnumber -1
Loop
End Sub

SubMoveDown_onClick()
Do Until SortNumber = Recorset.Count -1
SortNumber = SortNumer + 1
Loop
End Sub

 

[Rich]

DoCmd.RunCommand acCmdSaveRecord
SomeControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

 

[pleeson]

Thanks all, I have tried all the above in turn with no luck.

God, This sort of works but gives a value of 1 to the Project_Order no matter which record you move up. Thus you end up with records with the same value of 1.

Ian, I get an error on yours but I am looking into that. It could be that I am trying to apply this control to a subform?

Rich, Would that only work on an existing set of numbers. So the code is basically setting focus on Project_Order and then orders it either Ass or Desc.

Thanks again all. Any further ideas? I am searching the depths of the internet so if I come up with an answer I will post it here.

Paul

 

[Albert2]

It's possible in case the field has no unique values requirements, as below. Tot explain what it does: In this case letters are title names and numbers position A=1,B=2.C=3,D=4,E=5, user modifies the A=3 and the rest of titles change to B=1,C=2,A=3,D=4 y E=5.
But someone know how to apply the following code in case the field need unique values x ex. to link it to another table.

Table:
ChapterID, ChapterOrder(Num)

Update Query: UpdateChapterOrder:
UPDATE Chapters AS c SET c.ChapterOrder = IIf([ChapterOrder]=[NewOrder],[NewOrder],[ChapterOrder]+1)
WHERE [ChapterID] = [ID] AND [ChapterOrder] <> [NewOrder];

VB

Private Sub ChapterOrder_AfterUpdate()
    Dim db As DAO.Database
    Dim rsChapters As DAO.Recordset
    Dim strSQL As String
    Dim ChapterID As Long
    Dim newOrder As Long
    Dim existingCount As Long
 
    On Error GoTo ErrorHandler
 
    Set db = CurrentDb
    Set rsChapters = db.OpenRecordset("Chapters", dbOpenDynaset)
 
    ChapterID = Me.ChapterID ' Obtain Id Chapte modify it
    newOrder = Me.ChapterOrder ' Obtain new order value
 
    ' Verify if the new order value exist in other fields
    existingCount = DCount("*", "Chapters", "ChapterOrder = " & newOrder & " AND ChapterID <> " & ChapterID)
 
    If existingCount = 0 Then ' It doen't exist duplicate, just update value
        strSQL = "UPDATE Chapters SET ChapterOrder = " & newOrder & " WHERE ChapterID = " & ChapterID
        db.Execute strSQL
    Else ' Exist value, reorganize values
        ' Increase highest superior values to give space to the new value
        strSQL = "UPDATE Chapters SET ChapterOrder = ChapterOrder + 1 WHERE ChapterOrder >= " & newOrder
        db.Execute strSQL
      
        ' Assign new value to the field ChapterOrder
        strSQL = "UPDATE Chapters SET ChapterOrder = " & newOrder & " WHERE ChapterID = " & ChapterID
        db.Execute strSQL
    End If
 
    rsChapters.Close
    Set rsChapters = Nothing
    Set db = Nothing
 
    ' Update form or list to show changes
    Me.Requery
 
    Exit Sub
 
ErrorHandler:
    MsgBox "Error: " & Err.Description, vbExclamation, "Update Error"
    On Error Resume Next
    rsChapters.Close
    Set rsChapters = Nothing
    Set db = Nothing
    Exit Sub
End Sub

 

[CJ_London]

@Albert2 - you do know you are responding to an 18 year old thread?

You appear to be asking a question, so strongly recommend you start your own thread. And clarify what you mean by ' in case the field need unique values x ex. to link it to another table.' Provide some example data as usually linking to another table implies non-unique values (if it was unique, there would be no records in the other table at this point to link to)

 

[Mike Krailo]

If the question is about the 18 year old thread then maybe this will help. Maintaining Custom Sort Order

 

[Albert2]

I just wanted to follow the related question without opening a new one. Thanks for your suggestions!

I'll do it!

 

[Pat Hartman]

I just wanted to follow the related question without opening a new one. Thanks for your suggestions!

Welcome
If you think an old thread has relevant information then post a link to it. Otherwise start a new thread. Old threads tend to have a lot of dead stuff in them and are lengthy. You don't want people to have to read all the old stuff unless it is really relevant to your question. Also, once a thread gets to be too lengthy, no one will bother reading it and you won't get the help you need.

and finally,
Please use the code tags tool when you post your code to keep the indenting intact.