Renumber sequential numbers after update

[Dude]

Hey guys,

I hope someone could help me with this challenge (a challenge for me, since I only have basic knowledge of Access/VB).

I have a table with a field with a unique ID (ExerciseID), a field with custom numbers to determine the order of records (ExercisePos), and a few related fields.

What I'm trying to set up here is a way to automatically update the Position field (ExercisePos) correctly when a change has been made.
So change to a record could be:
- Record has been deleted
- Moved to a lower position
- Moved to a higher position

So after a record has been given a number, the code should renumber the field (by adding 1 to every record) starting from the record that currently holds that number and going to the end of the field. At least, that's the solution that seemed most logical to me.

I've gathered information from different sources including the following thread
https://www.accessforums.net/forms/r...79/index3.html

and have cooked up the following code and placed it in an After_Update Event:

Private Sub SortSeq()Dim Sequence As Long
Dim oldPos As Long
Dim newPos As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb()
Set rs = db.OpenRecordset("Exercises")


newPos = Me.ExercisePos.Value
oldPos = rs("ExercisePos")
Me.Requery


If newPos <> oldPos Then


With Me.Recordset
   Do While Not .EOF
      Sequence = Sequence + 1
      .Edit
      !ExercisePos = Sequence
      .Update
      .MoveNext
            
   Loop
   Me.Requery
End With
End If


Me.Requery
Set rs = Nothing
Set db = Nothing
End Sub

The code almost works.

However:
- Sometimes the new postion (newPos) is respected and the record preserves the ExercisePos that has been entered, but most of the time the record becomes ExercisePos + 1. Sometimes even ExercisePos -1 (when moving to a higher number). It seems really random to me at the moment.
- Trying to change the ExercisePos of a record to 1 is not possible. The record becomes 2.
- When changing an ExercisePos of a record that is currently 1, an error occurs (Run-time error 3021, no current record), which makes sense, but I really have no clue how to deal with it.

I'm fully aware of the fact that the current code is incomplete, but hopefully I'm going in the right direction.

After messing with it for over a week I realise that I need help of some real pro's.

So I hope someone can help me. It would be much appreciated!

[orange]

Can you tell readers in 4-5 lines of plain English WHAT the database / business is about?
If you had to answer someone who doesn't know database, or Access or You, who asked, What's the issue?
What would you say?
We only know what you tell us and it seem to be jargonese to me.

[John_G]

Hi -

when you open the recordset rs, with the Exercises table, and then execute oldPos = rs("ExercisePos"), you really have no way of knowing what oldPos will be, since you have no way of knowing wich record in the Exercises table rs is on when you open the recordset.

In your code, you Dim Sequence as Long, but then you don't initialize it to anything. Sequence always starts at 0 - it that what you really want to do?

You might be able to do this with one SQL statement:

SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where ExercisePos >= me!ExercisePos and ExerciseID <> " & me!ExerciseID.
db.execute SQL, dbfailonerror
me.requery

The last bit, in red, is required to prevent the current record (which has the new ExercisePos value) from being updated as well.

HTH

John

[Dude]

Can you tell readers in 4-5 lines of plain English WHAT the database / business is about?
If you had to answer someone who doesn't know database, or Access or You, who asked, What's the issue?
What would you say?
We only know what you tell us and it seem to be jargonese to me.

Sorry, if my OP wasn't clear, but the database is to keep track of content that is created for training software that has a certain amount of exercises.
I have 9 tables that are related and am setting up forms to be able to enter data more easily.
There is one table to keep track of the exercises name and the exercise position.
Because of several reasons, sometimes the position of an exercise in relation to other exercises needs to be changed.

This specific field I'm trying to control, which handles the record order/position/rank isn't related to any other field.
Basically, I just want to make sure that every record gets the right sequential number when their position has been changed.
So a position number will be altered manually and I want to get the rest of the field to adjust accordingly.

I made a very basic visualization of what I'm trying to achieve.



I hope this clears things up for you and future readers.

[Dude]

Hi -

when you open the recordset rs, with the Exercises table, and then execute oldPos = rs("ExercisePos"), you really have no way of knowing what oldPos will be, since you have no way of knowing wich record in the Exercises table rs is on when you open the recordset.

In your code, you Dim Sequence as Long, but then you don't initialize it to anything. Sequence always starts at 0 - it that what you really want to do?

You might be able to do this with one SQL statement:

SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where ExercisePos >= me!ExercisePos and ExerciseID <> " & me!ExerciseID.
db.execute SQL, dbfailonerror
me.requery

