Re: Invalid Argument on Append Query

[Rich P]

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,Ven dorKey, POApproved, " _
& "SuggShipDa te, Description, CostofGoods, Freight, TotalRetail,
PODatetoStores, DeptNum, Reason) " _
& "Select " & txtPOnum & ", #" & txtPODate & "#, #" & txtDateEntered &
"#, '" & txtMerchantKey & _
& "', '" & txtVendorKey & "', '" & txtApproved & "', #" &
txtShippedDate & "#, '" & txtDescription & "', " _
& txtCostOfGoods & ", " & txtFreight & ", " & txtTotalRetail & ", #" &
txtPostDateToSt ores & "#, " _
& 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 ***

[Tony Toews [MVP]]

Re: Invalid Argument on Append Query

Rich P <rpng123@aol.co mwrote:

>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.Execu te strSQL,dbfailon error.. For ADO use
CurrentProject. Connection.Exec ute strCommand, lngRecordsAffec ted, adCmdText You
can then remove the docmd.setwarnin gs lines.

If you're going to use docmd.setwarnin gs 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.execu te 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/

[sara]

Re: Invalid Argument on Append Query

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

Rich P <rpng...@aol.co mwrote:

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.Execu te strSQL,dbfailon error..  For ADO use
CurrentProject. Connection.Exec ute strCommand, lngRecordsAffec ted, adCmdText    You
can then remove the docmd.setwarnin gs lines.
>
If you're going to use docmd.setwarnin gs 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.execu te 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

[sara]

Re: Invalid Argument on Append Query

On Oct 6, 5:56 pm, Rich P <rpng...@aol.co mwrote:

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,Ven dorKey, POApproved, " _
 & "SuggShipDa te, Description, CostofGoods, Freight, TotalRetail,
PODatetoStores, DeptNum, Reason) " _
 & "Select " & txtPOnum & ", #" & txtPODate & "#, #" & txtDateEntered &
"#, '" & txtMerchantKey & _
 & "', '" & txtVendorKey & "', '" & txtApproved & "', #" &
txtShippedDate & "#, '" & txtDescription & "', " _
 & txtCostOfGoods & ", " & txtFreight & ",  " & txtTotalRetail & ", #" &
txtPostDateToSt ores & "#, " _
 & 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 Developersdexht tp://www.developersd ex.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]
dtmPODateToStor es = [Forms]![frmCancelPO]![txtPODatetoStor es]
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 & ", #" &
dtmPODateToStor es _
& "#, " & lngDeptNum & ", '" & strReason & "'"

CurrentDb.Execu te strSQL, dbFailOnError

I used Tony's suggestion on CurrentDB.Execu te. Now if I can just
remember all this!

Many thanks -
sara

[Rich P]

Re: Invalid Argument on Append Query

Glad it is working out. And some quick info:

currentdb.execu te is probably a little faster than docmd.runsql -- if
you are processing several thousand records (like 20,000-40,000 or
100,000+ currentdb.execu te probably will outperform docmd.runsql). For
one record, however - currentdb.execu te 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.execu te. 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.SetWarnin gs False
...
DoCmd.RunSql strSql
...
DoCmd.SetWarnin gs 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.SetWarnin gs False


Rich

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

[Tony Toews [MVP]]

Re: Invalid Argument on Append Query

Rich P <rpng123@aol.co mwrote:

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

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/

[Rich P]

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.Execu te return an error object with dbFailOnError
flag set?

Rich

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

[David W. Fenton]

Re: Invalid Argument on Append Query

Rich P <rpng123@aol.co mwrote in
news:48eb9194$0 $89390$815e3792 @news.qwest.net :

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

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.Execu te 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/

[Rich P]

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

[Lou O]

Re: Invalid Argument on Append Query

On Oct 8, 12:03 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

Rich P <rpng...@aol.co mwrote innews:48eb9194 $0$89390$815e37 92@news.qwest.n et:
>

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

>
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.Execu te 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.Execu te strSQL, the error pops up

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


strSQL = "UPDATE tblWinTypes SET tblWinTypes.fld Disc = Forms!
frmPrices.txtWi nDisc, tblWinTypes.fld Desc = Forms!
frmPrices.txtCu rDesc, tblWinTypes.fld MinWidth = Forms!
frmPrices.txtCu rMinW, tblWinTypes.fld MaxWidth = Forms!
frmPrices.txtCu rMaxW, tblWinTypes.fld MinHeight = Forms!
frmPrices.txtCu rMinH, tblWinTypes.fld MaxHeight = Forms!
frmPrices.txtCu rMaxH WHERE tblWinTypes.fld WinID = Forms!
frmPrices.txtID ;"

[David W. Fenton]

Re: Invalid Argument on Append Query

Lou O <lgeastwood@gma il.comwrote in
news:2882e39f-3c8a-4cdb-88df-5de088f818ef@m3 6g2000hse.googl egroups.co
m:

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.Execu te strSQL, the error pops up
>

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.fld Disc = '" &
Forms!frmPrices .txtWinDisc & "', tblWinTypes.fld Desc = '" &
Forms!frmPrices .txtCurDesc & "', tblWinTypes.fld MinWidth = " &
Forms!frmPrices .txtCurMinW & ", tblWinTypes.fld MaxWidth = " &
Forms!frmPrices .txtCurMaxW & ", tblWinTypes.fld MinHeight = " &
Forms!frmPrices .txtCurMinH & ", tblWinTypes.fld MaxHeight = " &
Forms!frmPrices .txtCurMaxH & " WHERE tblWinTypes.fld WinID = " &
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/

[Rich P]

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

[lyle fairfield]

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.Execu te: 93
DBEngine(0)(0). Execute: 93
CurrentProject. Connection.Exec ute: 202
QueryDef.Execut e: 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.Execu te.

Querydef.Execut e 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...@teluspl anet.netwrote:

Also performance can be significantly different between the two methods.  One posting
stated currentdb.execu te took two seconds while docmd.runsql took eight seconds.  As
always YMMV.

[Tony Toews [MVP]]

Re: Invalid Argument on Append Query

lyle fairfield <lyle.fairfield @gmail.comwrote :

>Ticks for 100 iterations
>
>Northwind 2007.accdb
>
>UPDATE Employees SET [Last Name] = StrConv([Last Name], 1)
>
>DoCmd.RunSQL : 437
>CurrentDb.Exec ute: 93
>DBEngine(0)(0) .Execute: 93
>CurrentProject .Connection.Exe cute: 202
>QueryDef.Execu te: 62
>The last depends on the existence of a saved query of the sql.

Interesting. Thanks.

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

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!InvTransDeta ilID & ";"
CurrentDb.Execu te 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/

[Tony Toews [MVP]]

Re: Invalid Argument on Append Query

Rich P <rpng123@aol.co mwrote:

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

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/