How do I use buttons to reorder a list of tasks?

[Seth Schrock]

I have a form that I use to track jobs. Each job as a list of tasks for which I use a subform in Data Sheet view. I would like to be able to have buttons that can change the order of the tasks. All I really need are "Move Up" and "Move Down" buttons, but "Move to Top" and "Move to Bottom" would be nice. I have a "TaskOrder" field which I can populate manually, but when you want to move a task up five levels it gets hard to change all of the numbers. I'm guessing that I would need an update query to change the values and then requery the form, but I can't figure out what logic to use.

[Rabbit]

Just get the two unique ids of the rows you want to move and update them.

[Seth Schrock]

How do I get the PK of the other record? Getting the PK of the current record is easy. It is just the PK of the other record that I don't know how to do.

[Rabbit]

That depends on data structure.

[Seth Schrock]

Here is the query that I have supporting the form:

SELECT TaskID, Task, TaskComplete, JobID, TaskOrder
FROM tblTask
ORDER BY TaskOrder

TaskID is the PK
Task is Text
TaskComplete is Yes/No
JobID is the Foreign Key
TaskOrder is a Number field and is what needs changed.

Is that all you need?

[twinnyfo]

Seth,

How about code similar to this. This sends the current task order to the function and updates the table.

Option Compare Database
Option Explicit
 
Private Function MoveUp(intCurrent As Integer)
On Error GoTo EH
    Dim db1 As Database
    Dim rst1 As Recordset
    Dim db2 As Database
    Dim rst2 As Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder = " & intCurrent & ";"
    Set db1 = CurrentDb()
    Set rst1 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder = " & intCurrent - 1 & ";"
    Set db2 = CurrentDb()
    Set rst2 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    With rst1
        .Edit
        !TaskOrder = rst1!TaskOrder - 1
        .Update
    End With
    With rst2
        .Edit
        !TaskOrder = rst2!TaskOrder + 1
        .Update
    End With
    rst1.Close
    rst2.Close
    db1.Close
    db2.Close
    Exit Function 
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Function 
End Function 
 

With only a few mods, you could move the task down the order or move it to the top.

Let me know if this helps......

[Seth Schrock]

BINGO! Now, what would be the "few mods" that would make it so that I can move to top? I think that I could make the mathamatical changes to switch the two records from the middle to the top and vice versa, but how to move, say number 6, to number 1, the previous number 1 to number 2, 2 to 3, 3 to 4, 4 to 5, and 5 to 6 is what I'm not sure of.

[twinnyfo]

Seth,

Your db2/rst2 would include ALL records EXCEPT the target record. Then set your target record to 1, and then cycle through all the other records, setting their values to 2, 3, 4, etc.

Do the opposite for moving to the end (you need to do all the record counting in the code.)

[Seth Schrock]

Okay, here is that I tried out. It does work, but I want to make sure that I'm doing things properly.

Function MoveFirst(intCurrent As Integer)
On Error GoTo EH
    Dim db1 As Database
    Dim rst1 As Recordset
    Dim db2 As Database
    Dim rst2 As Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder = " & intCurrent & ";"
    Set db1 = CurrentDb()
    Set rst1 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder <> " & intCurrent & " AND TaskOrder <" & intCurrent
    Set db2 = CurrentDb()
    Set rst2 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    rst2.MoveLast
 
    With rst1
        .Edit
        !TaskOrder = 1
        .Update
    End With
 
    Do While intCurrent <> 1
        With rst2
            .Edit
            !TaskOrder = intCurrent
            .Update
            .MovePrevious
        End With
        intCurrent = intCurrent - 1
    Loop
 
    rst1.Close
    rst2.Close
    db1.Close
    db2.Close
    Exit Function
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Function
End Function
 

Also, is there a way to make it so that the record that I'm moving remains the one selected? Currently, in both the MoveUp and MoveFirst functions, if I select the fifth record when I click the button, the fifth record remains highlighted. I'm not sure if I need to make this a separate question or not.

[Seth Schrock]

Also, I just noticed that I will get an error if I click either the Move to First or Move Up buttons when the selected task is already = 1. I would assume that the reverse direction will have the same problem. I have solved the Move First and Move Up buttons by checking the value of intCurrent to see if it equals 1. If it does, then the function isn't run. My problem is, how do I check to see if it is the lowest order? Would I check for EOF or use a Do While Not is EOF loop?

[twinnyfo]

Seth,

Just a few minor tweaks, to make it work properly.... and to maintain good coding practices....

Function MoveFirst(intCurrent As Integer) 
On Error GoTo EH 
    Dim db1 As Database 
    Dim rst1 As Recordset 
    Dim db2 As Database 
    Dim rst2 As Recordset 
    Dim strSQL As String 
    Dim intRecords as Integer
    Dim intCounter as Integer
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder = " & intCurrent & ";" 
    Set db1 = CurrentDb() 
    Set rst1 = db1.OpenRecordset(strSQL, dbOpenDynaset) 
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder <> " & intCurrent & " ORDER BY TaskOrder;"
    'Need to sort the list by the current TaskOrder
    'No need to have any other criteria
    Set db2 = CurrentDb() 
    Set rst2 = db2.OpenRecordset(strSQL, dbOpenDynaset)
    'I had db1 in my original code..... (Sorry), although not sure it would matter....
    If Not rst2.EOF Then
        rst2.MoveLast
        intRecords = rst2.RecordCount
        rst2.MoveFirst
    Else
        intRecords = 0
    End If
    'Good practice to always double check for an empty recordset before we count the records!
 
    With rst1 
        .Edit 
        !TaskOrder = 1 
        .Update 
    End With 
 
    'This code updates all the other tasks from 2 to x
    For intCounter = 1 to intRecords 
        With rst2
            .Edit 
            !TaskOrder = intCounter + 1
            .Update 
            .MoveNext
        End With 
    Next intCounter
 
    rst1.Close 
    rst2.Close 
    db1.Close 
    db2.Close 
    Exit Function 
