Invalid Argument on Append Query

#1

HELP!

I have a screen (cancel an entry) where the user has to quickly review
the information (no changes allowed - just to make sure they chose the
correct order to cancel) and enter a reason for the cancel.

Then I run an append query to put the information onto a Cancel
table. And then a delete query to delete the order off the order
table. (When I rewrite I'll do it differently!)

The append query for a cancel comes up with "Invalid Argument".

I created the Append Query using the Grid. Each field is the field
from the form and the append field the appropriate field on the
table. One field, "Cancel Date" is not on the form, but defaults to
current date. It worked at one time (there are169 cancels on the
table - some from this past August and September)

I don't get it:

Query code:

INSERT INTO tblPOCancels ( PONum, PODate, DateEntered, MerchantKey,
VendorKey, POApproved, SuggShipDate, Description, CostofGoods,
Freight, TotalRetail, PODatetoStores, DeptNum, Reason )
SELECT [Forms]![frmCancelPO]![txtPONum] AS Expr1, [Forms]!
[frmCancelPO]![txtPODate] AS Expr2, [Forms]![frmCancelPO]!
[txtDateEntered] AS Expr4, [Forms]![frmCancelPO]![txtMerchantKey] AS
Expr5, [Forms]![frmCancelPO]![txtVendorKey] AS Expr6, [Forms]!
[frmCancelPO]![txtPOApproved] AS Expr7, [Forms]![frmCancelPO]!
[txtShipDate] AS Expr8, [Forms]![frmCancelPO]![txtDescription] AS
Expr3, [Forms]![frmCancelPO]![txtCostofGoods] AS Expr9, [Forms]!
[frmCancelPO]![txtFreight] AS Expr10, [Forms]![frmCancelPO]!
[txtTotalRetail] AS Expr11, [Forms]![frmCancelPO]![txtPODatetoStores]
AS Expr12, [Forms]![frmCancelPO]![txtDeptNum] AS Expr13, [Forms]!
[frmCancelPO]![txtReason] AS Expr14;

I also put MsgBox code to make sure all the fields were correct and
all is ok.

MsgBox [Forms]![frmCancelPO]![txtPONum]
MsgBox [Forms]![frmCancelPO]![txtPODate]
MsgBox [Forms]![frmCancelPO]![txtDescription]
MsgBox [Forms]![frmCancelPO]![txtDateEntered]
MsgBox [Forms]![frmCancelPO]![txtMerchantKey]
MsgBox [Forms]![frmCancelPO]![txtVendorKey]
MsgBox [Forms]![frmCancelPO]![txtPOApproved]
MsgBox [Forms]![frmCancelPO]![txtShipDate]
MsgBox [Forms]![frmCancelPO]![txtCostofGoods]
MsgBox [Forms]![frmCancelPO]![txtFreight]
MsgBox [Forms]![frmCancelPO]![txtTotalRetail]
MsgBox [Forms]![frmCancelPO]![txtPODatetoStores]
MsgBox [Forms]![frmCancelPO]![txtDeptNum]
MsgBox

DoCmd.OpenQuery "apqCancelPO", acViewNormal, acReadOnly

Thank you -
sara

#2

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 ***

#3

Rich P <rpng123 (AT) aol (DOT) com> wrote:

Quote:

DoCmd.RunSql strSql

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 at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#4

On Oct 6, 10:17*pm, "Tony Toews [MVP]" <tto... (AT) telusplanet (DOT) net> wrote:

Quote:

Rich P <rpng... (AT) aol (DOT) com> wrote:
DoCmd.RunSql strSql

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/

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

#5

On Oct 6, 5:56*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

Quote:

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***

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

#6

Glad it is working out. And some quick info:

currentdb.execute is probably a little faster than docmd.runsql -- if
you are processing several thousand records (like 20,000-40,000 or
100,000+ currentdb.execute probably will outperform docmd.runsql). For
one record, however - currentdb.execute may process it in 2 milliseconds
where docmd.runsql may take upwards of 8 milliseconds. This could be an
issue if you are a chipmonk catering to other chipmonks all on high
energy drinks. But if you are just a regular human - docmd.runsql is a
bit simpler than currentdb.execute. You can ask a user if they want to
perform the various operation long before you ever actually carry out
the Insert command. And yes, you probaly would want to also use

DoCmd.SetWarnings False
...
DoCmd.RunSql strSql
...
DoCmd.SetWarnings True

otherwise you will get all sorts of warning messages that you are about
to Append a record/delete/.... If you want these messages - then leave
out DoCmd.SetWarnings False

Rich

*** Sent via Developersdex http://www.developersdex.com ***

#7

Rich P <rpng123 (AT) aol (DOT) com> wrote:

Quote:

But if you are just a regular human - docmd.runsql is a
bit simpler than currentdb.execute.

However docmd.runsql won't tell you if an error occurred. And that's my biggest
concern.

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 at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#8

If there is a syntax error in the sql string - the procedure will just
error out - you can add

On Error Goto lblsomething

If you mean a data error - use referential integrity/normalization. If
RF gets violated - the procedure will error out.

The only other thing I could think of for errors would be a system
error. Does CurrentDB.Execute return an error object with dbFailOnError
flag set?

Rich

*** Sent via Developersdex http://www.developersdex.com ***

#9

Nice! Now I get it. This is better than having to futz around with
SetWarnings.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

#10

On Oct 8, 12:03 pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid>
wrote:

Quote:

Rich P <rpng... (AT) aol (DOT) com> wrote innews:48eb9194$0$89390$815e3792 (AT) news (DOT) qwest.net:

if you are just a regular human - docmd.runsql is a
bit simpler than currentdb.execute.

How about this, which is going to be just as easy to use as
DoCmd.RunSQL and will be safer, and have the added bonus of
requiring only one line of code to use (instead of three from
futzing around with SetWarnings):

Public Function RunSQL(strSQL As String) As Boolean
On Error GoTo errHandler

CurrentDB.Execute strSQL, dbFailOnError
RunSQL = True

exitRoutine:
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbExclamation, _
"Error in Function RunSQL()"
Resume exitRoutine
End Function

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I've been trying on and off to adopt the Execute method into my code
to replace DoCmd.RunSql without success.
I keep getting errors such as "Too few parameters. Expected (x)"
Below is the SQL Statement I use.
It seems to work fine with DoCmd.RunSQL.
When I try to use CurrentDb.Execute strSQL, the error pops up

Is there a different SQL syntax required for the Execute method?

strSQL = "UPDATE tblWinTypes SET tblWinTypes.fldDisc = Forms!
frmPrices.txtWinDisc, tblWinTypes.fldDesc = Forms!
frmPrices.txtCurDesc, tblWinTypes.fldMinWidth = Forms!
frmPrices.txtCurMinW, tblWinTypes.fldMaxWidth = Forms!
frmPrices.txtCurMaxW, tblWinTypes.fldMinHeight = Forms!
frmPrices.txtCurMinH, tblWinTypes.fldMaxHeight = Forms!
frmPrices.txtCurMaxH WHERE tblWinTypes.fldWinID = Forms!
frmPrices.txtID;"