Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    the Netherlands
    Posts
    15

    Unanswered: problem with UPDATE QUERY

    I've a database with numbers.

    SID............. 1 2 3 4 5 6 7 8 9
    SNumber...... 1 2 3 1 1 1 2 2 2

    I wanna update till

    SID............. 1 2 3 4 5 6 7 8 9
    SNumber...... 1 3 4 1 1 1 3 3 3

    but result is

    SID............. 1 2 3 4 5 6 7 8 9
    SNumber...... 1 7 3 1 1 1 2 2 2

    DO ANYONE KNOW WHATS WRONG?
    ----------------------------------------------------------------------------

    Dim Db As DAO.Database
    Dim RS As DAO.Recordset
    Dim selectSQL As String
    Dim updateSQL As String

    selectSQL = "SELECT * FROM Table WHERE SNumber >= 2"
    Set RS = Db.OpenRecordset(selectSQL, dbOpenDynaset)

    updateSQL = "UPDATE Table SET SNumber = (SNumber + 1) " & _
    "WHERE SID = " & RS("SID")
    Do Until RS.EOF
    Db.Execute update
    RS.MoveNext
    Loop
    Last edited by Tjung; 10-29-03 at 04:19.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool Try it like this

    UPDATE Table SET SNumber = (SNumber + 1)
    WHERE SNumber >= 2

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool OR

    RS1 = SELECT * FROM TBL WHERE SNumber >= 2


    RS1.Movefirst
    DO until RS1.EOF = TRUE
    RS1.EDIT
    RS1.SNumber = (SNumber + 1)
    RS1.update
    RS1.movenext
    Loop

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Do this change in your codeim Db As DAO.Database
    Dim RS As DAO.Recordset
    Dim selectSQL As String
    Dim updateSQL As String

    selectSQL = "SELECT * FROM Table WHERE SNumber >= 2"
    Set RS = Db.OpenRecordset(selectSQL, dbOpenDynaset)

    updateSQL = "UPDATE Table SET SNumber = (SNumber + 1) " & _
    "WHERE SID = " & RS("SID")
    Do Until RS1.EOF
    Db.Execute updateSQL
    RS.MoveNext
    Loop
    Saludos
    Norberto

  5. #5
    Join Date
    Oct 2003
    Location
    the Netherlands
    Posts
    15

    Re: OR

    Originally posted by Marvels
    RS1 = SELECT * FROM TBL WHERE SNumber >= 2


    RS1.Movefirst
    DO until RS1.EOF = TRUE
    RS1.EDIT
    RS1.SNumber = (SNumber + 1)
    RS1.update
    RS1.movenext
    Loop

    THANKS! yeah.. this is easier than a update query. Great Idea!

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Norberto
    Do this change in your codeim Db As DAO.Database
    Dim RS As DAO.Recordset
    Dim selectSQL As String
    Dim updateSQL As String

    selectSQL = "SELECT * FROM Table WHERE SNumber >= 2"
    Set RS = Db.OpenRecordset(selectSQL, dbOpenDynaset)

    updateSQL = "UPDATE Table SET SNumber = (SNumber + 1) " & _
    "WHERE SID = " & RS("SID")
    Do Until RS1.EOF
    Db.Execute updateSQL
    RS.MoveNext
    Loop
    Norberto,
    you forgot to define the db and set it

    Dim db as datatbase OR Dim db as ADO.Database

    Set db= currentDB

    else everything was ok

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •