vba 更新语句中的语法错误运行时错误 3144
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26223478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
syntax error in update statement run-time error 3144
提问by Adrienn Krammer
I got syntax error in update statement.Run-time error: 3144 I use the following code
我在更新语句中出现语法错误。运行时错误：3144 我使用以下代码
CurrentDb.Execute "UPDATE product " & _
" SET [product name] = '" & Me.txtName & "'" & _
", [cost of product] = " & Me.txtCost & "" & _
", [weight] = " & Me.txtWeight & "" & _
", [group] = '" & Me.CmbGroup & "'" & _
", [group ID] = '" & Me.txtGroupID & "'" & _
", [Ordered] = " & Me.txtOrdered & "" & _
" WHERE [roduct name] = " & Me.txtName.Tag & ""
What can be the problem? If it makes sense, then Me.txtCost , Me.txtWeight and me.txtOrdered are number
可能是什么问题？如果有意义，那么 Me.txtCost 、 Me.txtWeight 和 me.txtOrdered 是数字
Thanks for your help!
谢谢你的帮助！
回答by mwolfe02
Two problems that I see:
我看到的两个问题：
- Typo in
WHERE [roduct name](should beWHERE [product name]) - Missing quotes around
Me.txtName.Tagat the end of the statement
- 输入错误
WHERE [roduct name]（应该是WHERE [product name]） Me.txtName.Tag语句末尾缺少引号
Try this instead:
试试这个：
CurrentDb.Execute "UPDATE product " & _
" SET [product name] = '" & Me.txtName & "'" & _
", [cost of product] = " & Me.txtCost & "" & _
", [weight] = " & Me.txtWeight & "" & _
", [group] = '" & Me.CmbGroup & "'" & _
", [group ID] = '" & Me.txtGroupID & "'" & _
", [Ordered] = " & Me.txtOrdered & "" & _
" WHERE [product name] = '" & Me.txtName.Tag & "'"
回答by Parfait
To future readers of this post, reconsider interpolating or concatenating VBA values into a dynamic SQL query. Consider parameterized querieswith MS Access' QueryDefs. This will avoid mistyping, misquoting, and unreadable, and unmaintainable code.
对于这篇文章的未来读者，请重新考虑将 VBA 值插入或连接到动态 SQL 查询中。考虑使用 MS Access 的QueryDefs参数化查询。这将避免错误输入、错误引用以及不可读和不可维护的代码。
SQL(save as an MS Access stored query only once)
SQL （仅保存一次 MS Access 存储查询）
PARAMETERS [txtName_PARAM] TEXT, [txtCost_PARAM] DOUBLE,
[txtWeight_PARAM] DOUBLE, [CmbGroup_PARAM] TEXT,
[txtGroupID_PARAM] TEXT, [txtOrdered_PARAM] LONG,
[txtName_Tag_PARAM] TEXT;
UPDATE product
SET [product name] = [txtName_PARAM],
[cost of product] = [txtCost_PARAM],
[weight] = [txtWeight_PARAM],
[group] = [CmbGroup_PARAM],
[group ID] = [txtGroupID_PARAM],
[Ordered] = [txtOrdered_PARAM],
WHERE [product name] = [txtName_Tag_PARAM];
VBA(dynamically bind values to parameter placeholders)
VBA （将值动态绑定到参数占位符）
Dim qdef as QueryDef
Set qdef = CurrentDb.QueryDefs("mySavedQuery")
qdef![txtName_PARAM] = Me.txtName
qdef![txtCost_PARAM] = Me.txtCost
qdef![txtWeight_PARAM] = Me.txtWeight
qdef![CmbGroup_PARAM] = Me.CmbGroup
qdef![txtGroupID_PARAM] = Me.txtGroupID
qdef![txtOrdered_PARAM] = Me.txtOrdered
qdef![txtName_Tag_PARAM] = Me.txtName.Tag
qdef.Execute dbFailOnError
Set qdef = Nothing
