Results 1 to 10 of 10
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    Can not fix syntax error in update command

    Hello, I have a form where I want to update a certain cell from the EMP_TBL. It is saying my syntax is wrong and I can not find the error. If you could point me in the right direction, that would be greatly appreciated.




    Dim emp_id As Integer
    emp_id = Me.cmb_empl
    Dim employed As String
    employed = Me.cmb_quest
    Dim dbs As Database
    Dim qdf As QueryDef
    Set dbs = CurrentDb()
    dbs.Execute "UPDATE EMP_TBL " _
    & "SET EMPLOYED? = employed " _
    & "WHERE EMP_ID = emp_id;"
    dbs.Close

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you use a variable for the SQL and this technique you'll see the problem:

    http://www.baldyweb.com/ImmediateWindow.htm

    Hint, you have to concatenate the variables into the string. Help on that here:

    http://www.baldyweb.com/BuildSQL.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    How can I get that error box open to see how my query is set up wrong?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    database_1,

    Did you review the links that PBaldy provided??
    The first link should answer your post #3.

    If you don't understand the links, then tell us what is confusing to you?

  5. #5
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    I have never used the VBA Immediate window and am unsure how to

  6. #6
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    I figured out how to access the immediate window and was able to make some changes. Now when I try to run the sql, I get a data type mismatch. I am not sure where this is occurring. Private Sub cmd_save_Click()
    Dim emp As String
    Dim EMP_ID As Integer




    emp = Me.cmb_quest
    EMP_ID = Me.cmb_empl


    Dim mySQL As String


    mySQL = "UPDATE [EMP_TBL] SET [EMPLOYED?] = '" & emp & "' WHERE [EMP_ID] ='" & EMP_ID & "'"


    DoCmd.RunSQL (mySQL)
    Debug.Print mySQL


    End Sub

    I get back the correct EMP_ID and emp value but for some reason, it will not change the table

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What is the SQL in the immediate window after you run the sub??
    Would you post the result from the immediate window?


    Is "EMPLOYED?" a text type field??? (BTW - field names should be letters and/or numbers ONLY (exception is the underscore). Should not use spaces, punctuation or special characters in object names
    Is "EMP_ID" a text type field?

    Dates need "#" delimiters
    Text need quote delimiters
    Numbers do not need delimiters.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    mySQL = "UPDATE [EMP_TBL] SET [EMPLOYED?] = '" & emp & "' WHERE [EMP_ID] =" & EMP_ID
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    That worked! Thank you for the advice, still trying to get the hang of Access and all the tips are greatly appreciated. One thing is when I do run the query, it warns me that I will be updating a table, is there a way so that the error message does not pop up every time?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Use this syntax -see more info here

    Currentdb.Execute yourSqlHere, dbFailonError

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

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2019, 10:20 AM
  2. Replies: 5
    Last Post: 09-16-2018, 01:18 PM
  3. Replies: 1
    Last Post: 09-22-2014, 08:54 AM
  4. Error 3144: Syntax error in UPDATE statement??
    By Paintballlovr in forum Programming
    Replies: 7
    Last Post: 03-26-2014, 12:53 PM
  5. SQL Update Syntax Error
    By Phred in forum Queries
    Replies: 9
    Last Post: 02-05-2012, 05:56 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