Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Quick SQL Update question

    Hello on my access form I have a button which executes an SQL update command -
    Code:
    DoCmd****nSQL "UPDATE tblLines SET tblLines.stockID  = [Forms]![form1]![txtstockID];"
    It updates the field stockID from a textbox on the form.

    If I wanted to update another field at the same time (orderQuantity) from another textbox should I just use two seperate update statements inside the command button, or is there a way of combining two into one? If that makes sense?


  2. #2
    Join Date
    Aug 2004
    Posts
    364
    Its ok Ive answered my own question
    Code:
    UPDATE tblLines SET tblLines.stockID = Forms!form1!txtstockID, tblLines.quantityOrdered = [Forms]![form1]![txtQuantity];


    But...the code is updating too many records, it is not just updating the selected field from the selected listbox row, it is updating all the records of that belong to that field. So if I update the quantityOrdered for a selected record, all the other quantityOrdereds are being updated as well - which isnt right.
    Last edited by moss2076; 11-10-10 at 09:54.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You haven't put a "WHERE" clause in your SQL statement, so it's updating every record in the table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Bingo! Of course!! Thanks a million!

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    Sometimes it takes a fresh pair of eyes to see what we can't.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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