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