Simple SQL query in Access fails with a missing semicolon error.

Question

So, I'm learning Access 2007, Visual Basic, and SQL at the same time. Not ideal.

I have this code attached to a button in a standard wizard-generated interface. I'm trying to copy a line from tblA to tblB. Every time the code is executed I get the message, "Run-time error '3137' Missing semicolon (;) at end of SQL statement."

I'm guessing that it's expecting the SQL statement to terminate earlier, before the WHERE? But without the WHERE, how would I attach the add to a particular line ID?

Private Sub buttonAdd_Click()
Dim strSQL As String
strSQL = "INSERT INTO [tblB]" & _
    "VALUES (ID, [Name], [Some value], [Some other value])" & _
    "SELECT * FROM tblA" & _
    "WHERE ID = '" & Me.ID & "' " & _
    ";"

DoCmd.RunSQL strSQL
End Sub

Answer 1

Syntax is wrong, you need to remove the "VALUES" keyword.
This is assuming that ID, [Name], [Some value] and [Some other value] are column names of tblB (some hesitation on my part with the last two names having "value").
The VALUES() SQL syntax is used to provide immediate values, but since you're getting the values from tblA, the query should look like:

strSQL = "INSERT INTO [tblB] " & _
    "(ID, [Name], [Some value], [Some other value]) " & _
    "SELECT * FROM tblA " & _
    "WHERE ID = '" & Me.ID & "' " & _
    ";"

Edit: I also added spaces between tokens. Good catch Nick D, thank for noticing this !

Answer 2

mjv is correct. You must remove the VALUES keyword and also you should put spaces between keywords, ie:

"SELECT * FROM tblA" & _
"WHERE ID = '" & Me.ID & "' " & _

the above will be joined as "...FROM tblAWHERE ID..."

Answer 3

You have put the field names in the values clause instead of after the table name, and there is a space missing between tbla and where. Both the value clause followed by select and the missing space could cause the error message by themselves.

strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
   "SELECT * FROM tblA " & _
   "WHERE ID = '" & Me.ID & "'"

The semicolon at the end is not requiered nowadays. You only need it for separating queries if you run more than one query at a time.

Also, if the ID field is numerical, you should not have apostrophes around the value:

strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
   "SELECT * FROM tblA " & _
   "WHERE ID = " & Me.ID