Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: docmd.runsql Update Problem

    Morning all hope you are well

    I have an issue will a runsql statement when trying to update a table. Here's the code.

    Dim SQL As String

    SQL = "UPDATE Userstbl " & _
    "SET strEmpPasswordA ='" & Me.txtreenterpassword.Value & "' WHERE strEmpNameA ='" & Me.username.Value & " '"
    DoCmd.RunSQL SQL

    The issue is that it comes up with the Warning that you are going to modify rows but instead of saying 1 Row it says 0.

    I have checked the coluom names in the table to make sure they match but cannot understand why this would not be working.

    Any help would be great

    Thank you

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is a space before the ending single quote:
    Code:
    & Me.username.Value & " '"
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    99

    Still not updating

    Thanks for the prompt reply Sinndho

    I have made the changes you recommended and removed the space but still I am getting the issue with it saying 0 rows to update.

    Thank you

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Add:
    Code:
    SQL = "UPDATE Userstbl " & _
    "SET strEmpPasswordA ='" & Me.txtreenterpassword.Value & "' WHERE strEmpNameA ='" & Me.username.Value & "'"
    Debug.Print SQL
    Stop
    2. Run
    3. Open the Immediate window (Ctrl+G) and copy the value of SQL that was printed there.
    4. Open a new query, switch it in SQL view and paste what you copied in 3.
    5. Switch the query to Datasheet view (not execute!) and see what it shows.
    Have a nice day!

  5. #5
    Join Date
    Feb 2013
    Posts
    99

    Hummm

    Thanks for the Advice Sinndho with the debug

    Right in the Immediate window I get

    UPDATE Userstbl SET strEmpPasswordA ='test' WHERE strEmpNameA ='10'

    Added to a query and opened in Datasheet view and under the field [strEmpNameA] and table name userstbl in the Criteria is the number 10

    Now number 10 is the Auto numbered [ID] number which equals 10, I will have a look at why it is adding this criteria and get back to you

    Thanks for the advise so far

    Kind Regards

  6. #6
    Join Date
    Feb 2013
    Posts
    99

    Horar

    Once again Sinndho thanks

    Found that the original select statement selects both the ID and the strEmpNameA from the table changed the Me.username.value to Me.username.Column(1) and it worked.

    Thanks once again for point me in the right direction

    Kind Regards

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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