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

    Syntax Error 3144 in SQL Update Query.

    Access 2007, not compiled, not split, and in normal form. My skill levels are moderate.

    The purpose of this code is to allow the user to edit a Meal Name.

    The meal name was originally "Hamburger with Tater Tots" and edited to "BBQ Hamburger."

    The data is stored in two unrelated tables, Tbl_Meals and Tbl_MealsWithItems.

    Tbl_Meals MealName is bound to the text box on the form, MealName, where the user edits the name.


    me.dirty=false saves the change to the Tbl_Meals.

    The second table Tbl_MealsWithItems contains three meal ingredients, each a separate record with the same corresponding MealID "8" in this case.

    The MealID "8" from the form is captured first and stored as the variable intMealID

    The second table Tbl_MealsWithItems should be updated by the SQL statement.

    Everything looks correct to me but it is generating error 3144. I don't see anything wrong with the syntax.

    All fields are Integers.

    Watch shows I am grabbing all the right variables.

    Debug.print stSql results are below and look good to me.

    Dim intMealid As Integer, stSql As String, stMealName As String

    intMealid = Me.MealID

    Me.Dirty = False forces save to Tbl_Meals

    stMealName = Me.MealName captures new name.

    stSql = "UPDATE Tbl_MealsWithItems" _
    & " SET MealName = '" & stMealName & "'," _
    & " WHERE MealNumber = " & intMealid & ";"


    Debug.Print stSql


    UPDATE Tbl_MealsWithItems SET MealName = 'BBQ Hamburger', WHERE MealNumber = 8;


    DoCmd.RunSQL stSql

    Fails with error 3144

    As always, help is appreciated.

    Phred

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't know what 3144 is but there seems to be an extra "," in there. Remove it and see if that helps.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,937
    I don't think it should make any difference, but have you tried:
    CurrentDb.Execute stSql

    instead of:
    DoCmd.RunSQL stSql
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Phred is offline Competent Performer
    Windows XP Access 2010 (version 14.0)
    Join Date
    Dec 2009
    Posts
    246
    Thanks Bob but it didn't make any difference. I think something is wrong at the end of my Sql statement. I think my data types are ok.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,937
    Another long shot
    Try:
    '""" & stMealName & """

    Instead of:
    '" & stMealName & "'
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. SQL Update Syntax Error
    By Phred in forum Queries
    Replies: 9
    Last Post: 02-05-2012, 05:56 PM
  2. Syntax Error on Update Statment
    By TinaCa in forum Programming
    Replies: 2
    Last Post: 09-14-2011, 05:53 PM
  3. Syntax error in UPDATE statement HELP!
    By asmith78 in forum SQL Server
    Replies: 5
    Last Post: 09-07-2011, 05:50 PM
  4. Run Time Error 3144 for UPDATE
    By KrenzyRyan in forum Programming
    Replies: 12
    Last Post: 05-20-2011, 10:28 AM
  5. UPDATE query syntax
    By jgelpi16 in forum Programming
    Replies: 10
    Last Post: 08-21-2010, 07:40 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