The last bit, in red, is required to prevent the current record (which has the new ExercisePos value) from being updated as well.

HTH

John

Hey John,

Thanks for your reply.

You are totally on point!
I asked myself the same questions, but I just didn't know how to answer those questions with the correct code.

Forgive my ignorance, but am I to add the code you provided in the VB code builder?

It is giving me a syntax error.

I probably still need to Dim db As DAO.Database to be able to execute it, right?

[John_G]

I think you could put this in the same sub as before:

Private Sub SortSeq()
Dim SQL as AString
Dim db As DAO.Database
Set db = CurrentDb()

SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where ExercisePos >= me!ExercisePos and ExerciseID <> " & me!ExerciseID.
db.execute SQL, dbfailonerror
me.requery

Me.Requery
Set db = Nothing
End Sub

Since this Sub is not an event procedure, you will have to call it in code for a command button, or in the after update event of the form.

[Dude]

I think you could put this in the same sub as before:

Private Sub SortSeq()
Dim SQL as AString
Dim db As DAO.Database
Set db = CurrentDb()

SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where ExercisePos >= me!ExercisePos and ExerciseID <> " & me!ExerciseID.
db.execute SQL, dbfailonerror
me.requery

Me.Requery
Set db = Nothing
End Sub

Since this Sub is not an event procedure, you will have to call it in code for a command button, or in the after update event of the form.

Great thanks, I put the code in the same sub.
And left in in the After Update event.
But the 'SQL = ...' line is highlighted in red in the code builder and I'm still getting a syntax error.

Is the period at the end of the me!ExerciseID. in red correct?
In any case, if I remove the period it gives me 'Run-time error 3061: Too few parameters. Expected 1.'

Soooo, I'm a bit lost.

[John_G]

Opps - sorry. Yes, the period was a mistake.

And my bad - I have the SQL statement wrong. It should be:


SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where ExercisePos >= " & me!ExercisePos & " and ExerciseID <> " & me!ExerciseID

I had the me!ExercisePos inside the quotes.

Sorry about that - I didn't check the SQL properly.

[Dude]

Thanks John!

It almost seems to work. I tried several scenario's and everything seems to go work well.

There are only some issues that occur once I start manipulating the first record.

If I change the position number from 1 to any other number (n) of a record (A), then the record(B) with position number 2 is the first one in the list.
The position of record (A) seems to become (n-1), instead of position (n)
If I do the same thing with record (B), the position of record (B) becomes (n-2), instead of position (n)
And so on.

I tried to solve it by running the renumbering sub of the thread that I posted in the OP, afterwards after each update, but that didn't seem to be the right medicine.
Changing record (A) to a higher number seems to make it (n-1) with every update.

Thanks for your time!

[John_G]

Hi -

We both got caught making the same asuumption, i.e. that you always moved records "up" in the list. That works, but not correctly. When you move a record "up", changes are required only in the range between where it WAS, and where it is NOW; my code changes everything below where it is NOW, and that is incorrect.

So, the code has to be revised to deal with both possibilities (I haven't tested this!):

Private Sub ExerciseID_AfterUpdate()
Dim SQL as String, OldPos as Integer, NewPos as integer 
Dim db As Database
Set db = CurrentDb()

OldPos = me!ExercisePos.OldValue    ' Original value before you changed it
NewPos = me!ExercisePos   ' Value you changed it TO

If NewPos < OldPos then
'
' Record was Moved UP
'
SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where (ExercisePos Between " & _
NewPos & " AND " & OldPos & ") and ExerciseID <> " & me!ExerciseID

else
'
' Record was moved down
'
SQL = "Update Exercises set ExercisePos = ExercisePos - 1 where (ExercisePos Between " & _
OldPos & " AND " & NewPos & ") and ExerciseID <> " & me!ExerciseID

Endif

db.execute SQL, dbfailonerror
me.requery

Me.Requery
Set db = Nothing
End Sub

I think this is best put in the After Update event of the ExercisePos control, to be sure you get the previous value properly.


Let us know how this works out.

John

[Dude]

Hey John, sorry for the belated reply.

I tested it and it works perfectly, thanks!

Dealing with numbers that get a higher position is a bit different then dealing with numbers that get a lower position though.
When say we want a record on position 2 to be on position 9 (so between current 9 and 10) then we have to enter 10 as the new position.
If we want a record on position 9 to go to position 2 (so between current 1 and 2) then we have to enter 2 as the new position.
So up means x+1 and down means (x).

It makes sense in a way, but it's something that needs to be explained to other people dealing with the database.

But this will do!

Many, many thanks!