Auto numbers when priority change

[dim]

Hi,

I have a very basic application to manage the tasks.
In the main form "Tasks" one of the field is the priority (Order by) and I have this example:

Task Priority

B 1
A 2
C 3
E 4
D 5

If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:

Task Priority

C 1
B 2
A 3
E 4
D 5

There is a way to do this?
Thank You!

 

[RainLover]

If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:

You have shown what happens in one simple case. What we need is a Rule that can be translated into code.

Now that Rule cannot simply be If C becomes 1 then A becomes 2 and B becomes 3 etc.

Could you imaging what would happen if you had several thousand records. How does this get converted into a simple Rule written in English. Then we can look at converting that into code.

 

[namliam]

my suggestion is to change your priority from 1,2,3,4
to 10,20,30,40

Then people can shift them more easily into a proper order, then upon form close or button press you can "clean up" the order from 5,7,10,16,20 back to 10,20,30,40,50 ready for the next "shuffle"

** where I use 10,20 you can also use 100, 1000 or 10000000000 if you prefer.

 

[plog]

First, nothing is "automatic". Especially in Access which doesn't have triggers. That means you are going to need a form which captures the changing of a priority. Once you have that, you can use the below code to reorder your data:

Public Function set_Order(in_Task As String, in_NewPriority As Integer)
    ' rearranges all Tasks' Priorities based on task that is changing (in_Task) and its new priority (in_NewPriority)
    
    int_CurrentPriority = DLookup("[Priority]", "Tasks", "[Task]='" & in_Task & "'")
    ' gets current priority of the task that has been submitted for change
    
    str_SQL = "UPDATE Tasks SET Priority = Priority"
    ' SQL string that will be run to update priorities
    
    If int_CurrentPriority > in_NewPriority Then
    ' if priority of selected task is getting smaller, adds 1 to all those in between new position and old position
        str_SQL = str_SQL & " + 1 WHERE Priority>=" & in_NewPriority & " AND Priority<" & int_CurrentPriority & ";"
    End If
    If int_CurrentPriority < in_NewPriority Then
    ' if priority of selected task is getting larger, subtracts 1 from all those in between new position and old position
        str_SQL = str_SQL & " - 1 WHERE Priority<=" & in_NewPriority & " AND Priority>" & int_CurrentPriority & ";"
    End If
       
    DoCmd.RunSQL str_SQL
    ' runs SQL to change all those affected by changing priority
    
    str_SQL = "UPDATE Tasks SET Priority = " & in_NewPriority & " WHERE Task='" & in_Task & "';"
    DoCmd.RunSQL str_SQL
    ' changes Task's priority that is focus of change

set_Order = 0
End Function

You pass it the Task being changed and its new priority, it will do the rest. That function will work on good data. I didn't put any error handling into it, so if you pass it an invalid task it's going to blow up; if you have 2 tasks assigned the same priority, I'm not certain what's going to happen; if you pass it null data, god help us all.

 

[vbaInet]

I wonder if it's even necessary updating the entire table each time.

Can a user request to change the order from 3 to 2 or will it always be from an order (e.g. 3) to the first order (1). If you're always moving a task to the first order then you won't need to update the entire table.

 

[dim]

Thank you Plog, I'll try your code!