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