Results 1 to 11 of 11
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    update sql vs vba

    Can't figure it out what I am doing wrong here, the update query works without any problem

    Code:
    UPDATE tbl_Tracking SET tbl_Tracking.EHT_Stage = "0", tbl_Tracking.EHT_Confirmed = -1WHERE (((tbl_Tracking.EHT_Stage) Is Null) AND ((tbl_Tracking.P_Tracing) Like "*et*"));
    but when I convert it to vba and try to run it, the function doesn't do anything and I am not getting any error message either.



    Code:
    Private Sub Command125_Click()
    Dim rsSQL As DAO.Recordset
    Dim strsql As String
    strsql = "UPDATE tbl_Tracking SET tbl_Tracking.EHT_Stage = '0', tbl_Tracking.EHT_Confirmed = Yes " & _
    "WHERE (((tbl_Tracking.EHT_Stage)<'0' Or (tbl_Tracking.EHT_Stage) Is Null) AND ((tbl_Tracking.P_Tracing) Like '*et*'));"
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If EHT_Stage is a number or yes/no type field, remove the apostrophes. If EHT_Confirmed is a Yes/No type field, use True or -1 instead of Yes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Quote Originally Posted by June7 View Post
    If EHT_Stage is a number or yes/no type field, remove the apostrophes. If EHT_Confirmed is a Yes/No type field, use True or -1 instead of Yes.
    Thanks June7 for the reply, EHT_Stage is a text and EHT_Confirmed is a checkbox. I have changed from Yes to -1 the value for checkbox statement but still my code doesn't update the table.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Ooops. You aren't executing the SQL. You are just building it.

    Options:

    DoCmd.RunSQL strsql

    or

    CurrentDb.Execute strSQL
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Thanks June7
    DoCmd does the job.

    Quote Originally Posted by June7 View Post
    Ooops. You aren't executing the SQL. You are just building it.

    Options:

    DoCmd.RunSQL strsql

    or

    CurrentDb.Execute strSQL

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I know its marked solved but worth pointing out two things:

    a) Though both work, CurrentDB.Execute is more efficient than DoCmd.RunSQL.
    It also suppresses the 'You are about to update 10 records' messages without needing to use DoCmd.SetWarnings False
    Using this syntax CurrentDB.Execute "your SQL statement here", dbFailOnError means you only get a message when an error occurs

    b) Also you can use Yes (with no quote marks) as well as True or -1 for Boolean fields in action queries. Similarly No/False/0 all work
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Thought I had encountered issues with using Yes or No in the past. But just tested and it did work.

    Glad you got is working.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    All three methods have worked since v1.0. I know because I recently tested it for a thread at UA
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Must have been something in VBA I ran into. All good.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    isladogs. Thanks for the advise, indeed I got the update message using .Runsql but not when I changed to .execute function.

    Quote Originally Posted by isladogs View Post
    I know its marked solved but worth pointing out two things:

    a) Though both work, CurrentDB.Execute is more efficient than DoCmd.RunSQL.
    It also suppresses the 'You are about to update 10 records' messages without needing to use DoCmd.SetWarnings False
    Using this syntax CurrentDB.Execute "your SQL statement here", dbFailOnError means you only get a message when an error occurs

    b) Also you can use Yes (with no quote marks) as well as True or -1 for Boolean fields in action queries. Similarly No/False/0 all work

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Excellent. I always use that approach. Slightly faster and no need to suppress warning messages.
    Do use dbfailonerror though or you won't know if it fails for any reason.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 2
    Last Post: 06-21-2018, 03:16 AM
  2. Replies: 6
    Last Post: 11-05-2014, 05:27 PM
  3. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  4. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 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