VBA Access Error Code 3129

[PatAccess]

Hello, I really need some help with this code
Private Sub Cmd_UpdateInfo_Click()
Dim iStr As String
iStr = "INSERT INTO Tbl_CorporateLicUpdate (CorpLicID,LastFilingDate,Expires,ProcessingDate,Renewed)"
iStr = iStr & "VALUES ("
iStr = iStr & "'" & Me.RecordID.Value & "' , #" & Date & "# , #" & Me.NewExp.Value & "# , #" & Me.NewProc.Value & "# , 0)"
DoCmd.RunSQL iStr

Dim uSQL As String
uSQL = "UPDATE Tbl_CorporateLicUpdate" & _
"SET Renewed=-1" & _
"WHERE CorpID = " & Me.RecordID & " AND ID = " & Me.ID & ""
DoCmd.RunSQL uSQL
End Sub

So the 1st portion works properly. It is inserting the new record.

The 2nd Portion gives me error code 3129 - Invalid SQL Statement
Previously I had
'DoCmd.RunSQL "UPDATE Tbl_CorporateLicUpdate SET Renewed= TRUE WHERE CorpLicID =" & Me.RecordID & " AND ID=" & Me.ID

But it say variable not yet created in this context. Anything I'm not seeing?

Thank you

 

[pbaldy]

Use this to see the finished SQL. Right off, you have a problem with spaces (or lack thereof):

http://www.baldyweb.com/ImmediateWindow.htm

 

[RuralGuy]

I would start by doing a MsgBox "[" & uSQL & "]" just before the RunSQL command.

 

[The_Doc_Man]

...
uSQL = "UPDATE Tbl_CorporateLicUpdate" & _
"SET Renewed=-1" & _
...

If you actually printed this out you would see that there is no space between the table name and the SET clause. Therefore, the parser that reads the string will see

UPDATE Tbl_CorporateLicUpdateSET ...

and in that context, it is looking for a table name that ends with SET, which probably doesn't exist. SQL uses spaces (and/or TAB characters) as token delimiters when parsing out statements of this type. Your FIRST statement actually also has this problem BUT because of the INSERT INTO table (fieldlist) VALUES syntax having that trailing parenthesis after the field list, you can get away with not having a space there even though technically you should always include spaces in that location anyway.

 

[PatAccess]

Use this to see the finished SQL. Right off, you have a problem with spaces (or lack thereof):


Thank you Guys! All 3 of them helped
When I was able to see the Code Printed it really became clearer.
I appreciate it!

 

[The_Doc_Man]

No problem. Just remember that we appreciate knowing when you consider your problem closed, and we also appreciate it when you click the THANKS button when we actually DO help you.

 

[PatAccess]

No problem. Just remember that we appreciate knowing when you consider your problem closed, and we also appreciate it when you click the THANKS button when we actually DO help you.

How do I close. I just figured out the Thanks Button
First time using this site
Thank you

 

[pbaldy]

Happy to help and welcome to the site! You can mark the thread closed by editing it (the thread) and changing the title to Solved.