Re：附加查询的参数无效 [英] Re: Invalid Argument on Append Query
问题描述
嗨Sara，
如果你这样试试 - 你可能会有更好的运气
Dim strSql As String
strSql =" INSERT INTO tblPOCancels（PONum，PODate，DateEntered，
MerchantKey，VendorKey，POApproved，" _
&" SuggShipDate，Description ，CostofGoods，Freight，TotalRetail，
PODatetoStores，DeptNum，Reason）" _
& 选择 &安培; txtPOnum& "，＃" &安培; txtPODate& ＃，＃ &安培; txtDateEntered&
"＃，''" &安培; txtMerchantKey& _
& ''，''" &安培; txtVendorKey& ''，''" &安培; txtApproved& ''，＃" &
txtShippedDate& ＃，''" &安培; txtDescription& ''， _
& txtCostOfGoods& ， &安培; txtFreight& ， &安培; txtTotalRetail& "，＃" &
txtPostDateToStores& ＃， _
& txtDeptNum& "，''" &安培; txtReason& "''"
DoCmd.RunSql strSql
您可以将此代码放在表单本身的命令按钮中。我将
分隔您的值如下 - 数值不需要
分隔。日期值（对于Access Jet sql）需要一个＃符号来进行
分隔 - 而文本值（原因，描述，......）需要一个
撇号''界定。此外，上面的字符串显示所有混乱
up。如果你将它复制到记事本中以解读它 - 字符串是
全部被打破_&
下划线定义了字符串的末尾和&符号定义
下一个字符串的开头。清理完毕后 - 将其复制到
表格按钮的代码模块中。希望你不会得到任何红色。那么
应该可以工作。
HTH
Rich
***通过开发人员发送的指南 http://www.developersdex.com ***
Hi Sara,
If you try it this way - you may have better luck
Dim strSql As String
strSql = "INSERT INTO tblPOCancels ( PONum, PODate, DateEntered,
MerchantKey,VendorKey, POApproved, " _
& "SuggShipDate, Description, CostofGoods, Freight, TotalRetail,
PODatetoStores, DeptNum, Reason) " _
& "Select " & txtPOnum & ", #" & txtPODate & "#, #" & txtDateEntered &
"#, ''" & txtMerchantKey & _
& "'', ''" & txtVendorKey & "'', ''" & txtApproved & "'', #" &
txtShippedDate & "#, ''" & txtDescription & "'', " _
& txtCostOfGoods & ", " & txtFreight & ", " & txtTotalRetail & ", #" &
txtPostDateToStores & "#, " _
& txtDeptNum & ", ''" & txtReason & "''"
DoCmd.RunSql strSql
You can place this code in a command button on the form itself. I
delimited your values as follows - number value do not require
delimiting. Date values (for Access Jet sql) require a # symbol for
delimiting - and text values (reason, Description, ...) require a single
apostrophe '' for delimiting. Also, the string above appears all jumbled
up. If you copy this into notepad to unscramble it - the strings are
all broken at _ &
the underscore defines the end of the string and the ampersand defines
the beginning of the next string. Once you clean it up - copy it into
the Form button''s code module. Hopefully you wont get any red. Then it
should work.
HTH
Rich
*** Sent via Developersdex http://www.developersdex.com ***
推荐答案
Rich P< rp ***** @ aol.comwrote：
Rich P <rp*****@aol.comwrote:
> DoCmd.RunSql strSql
>DoCmd.RunSql strSql
DoCmd.RunSQ的问题是它忽略了任何错误。以下任一项
将显示查询收到的任何错误消息。如果使用DAO，请使用
Currentdb.Execute strSQL，dbfailonerror ..对于ADO使用
CurrentProject.Connection.Execute strCommand，lngRecordsAffected，adCmdText你
然后可以删除docmd.setwarnings行。
如果您要使用docmd.setwarnings，请确保将True语句放入
任何错误处理代码也是如此。否则奇怪的事情可能会发生在
之后，尤其是在您使用应用程序时。例如，您将不再获得
您是否希望保存更改如果您关闭对象，则会显示消息这可能意味着
将不需要的更改，删除或添加内容保存到您的MDB。
此外，这两种方法的性能可能会有很大差异。一个帖子
声明currentdb.execute花了两秒钟，而docmd.runsql花了八秒钟。因为
总是YMMV。
Tony
-
Tony Toews，Microsoft Access MVP
请仅在新闻组中回复，以便其他人可以阅读整个邮件主题。
Microsoft Access Links，Hints，Tips&会计系统
http://www.granite.ab.ca /accsmstr.htm
Tony的Microsoft Access博客 - http://msmvps.com/blogs/access/
10月6日，10：17 * pm，Tony Toews [MVP] ]" < tto ... @ telusplanet.netwrote：
On Oct 6, 10:17*pm, "Tony Toews [MVP]" <tto...@telusplanet.netwrote:
Rich P< rpng ... @ aol.comwrote：
Rich P <rpng...@aol.comwrote:
DoCmd.RunSql strSql
DoCmd.RunSql strSql
* DoCmd.RunSQ的问题是它忽略了任何错误。 *以下任一项
将显示查询收到的任何错误消息。 *如果使用DAO，请使用
Currentdb.Execute strSQL，dbfailonerror .. *对于ADO使用
CurrentProject.Connection.Execute strCommand，lngRecordsAffected，adCmdText * *你
然后可以删除docmd.setwarnings行。
如果您要使用docmd.setwarnings，请确保将True语句放入
任何错误处理代码。 *否则奇怪的事情可能会在
之后发生，尤其是在您使用应用程序时。 *例如，您将不再获得
您是否希望保存更改如果您关闭对象，则会显示消息*这可能意味着
将不需要的更改，删除或添加内容保存到您的MDB。
两种方法之间的性能也可能有很大差异。 *一个帖子
声明currentdb.execute花了两秒钟，而docmd.runsql花了八秒钟。 *因为
总是YMMV。
Tony
-
Tony Toews，Microsoft Access MVP
* *请仅在新闻组中回复，以便其他人可以阅读整个消息主题。
* * Microsoft Access Links，Hints ，提示&会计系统：http：//www.granite.ab.ca/accsmstr.htm
* * Tony的微软Access博客-http：//msmvps.com/blogs/access/
The problem with *DoCmd.RunSQ is that it ignores any errors. *Either of the following
will display any error messages received by the query. *If using DAO, use
Currentdb.Execute strSQL,dbfailonerror.. *For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText * *You
can then remove the docmd.setwarnings lines.
If you''re going to use docmd.setwarnings make very sure you put the True statement in
any error handling code as well. * Otherwise weird things may happen later on
especially while you are working on the app. *For example you will no longer get the
"Do you wish to save your changes" message if you close an object. *This may mean
that unwanted changes, deletions or additions will be saved to your MDB.
Also performance can be significantly different between the two methods. *One posting
stated currentdb.execute took two seconds while docmd.runsql took eight seconds. *As
always YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony''s Microsoft Access Blog -http://msmvps.com/blogs/access/
托尼 -
非常感谢！我使用了你的建议（见上面的回复代码）
我也把设置警告和在错误例程中，我在这个应用程序中使用了
。我偶尔会有时间
我会说Access并不是在问我有关保存的问题。我很沮丧。
幸运的是，我总是在沙盒中工作。复制并且只在移动代码时
我已经完成了测试。但现在我想*我知道为什么。谢谢！
Sara
Tony -
Many MANY Thanks! I used your suggestion (see code in reply above)
and I also put the "Set Warnings" in the error routine everywhere I
used it in this application. I would occasionally have a time where
I''d say "Access isn''t asking me about saving" and I was frustrated.
Luckily, I always work in a "sandbox" copy and only move code in when
I''m done testing. But now I *think* I know why. THANKS!!
Sara
10月6日，5：56 * pm，Rich P< rpng ... @ aol.comwrote：
On Oct 6, 5:56*pm, Rich P <rpng...@aol.comwrote:
嗨Sara，
如果你这样试试 - 你可能会有更好的运气
Dim strSql As String
strSql =" INSERT INTO tblPOCancels（PONum，PODate，DateEntered，
MerchantKey，VendorKey ，POApproved，_
*&SuggShipDate，Description，CostofGoods，Freight，TotalRetail，
PODatetoStores，DeptNum，Reason）" _
*& 选择 &安培; txtPOnum& "，＃" &安培; txtPODate& ＃，＃ &安培; txtDateEntered&
"＃，''" &安培; txtMerchantKey& _
*& ''，''" &安培; txtVendorKey& ''，''" &安培; txtApproved& ''，＃" &
txtShippedDate& ＃，''" &安培; txtDescription& ''， _
*& txtCostOfGoods& ， &安培; txtFreight& "，*" &安培; txtTotalRetail& "，＃" &
txtPostDateToStores& ＃， _
*& txtDeptNum& "，''" &安培; txtReason& "''"
DoCmd.RunSql strSql
您可以将此代码放在表单本身的命令按钮中。 *我
将您的值分隔如下 - 数值不需要
分隔。 *日期值（对于Access Jet sql）需要一个＃符号来进行
分隔 - 而文本值（原因，描述，......）需要一个
撇号''用于划界。 *此外，上面的字符串显示所有混乱
up。 *如果你将其复制到记事本中以解读它 - 字符串是
全部在_& *
下划线定义了字符串的结尾，而&符号定义了下一个字符串开头的
。 *清理后 - 将其复制到
表格按钮的代码模块中。 *希望你不会得到任何红色。 *那么
应该有效。
HTH
Rich
***通过开发者发送索引：http：//www.developersdex.com***
Hi Sara,
If you try it this way - you may have better luck
Dim strSql As String
strSql = "INSERT INTO tblPOCancels ( PONum, PODate, DateEntered,
MerchantKey,VendorKey, POApproved, " _
*& "SuggShipDate, Description, CostofGoods, Freight, TotalRetail,
PODatetoStores, DeptNum, Reason) " _
*& "Select " & txtPOnum & ", #" & txtPODate & "#, #" & txtDateEntered &
"#, ''" & txtMerchantKey & _
*& "'', ''" & txtVendorKey & "'', ''" & txtApproved & "'', #" &
txtShippedDate & "#, ''" & txtDescription & "'', " _
*& txtCostOfGoods & ", " & txtFreight & ", *" & txtTotalRetail & ", #" &
txtPostDateToStores & "#, " _
*& txtDeptNum & ", ''" & txtReason & "''"
DoCmd.RunSql strSql
You can place this code in a command button on the form itself. *I
delimited your values as follows - number value do not require
delimiting. *Date values (for Access Jet sql) require a # symbol for
delimiting - and text values (reason, Description, ...) require a single
apostrophe '' for delimiting. *Also, the string above appears all jumbled
up. *If you copy this into notepad to unscramble it - the strings are
all broken at _ & *
the underscore defines the end of the string and the ampersand defines
the beginning of the next string. *Once you clean it up - copy it into
the Form button''s code module. *Hopefully you wont get any red. *Thenit
should work.
HTH
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
我以为我发布了这个谢谢你，但我没有看到它所以我再次发布了
。放手吧太重要了！
Rich -
谢谢！我花了一些时间来获得变量和SQL，但是我确实这样做了！
做到了！我也学到了很多，谢谢你。看下面的代码
：
dtmPODate = [Forms]！[frmCancelPO]！[txtPODate]
strDescription = [Forms]！[frmCancelPO ]！[txtDescription]
dtmDateEntered = [Forms]！[frmCancelPO]！[txtDateEntered]
lngMerchantKey = [Forms]！[frmCancelPO]！[txtMerchantKey]
lngVendorKey = [Forms]！[frmCancelPO]！[txtVendorKey]
strPOApproved = [Forms]！[frmCancelPO]！[txtPOApproved]
dtmSuggShipDate = [形式]！[frmCancelPO]！[txtShipDate]
curCostOfGoods = [Forms]！[frmCancelPO]！[txtCostofGoods]
curFreight = [Forms]！[frmCancelPO]！[ txtFreight]
curTotalRetail = [Forms]！[frmCancelPO]！[txtTotalRetail]
dtmPODateToStores = [Forms]！[frmCancelPO]！[txtPODatetoStores]
lngDeptNum = [Forms]！[frmCancelPO]！[txtDeptNum]
strSQL =" INSERT INTO tblPOCancels（PONum，PODate，DateEntered，
MerchantKey ，_
&VendorKey，POApproved，SuggShipDate，Description ，
CostofGoods， _
& " Freight，TotalRetail，PODatetoStores，DeptNum，Reason）" _
& "选择 &安培; lngPONum& "，＃" &安培; dtmPODate& ＃，＃ &
dtmDateEntered _
& ＃，''" &安培; lngMerchantKey& ''，''" &安培; lngVendorKey& ''，''" &
strPOApproved _
& ''，＃" &安培; dtmSuggShipDate& ＃，''" &安培; strDescription& ''， &
curCostOfGoods _
& ， &安培; curFreight& ， &安培; curTotalRetail& "，＃" &b; b $ b&b ＃， &安培; lngDeptNum& "，''" &安培; strReason& "''"
CurrentDb.Execute strSQL，dbFailOnError
我在CurrentDB.Execute上使用了Tony的建议。现在，如果我能够，只需要记住这一切！
非常感谢 -
sara
I thought I posted this thank you, but I don''t see it so I''m posting
again. Too important to let it go!
Rich -
THANK YOU!! It took me a bit to get the variables and SQL right, but I
did it! Learned a lot, too and I thank you for that. See the code
below:
dtmPODate = [Forms]![frmCancelPO]![txtPODate]
strDescription = [Forms]![frmCancelPO]![txtDescription]
dtmDateEntered = [Forms]![frmCancelPO]![txtDateEntered]
lngMerchantKey = [Forms]![frmCancelPO]![txtMerchantKey]
lngVendorKey = [Forms]![frmCancelPO]![txtVendorKey]
strPOApproved = [Forms]![frmCancelPO]![txtPOApproved]
dtmSuggShipDate = [Forms]![frmCancelPO]![txtShipDate]
curCostOfGoods = [Forms]![frmCancelPO]![txtCostofGoods]
curFreight = [Forms]![frmCancelPO]![txtFreight]
curTotalRetail = [Forms]![frmCancelPO]![txtTotalRetail]
dtmPODateToStores = [Forms]![frmCancelPO]![txtPODatetoStores]
lngDeptNum = [Forms]![frmCancelPO]![txtDeptNum]
strSQL = "INSERT INTO tblPOCancels (PONum, PODate, DateEntered,
MerchantKey, " _
& " VendorKey, POApproved, SuggShipDate, Description,
CostofGoods, " _
& " Freight, TotalRetail, PODatetoStores, DeptNum, Reason) " _
& " Select " & lngPONum & ", #" & dtmPODate & "#, #" &
dtmDateEntered _
& "#, ''" & lngMerchantKey & "'', ''" & lngVendorKey & "'', ''" &
strPOApproved _
& "'', #" & dtmSuggShipDate & "#, ''" & strDescription & "'', " &
curCostOfGoods _
& ", " & curFreight & ", " & curTotalRetail & ", #" &
dtmPODateToStores _
& "#, " & lngDeptNum & ", ''" & strReason & "''"
CurrentDb.Execute strSQL, dbFailOnError
I used Tony''s suggestion on CurrentDB.Execute. Now if I can just
remember all this!
Many thanks -
sara
这篇关于Re：附加查询的参数无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
