none
Updating table with failed results RRS feed

  • Question

  • Hi all,

    I have a MS Access database where data in a table needs to be updated. The SQL statements below.

    My issue is that everything works fine, but when the last record is updated, ALL the previous updated records is changed to the value of the last updated record.

    The first SELECT statement retrieves the correct records for updating - in this case, 4 records are retrieved.

    When the UPDATE statement is executed, all 4 records are updated with the same value, in stead of each value individually.

    What should happen is that the 4 records retrieved, has a value of 3, 4, 5, and 6. When they are updated, the values should be updated as follows:

    3 update to 4

    4 update to 5

    5 update to 6

    6 update to 7

    but all 4 records are updated to 7.

    Can somebody see if I have a flaw in my statements?

    Thanks

    Deon

    Monday, November 13, 2017 12:38 PM

Answers

  • In the first step of the loop, 3 becomes 4.

    In the second step of the loop, 4 becomes 5 - both values that were 4 to begin with and values that were 3, but have been changed to 4 in the first step.

    Etc.

    To get around this, sort rst descending:

            SQL = ...
                ...
                & "ORDER BY ObjectiveSeqNumber DESC"

    That way, 6 will be updated to 7, then 5 will be updated to 6, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deon SA Monday, November 13, 2017 1:35 PM
    Monday, November 13, 2017 1:01 PM

All replies

  • Hi all,

    First of all, the forums, etc has changed. I used to post to Microsoft developer Forum for MS Access, but this choice has disappeared from the list. Now I am not sure where I must post this. Please direct me to the correct forum.

    I have a MS Access database where data in a table needs to be updated. The SQL statements below.

    My issue is that everything works fine, but when the last record is updated, ALL the previous updated records is changed to the value of the last updated record.

    Can somebody see if I have a flaw in my statements?

    Thanks

    Deon

    Monday, November 13, 2017 9:04 AM
  • Hi Deon SA.

    You could ask your question in the Access for Developers Forum.

    Bye.


    Luigi Bruno
    MCP, MCTS, MOS, MTA

    Monday, November 13, 2017 12:04 PM
  • In the first step of the loop, 3 becomes 4.

    In the second step of the loop, 4 becomes 5 - both values that were 4 to begin with and values that were 3, but have been changed to 4 in the first step.

    Etc.

    To get around this, sort rst descending:

            SQL = ...
                ...
                & "ORDER BY ObjectiveSeqNumber DESC"

    That way, 6 will be updated to 7, then 5 will be updated to 6, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deon SA Monday, November 13, 2017 1:35 PM
    Monday, November 13, 2017 1:01 PM
  • Thanks Hans - works 100%!!
    Monday, November 13, 2017 1:35 PM