update error 1

[longhair]

afternoon all,

receive a 450 error (wrong # of arguments or invalid property assignment with the following):

Set cmdPOin = New ADODB.Command
cmdPOin.ActiveConnection = CurrentProject.Connection
cmdPOin.CommandType = adCmdText
cmdPOin.CommandText "UPDATE tblOrderPO SET PONumber = " & strPO & " WHERE SKU = " & rst!SKU & ";"

strPO and rst!SKU are both populated.
PONumber is a valid text field in tblOrderPO
any suggestions?
regards,
longhair

 

[RoyVidar]

For text fields, you need text delimiters (single quotes), say

[tt]"UPDATE tblOrderPO SET PONumber = " & strPO & " WHERE SKU = '" & rst!SKU & "';"[/tt]

Roy-Vidar

 

[RoyVidar]

Ouch, qutes in the wrong place, sorry

[tt]
"UPDATE tblOrderPO SET PONumber = '" & strPO & "' WHERE SKU = " & rst!SKU & ";" [/tt]

Roy-Vidar

 

[longhair]

RoyVidar,
thanks for the pointer.
had already tried that but still receive the same error.
regards,
longhair

 

[RoyVidar]

One thing that should have given a compile error, is the lack of equal sign between .CommandText and the string

[tt]Set cmdPOin = New ADODB.Command
Set cmdPOin.ActiveConnection = CurrentProject.Connection
cmdPOin.CommandType = adCmdText
cmdPOin.CommandText = "UPDATE tblOrderPO SET PONumber = '" & strPO & "' WHERE SKU = " & rst!SKU[/tt]

Could you do a debug print of the string?

[tt]Debug.Print "UPDATE tblOrderPO SET PONumber = '" & strPO & "' WHERE SKU = " & rst!SKU[/tt]

and pick the result from the immediate pane (ctrl+g). That string should run, if you paste it into the SQL view of the query builder.

If there are illegal characters within the string, such as comma, single quote etc, you'd need more tweaking of the string.

Which line is giving the error?

Roy-Vidar

 

[longhair]

RoyVidar,
thanks, that was it - no '=' sign in the .command.text line.
have a star.
regards,
longhair