EH: 
    MsgBox Err.Number & " " & Err.Description 
    Exit Function 
End Function
 

I hope these tweaks help you out.....

[Seth Schrock]

I think that I understand most of the tweaks, however I do have a question about the criteria in the second query. Lets say I have 10 tasks, and I'm moving task 5 to be number 1. If I don't have the TaskOrder < intCurrent criteria, then it would pull 1-4, 6-10 in that query. Wouldn't that cause a problem? I don't see anything in the code that would make it not touch 6-10. Am I missing something?

[twinnyfo]

Concerning your previous post, I would use a the DMax function, to see the highest value of TaskOrder in the table. Then compare the values.

Concerning your follow-up question, remember if your current task is number 5, yes, it might make some sense to limit to only those records less than the current task. If you have a bunch of records, it will reduce the time it takes to iterate through those records. Wise choice and good catch! My code would be redundant and just make 6-10 equal to 6-10. I very gladly stand corrected!

[twinnyfo]

In fact, that should be the ONLY criteria for the SQL statement:

    strSQL = "SELECT * FROM tblTask WHERE TaskOrder < " & intCurrent & ";"  
 

[Seth Schrock]

Duh... At least I'm not the only one who didn't catch that the first time.

Also, on the DMax() function, does it return an integer or string? I have declared intLastNumber as integer and then did DMax for TaskOrder in rst2 and I'm getting an data type mismatch error on that line. Here is the code (line 18 is where the DMax() is):

Function MoveLast(intCurrent As Integer)
On Error GoTo EH
    Dim db1 As Database
    Dim rst1 As Recordset
    Dim db2 As Database
    Dim rst2 As Recordset
    Dim strSQL As String
    Dim intRecords As Integer
    Dim intCounter As Integer
    Dim intLastNumber As Integer
 
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder = " & intCurrent & ";"
    Set db1 = CurrentDb()
    Set rst1 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    strSQL = "SELECT * FROM tblTask WHERE TaskOrder >" & intCurrent & " ORDER BY TaskOrder"
    Set db2 = CurrentDb()
    Set rst2 = db2.OpenRecordset(strSQL, dbOpenDynaset)
    intLastNumber = DMax("TaskOrder", rst2)
 
    If intCurrent <> intLastNumber Then
        If Not rst2.EOF Then
            rst2.MoveLast
            intRecords = rst2.RecordCount
            rst2.MoveFirst
        Else
            intRecords = 0
        End If
 
        With rst1
            .Edit
            !TaskOrder = intRecords
            .Update
        End With
 
        For intCounter = intCurrent To intRecords
            With rst2
                .Edit
                !TaskOrder = intCounter + 1
                .Update
                .MoveNext
            End With
 
        Next intCounter
    Else
        MsgBox ("Task cannot be moved to a lower order")
    End If
 
 
    rst1.Close
    rst2.Close
    db1.Close
    db2.Close
    Exit Function
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Function
End Function

[twinnyfo]

To use DMax(), use the following syntax:

intLastNumber = DMax("[TaskOrder]", "tblTask")
 

It should only be looking for numeric values. Unless you expect hundreds of thousands of tasks, TaskOrder should have a data type of integer (not a long integer).

[Seth Schrock]

Okay, one last issue. When I click the button, it says "no current record", but it does work. Here is my code:

Function MoveLast(intCurrent As Integer, intJobID As Integer)
On Error GoTo EH
    Dim db1 As Database
    Dim rst1 As Recordset
    Dim db2 As Database
    Dim rst2 As Recordset
    Dim strSQL As String
    Dim intRecords As Integer
    Dim intCounter As Integer
    Dim intLastNumber As Integer
 
    strSQL = "SELECT * FROM tblTask WHERE JobID = " & intJobID & "AND TaskOrder = " & intCurrent & ";"
    Set db1 = CurrentDb()
    Set rst1 = db1.OpenRecordset(strSQL, dbOpenDynaset)
    strSQL = "SELECT * FROM tblTask WHERE JobID = " & intJobID & "AND TaskOrder >" & intCurrent & " ORDER BY TaskOrder"
    Set db2 = CurrentDb()
    Set rst2 = db2.OpenRecordset(strSQL, dbOpenDynaset)
    intLastNumber = DMax("TaskOrder", "qryTaskReorder")
 
    If intCurrent <> intLastNumber Then
        If Not rst2.EOF Then
            rst2.MoveLast
            intRecords = rst2.RecordCount
            rst2.MoveFirst
        Else
            intRecords = 0
        End If
 
        With rst1
            .Edit
            !TaskOrder = intLastNumber
            .Update
        End With
 
        For intCounter = intCurrent To intLastNumber
            With rst2
                .Edit
                !TaskOrder = intCounter
                .Update
                .MoveNext
            End With
 
        Next intCounter
    Else
        MsgBox ("Task cannot be moved to a lower order")
    End If
 
 
    rst1.Close
    rst2.Close
    db1.Close
    db2.Close
    Exit Function
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Function
End Function
 

I think that it is because of line 40 trying to go to the next record the last time and there is no record to go to. The reorder works, I just need to figure out a way to keep it from going to the next record the last time. Would it work to surround line 40 with the following?

If Not rst2.EOF Then
.MoveNext
End If

[Seth Schrock]

Okay. I figured it out. I added the following line 34 of Post #17

intLastNumber = intLastNumber - 1

This caused the next loop to run one less time. Thanks Twinnyfo for all your help.