Re: Invalid Argument on Append Query

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

re: Re: Invalid Argument on Append Query

Rich P <rpng123@aol.comwrote:
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/

re: Re: Invalid Argument on Append Query

On Oct 6, 10:17*pm, "Tony Toews [MVP]" <tto...@telusplanet.netwrote: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

re: Re: Invalid Argument on Append Query

On Oct 6, 5:56*pm, Rich P <rpng...@aol.comwrote: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: Re: Invalid Argument on Append Query

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

re: Re: Invalid Argument on Append Query

Rich P <rpng123@aol.comwrote:
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/

re: Re: Invalid Argument on Append Query

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

re: Re: Invalid Argument on Append Query

Rich P <rpng123@aol.comwrote in
news:48eb9194$0$89390$815e3792@news.qwest.net:
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/

re: Re: Invalid Argument on Append Query

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

Rich

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

re: Re: Invalid Argument on Append Query

On Oct 8, 12:03 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote: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;"

re: Re: Invalid Argument on Append Query

Lou O <lgeastwood@gmail.comwrote in
news:2882e39f-3c8a-4cdb-88df-5de088f818ef@m36g2000hse.googlegroups.co
m:
DoCmd.RunSQL resolves references to form fields. But Execute does
not, so you have to resolve them in your SQL string:

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 & ";"

I assume that the first two fields are character fields and the
others are numeric. By using "'" for the delimiter, I'm also
assuming that the single quote character does not occur within the
data in those fields.

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

re: Re: Invalid Argument on Append Query

Now this isn't as simple as DoCmd.RunSql. My original suggestion was
for simplicity sake.

My thing is to go the simplest route. If OOP were simpler to use for a
given operation than having to write several lines of VBA code (by
several I mean thousands of VBA lines to a few hundred OOP lines to
achieve the same thing) then I would suggest that.

Rich

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

re: Re: Invalid Argument on Append Query

Ticks for 100 iterations

Northwind 2007.accdb

UPDATE Employees SET [Last Name] = StrConv([Last Name], 1)

DoCmd.RunSQL: 437
CurrentDb.Execute: 93
DBEngine(0)(0).Execute: 93
CurrentProject.Connection.Execute: 202
QueryDef.Execute: 62
The last depends on the existence of a saved query of the sql.

Of course, once we leave JET/ACE we get into a-synchronicity and time
may or may not be of any interest to us.
I think I have not used DoCmd.RunSQL or CurrentDb.Execute.

Querydef.Execute seems to be a powerful but largely ignored call. I am
experimenting with it now as a dynamic ODBC cludge, that is an ODBC
call without any persistent connection.

On Oct 6, 10:17*pm, "Tony Toews [MVP]" <tto...@telusplanet.netwrote:

re: Re: Invalid Argument on Append Query

lyle fairfield <lyle.fairfield@gmail.comwrote:
Interesting. Thanks.
I didn't like using it because it's a PITA if you have to debug a query later with
parameters. The folling is from
One method of using SQL queries in code as parameter queries can be difficult to work
with in code
http://www.granite.ab.ca/access/sqlqueries.htm

The problem is that you need to use a query in code such as looping through a
recordset or running an action query of some sort but you want to limit the number of
records via some criteria.

However when you run the query in the query window you have to enter appropriate
parameter values. Locating an appropriate value can be a pain in the ...

Actually what I always do when action or recordset queries get the slightest bit ugly
I make a SELECT query with the required fields. By ugly I mean one join to another
table, a lot of fields or whatever. I get this query working nicely showing all the
records I want. But with no selection criteria. Now I can quickly see if the query
looks proper now or six months from now when I forget what appropriate values were
for the criteria. I save that query.

I then create the action or SELECT query in the QBE grid with dummy selection
criteria based on that query I just created. I do NOT save it. Instead I click to the
SQL code view and copy the SQL code to the clipboard. Now I go into my VBA module
and paste the SQL code into the VBA code.. I then setup the strings with the double
quotes and line continuation for readability. I also setup the proper WHERE clause
criteria from my calling form or my code.

' decrement the QOH MatItemSizeQty record, ie change 5 to 3
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnHand = [miqQtyOnHand]-" & sngInventoryQty & " " & _
"WHERE itdID=" & me!InvTransDetailID & ";"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query.

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/

re: Re: Invalid Argument on Append Query

Rich P <rpng123@aol.comwrote:
Hmn, the above certainly hasn't been my experience. That said it's been quite a
number of years since I used docmd.runsql due to the setwanrings issues.

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/