none
Resequencing Project Priority List after User Changes Status of Project from Active to Completed RRS feed

  • Question

  • I have a Project Priority Application.  Each User Manager has their list of Projects and each project is prioritized with a number so if they have 10 active projects each project will have a number either 1-10.  If the user completes a project by changing the status of the project to Complete, they want all the remaining projects to resequence.  For example if the project that was prioritized as #3 get's completed than they want #4 to become 3, #5 to become 4 and so on etc.  So the priority will move up by 1.

    The second part will be to renumber the projects that are active when they change priority #10 in the Active List to #1 for example, they want the current #1 to become #2, #2 to become #3, and so on etc.  I wanted to concentrate on one part at a time because I suspect this part will be more difficult.  

    Sunday, August 12, 2018 2:53 PM

All replies

  • I would suggest that you change the numbering so that it increments by 10 rather than 1.  This will enable you to insert numbers within the sequence easily before renumbering the sequence, e.g. to insert a number between 10 and 20 you could insert 15.  With your example of making 10 the first priority you'd change it from 100 to zero or any negative number.  It would then be renumbered as 10 and all other numbers.  For display purposes  you'd set the ControlSource property of a control to, using my example below, =[PaymentIndex]/10 to show the values as a sequence incrementing by 1.

    To renumber the sequence, the following is an example of code in a bound form's module  which renumbers a set of regular payments from an account:

        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strRS As String
        Dim n As Integer
        
        Me.Dirty = False
        
        strSQL = "SELECT PaymentIndex FROM RegularPayments ORDER BY PaymentIndex"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        With rst
            .MoveLast
            n = 1
            .MoveFirst
            Do While Not .EOF
                .Edit
                .Fields("PaymentIndex") = n * 10
                .Update
                n = n + 1
                .MoveNext
            Loop
        End With
        Set rst = Nothing
                        
        Me.Requery

    Alternatively you could keep the increment of 1 and insert a fractional value, e.g. 1.5 between 1 and 2.  The column should be a single precision floating point number, not an integer.  The above code would then be amended to:

                .Fields("PaymentIndex") = n


    Ken Sheridan, Stafford, England


    Sunday, August 12, 2018 3:17 PM
  • 1. For example if the project that was prioritized as #3 get's completed than they want #4 to become 3, #5 to become 4 and so on etc.  So the priority will move up by 1.

    2. The second part will be to renumber the projects that are active when they change priority #10 in the Active List to #1 for example, they want the current #1 to become #2, #2 to become #3, and so on etc.  

    Hi Anna,

    1. An alternative way could be to run an Update query in the AfterUpdate event of the Status control. The current priority in this record is assigned to the variable  cur_prior. It would read something like:
        "UPDATE Prior_tbl SET Priority = Priority - 1 WHERE User_mgr = " & cur_user_mgr & " AND Priority > " & cur_prior.

    2. In the same way you could run an Update query in the AfterUpdate event of the Priority (new_prior is the new value, and old_prior the old value):
        "UPDATE Prior_tbl SET Priority = Priority + 1 WHERE User_mgr = " & cur_user_mgr & " AND Priority >= " & new_prior & " AND Priority < " & old_prior

    With the same systematics you can also devaluate priorties, by adapting the Update query.

    Imb.

    Sunday, August 12, 2018 4:58 PM