Renumber records when one number changed (1 Viewer)

[kilburfi]

I have a table which has 2 fields 1) Project_Priority_Number and 2) Previous_Priority_Number. If there were 100 records these would be numbered 1-100 in the order that the user originally sets the priority (this number is in addition to the record ID number). I have created a form with code that moves the Project_Priority_Number to the Previous_Priority_Number and then shows the Project_Priority_Number as blank and displays all of the records.

The user can then set new priority numbers in the blank column. Say they choose to make the old priority number 4 the new number 3 and priority 27 now becomes say 2, etc. I want the user to press a button that re-numbers the remaining ones based on their old position + or - 1 (basically to fill the gaps but based on their previous positions). I understand how to renumber if one is deleted but I don't know how to be more specific and re-number based both on their previous priority number and whether something else is now set to replace that.

For Example (the following numbers need to re-order based on previous priority 4 becomming the new priority 1 and the previous number 8 becomming 3 - so the previous 1 becomes 2 and then everything after the new 3 moves forward 1):

PROJECT PRIORITY NUMBER PREVIOUS PRIORITY NUMBER
1
2
3
1 4
5
6
7
3 8
9
10

 

[vbaInet]

1. How many users do you have?
2. How many people will be able to set priorities at any given time?
3. If more than one, have you catered for this?

 

[kilburfi]

Only 1 person will be able to set the priorities and it will be done following a single review meeting - so a one-off exercise possibly fortnightly or monthly.

 

[kilburfi]

The database will have password controls and no other user will be able to access the priority fields so it will be a password protected single button that provides this facility that will only be available to one person.

 

[jdraw]

Do you have to record when the priority changed (cross reference to mtg/person etc)? How many times could the priority change?

Do these priority changes occur while users are actively using this database?

 

[vbaInet]

Is this scenario possible?
1
2
3 <--- Change priority to 1
4 2
5 3
6 4

 

[gemma-the-husky]

The easiest way is not to renumber in integers, but to use real numbers

Given
1
2
3
4
5

Then You can reorder item 5 to appear between 2 and 3, by setting it to 2.5

 

[kilburfi]

JDRAW - I don't need to log changes as I was only planning on letting one user do this and it will be a task they will undertake perhaps once a fortnight or so. I would imagine the database will need locking to other users to do this.

 

[kilburfi]

VBANET - I don't think your way (copied below) will work because they wouldn't start re-numbering after the point that priority 3 was changed to 1 but also above that point (because the current 1 and 2 will also move to 2 and 3.

Also it may not be one number change (which should be quite simple) but there may be a dozen changes within a block of, say, 100 tasks. I was thinking of a loop that keeps going back to the top everytime it finds a number out of sync that then stops when it gets to the last record. Does anyone think that would work or is that too complicated?

Is this scenario possible?
1
2
3 <--- Change priority to 1
4 2
5 3
6 4

 

[vbaInet]

I wasn't proposing, I was merely asking if it was a possibility for clarity.

So if more than one record's priority can be changed within a block, how would the loop know which records were changed? Is there a field to indicate this?

It would appear that this is possible:

Rank    New Rank    FieldA
1                     A
2                     B
3         6           C
4                     D
5         1           E
6                     F

Now how will the records be re-ordered?

 

[kilburfi]

Thanks for the response. Yes there will be a field that is for the new number. Say field 1 has the numbers 1 - 100 and field 2 is used just to designate a new number (where relevant). So in field 1 the number might be 4 but in field 2 the new number would be 1. If that were the only change then the remaining fields would be left blank. I am trying to think through this logically (prior to coding) as I am sure to miss something (that you may have already thought of) which might be an issue.

So the code would run through the first three records and not do anything but then comes to 4 which gets re-numbered 1 so current 1 would become 2 and so on. As I said this would be ok if just 1 change but for many it would need to keep going back and starting again till all of field 2 updates had been incorporated. Also for every update you would end up with two records with the same priority number as the new 1 is replacing an existing 1.

As you can tell, even thinking about this is giving me a headache!

 

[vbaInet]

Ok, so how will the above be re-numbered? Show it like I've done.

 

[kilburfi]

The best way to understand my confusion is to sit with a piece of paper and write the numbers 1 to 15 in one column and then in the next column write 1 adjacent to 7, 2 adjacent to 13 and 3 adjacent to 15 and then try to think how to number them all based on the new priorities for the three records in column 2. This is the point where my brain has given up because although 7 becomes 1, the existing 1 doesn't become 2 because existing 13 becomes 2 and 15 becomes 3 - so actually the existing number 1 would, after all updates become 4 and then the gaps between 7-8 and 13-14 would also need closing with the rest of the numbers.

 

[vbaInet]

You ask a question, we come up with solutions! You need to rethink what you're trying to do.

 

[kilburfi]

OK I think I have got it! Three fields Current, Proposed and New. The New field is created by looking at the current number (starting at 1) and seeing if there is anything in the corresponding priority and, if not it looks through all records in the Proposed field to see if there is another number 1 if not it adds 1 to the number and looks again, an so on until it gets a free number and so on and so on with all records until they are numbered correctly. So all changed numbers (in proposed) get accounted for and the remaining ones (if not changed by the proposed) get the next available number. I have just tried it in Excel and it works. Thanks for making me think straight!

 

[vbaInet]

Those two extra fields are really not necessary. Let me help you think further:

1. One new Yes/No or Byte field called "NewOrder" which will indicate which record's order has been manually changed
2. In the After Update event of the "Project Priority" textbox, you will tick the "NewOrder" field

...from then on the project tasks can be re-ordered. Makes sense?

 

[kilburfi]

Yes makes perfect sense and I did just think I didn't need the extra field.

 

[RainLover]

The way I am reading this there are more holes in it than something that does not have as many holes in it.

What is the Rule. So far I can only see explanations. If this is new then that is old.

What is needed is a logical rule that cannot have any exceptions except those within the rule.

e.g If Column One equals 4 then add 3 then do something.

I am sure this is as clear as mud to you because it is to me.

Perhaps I should get out of here.

 

[vbaInet]

But then here comes the same question as before. If I changed the following values (New Rank), how will the records (Rank and FieldA) be ranked?

[COLOR="Red"]Rank[/COLOR]    New Rank    [COLOR="red"]FieldA[/COLOR]
1                     A
2                     B
3         6           C
4                     D
5         1           E
6                     F

 

[gemma-the-husky]

as I said

1 A
2 B
3 C
4 D
5 E
6 F

change 6 to 2.5

1 A
2 B
2.5 F
3 C
4 D
5 E

now if you must, renumber everything with a simple process
1 A
2 B
3 F
4 C
5 D
6 E