Results 1 to 8 of 8
  1. #1
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    Cool SQL Update query using variables having syntax problems

    I have four pieces of data on my form. I am able to pull them all into variables in my VBA statement. When I step through the variables, in the watch window, are all picking up the correct piece of information. No problem.

    The last variable is the Primary key of the record I am trying to update. No problem. It finds the correct record in the correct table every time.

    Occasionally the query will put one piece of data in the wrong field. But my Update query statement is failing due to syntax. I get Run-Time Error 3144 Syntax Error in Update Statement, which is not overly helpful.

    I am new to SQL and at a moderate skill level with Access 2007. My docmd.runsql works fine. Here is my code:

    stSiteCatID = Forms!Frm_Modal_AttachCategoryFrm_Modal_AttachCate gory2!SiteCategoryID

    stCatName = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!PickCat.Column(1)

    stCatID = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!PickCat.Column(0)

    stMealCt = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!DFMealCount

    stSQL = "UPDATE Tbl_SiteCategory" _
    & "Tbl_SiteCategory.CategoryID = (""" & stCatID & """)," _
    & "SET Tbl_SiteCategory.Category = (""" & stCatName & """)," _
    & "Tbl_SiteCategory.DefaultMealCount = (""" & stMealCt & """)," _
    & "WHERE ((Tbl_SiteCategory.SiteCategoryID) = (""" & stSiteCatID & """));"

    DoCmd.RunSQL stSQL

    Any ideas?



    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,809
    This should help:

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

    Offhand, you have a space problem, and a comma problem, and the second line seems out of place.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    Also, use apostrophe as text delimiter (Date type would use # delimiter). If the ID fields are number datatype, don't use delimiters. I assume DefaultMealCount is a number field. Why saving the Category name when you have the CategoryID? That looks like duplication of data. The Category name should be retrievable by joining tables on the ID fields. Try this with pBaldy's debugging suggestion.

    stSQL = "UPDATE Tbl_SiteCategory" _
    & " SET Category = '" & stCatName & "'," _
    & " CategoryID = '" & stCatID & "'," _
    & " DefaultMealCount = " & stMealCt & _
    & " WHERE SiteCategoryID = '" & stSiteCatID & "';"
    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.

  4. #4
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246

    SQL Update Query in VBA Solved

    Hi and thanks for your example! Not only did it work but I learned a lot. Your statement is nice, clean and simple.

    stSQL = "UPDATE Tbl_SiteCategory" _
    & " SET Category = '" & stCatName & "'," _
    & " CategoryID = '" & stCatID & "'," _
    & " DefaultMealCount = " & inMealCt & "" _
    & " WHERE SiteCategoryID = " & inCatID & ";"

    Thank you for your help.

    Phred

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    You don't need the quote marks when they don't enclose anything.

    & " DefaultMealCount = " & inMealCt & _
    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.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,809
    Quote Originally Posted by June7 View Post
    You don't need the quote marks when they don't enclose anything.

    & " DefaultMealCount = " & inMealCt & _
    I agree, but caution, as it would be:

    & " DefaultMealCount = " & inMealCt _

    since there's a & starting the next line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    Copy/paste strikes again! Good catch on my typo, pBaldy, thanks. My practice is the & at end of line, not beginning.
    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
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,809
    No problemo!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Update syntax
    By looloo in forum Programming
    Replies: 6
    Last Post: 09-23-2011, 07:58 PM
  2. Reports and variables, problems
    By _Boo in forum Reports
    Replies: 3
    Last Post: 06-23-2011, 01:08 PM
  3. sql UPDATE syntax help
    By ducthang88 in forum Programming
    Replies: 1
    Last Post: 12-04-2010, 12:12 PM
  4. UPDATE query syntax
    By jgelpi16 in forum Programming
    Replies: 10
    Last Post: 08-21-2010, 07:40 PM
  5. Syntax For After Update Event
    By MuskokaMad in forum Forms
    Replies: 14
    Last Post: 03-12-2010, 01:48 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 - Senior Forums