Results 1 to 11 of 11
  1. #1
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6

    Renumber sequential numbers after update

    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:



    Code:
    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!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

  4. #4
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6
    Quote Originally Posted by orange View Post
    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.

    Click image for larger version. 

Name:	example1.png 
Views:	22 
Size:	10.8 KB 
ID:	16155

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

  5. #5
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6
    Quote Originally Posted by John_G View Post
    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?

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I think you could put this in the same sub as before:

    Code:
    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.

  7. #7
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6
    Quote Originally Posted by John_G View Post
    I think you could put this in the same sub as before:

    Code:
    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.

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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.

  9. #9
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6
    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!

  10. #10
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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!):


    Code:
    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

  11. #11
    Dude is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2014
    Posts
    6
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add letters to sequential numbers
    By bassplayer79 in forum Programming
    Replies: 29
    Last Post: 09-12-2013, 04:20 AM
  2. Sequential numbers...
    By jlgray0127 in forum Programming
    Replies: 1
    Last Post: 04-03-2013, 10:06 AM
  3. Sequential Numbers
    By cactuspete13 in forum Queries
    Replies: 5
    Last Post: 03-27-2013, 12:14 PM
  4. Generating sequential numbers automatically
    By Malcolm41 in forum Access
    Replies: 2
    Last Post: 07-15-2012, 08:20 PM
  5. To generate sequential numbers in query
    By lizzywu in forum Programming
    Replies: 1
    Last Post: 03-02-2012, 12:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums