RunTime error 3134 Syntax error in INSERT INTO

[comgentech]

I have gone over and over this and cannot find the issue with this code can anybody help me?

Dim StrSql As String

StrSql = "INSERT INTO [Customer Table] ([Customer ID], [First Name], [Last Name], [Company Name], [Main Phone], [Alt Phone], [Address]) SELECT " & [Customer ID] & " As [Customer ID], " & [First Name] & " As [First Name] , " & [Last Name] & " AS [Last Name], " & [Company Name] & " AS [Company Name], " & [Main Phone] & " AS [Main Phone], " & [Alt Phone] & " AS [Alt Phone], " & [Address] & " AS [Address];"
DoCmd.RunSQL (StrSql)

 

[petermeachem]

You haven't got single quotes round the strings?

 

[Golom]

With syntax like this

SELECT " & [red][Customer ID][/red] & " As [Customer ID],

The [red][Customer ID][/red] field appears to refer to a variable in your code ... and an illegal one at that since VB variable names cannot contain blanks. Perhaps you want

StrSql = "INSERT INTO [Customer Table] ([Customer ID], [First Name], [Last Name], [Company Name], [Main Phone], [Alt Phone], [Address]) " & _
"SELECT [Customer ID], [First Name], [Last Name], [Company Name], [Main Phone], [Alt Phone], [Address];"
DoCmd.RunSQL (StrSql)

 

[Golom]

Ohh ... and your SELECT statement is missing a FROM clause.

 

[petermeachem]

Maybe it is Access?
In which case the SELECT should be
VALUES(val1, etc )

 

[Golom]

petermeachem

There are two forms of the SQL INSERT statement (even in Access)

INSERT INTO myTable (fld1, fld2, ...)

VALUES (Val1, Val2, ...)

And

INSERT INTO myTable (fld1, fld2, ...)

SELECT fld1, fld2, ...
From SomeTable

I think (but am not sure) the the OP is trying to use the second form.

 

[comgentech]

I Have found the problem to this SQL statement, I removed the & signs and access 2002 seems to be happy with that it's working fine now but I have an update query that is giving me the similar error only is error 3464 data type mismatch maybe you can see something here that Might help..

code>
Private Sub CmdUpdate_Click()
Dim Strsql As String
Strsql = "UPDATE [Customer Table] SET [Customer Table].[First Name] = '" & [First Name] & "', [Customer Table].[Last Name] = '" & [Last Name] & "', [Customer Table].[Company Name] = '" & [Company Name] & "', [Customer Table].Address = '" & Address & "', [Customer Table].[Main Phone] = '" & [Main Phone] & "', [Customer Table].[Alt Phone] = '" & [Alt Phone] & "' WHERE [Customer Table].[Customer ID] = " & [Customer ID] & ";"
DoCmd.RunSQL (Strsql)
DoCmd.Close acForm, "New Customer"
End Sub
code>
Basically I just want to update the information I just entered with the other query

 

[Golom]

Just looking at the first bit of it

Strsql = "[blue]UPDATE [Customer Table] SET [First Name] = '[/blue]" & [red][First Name][/red]

The [blue]blue[/blue] part is a literal string that will be inserted as-is to start your SQL code.

The [red]red[/red] part however is outside the quotes so VBA is going to interpret as a VBA variable from your code. Since it contains a space, no such variable can exist.

Since you are only referencing one table in the update statement, the source and destination fields are coming from that one table which seems sorta pointless. Where do you expect the field values to come from to UPDATE [red][Customer Table][/red]?

 

[petermeachem]

What I meant was that looked like Access code. I think from memory that the Update would work in Access.
The first [First Name] is in the table, the second a textbox on the form.

If you stop the code on the RunSQL line and make the text in strsql a new query, access is pretty good (sometimes) of showing where the error is. Or print the contents of strsql out. Often easier to spot on paper.
Are any of the text boxes blank? Worth checking that the table accepts blanks.

 

[comgentech]

i really Thank you guys for all the help, I have most of my programming experiance from access. I am still very green in vb programming. One thing I have learned is not to tie tables to forms and that spaces are a pain so next time I will not use spaces. However this database is old and I am just tring to fix parts of it. Any way Some of the values of the text boxes will be blank. The Table is set to accept blanks and the code works great in access but when I try to use it in vb it gives an error.

 

[HarleyQuinn]

Can you show us how you are trying to implement this in VB code?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[comgentech]

Not sure what your asking but, I hope this helps.
The first query is on a button that adds a new customer from a form to our customer db. The second query is on a button that edits an existing customer on a form and then updates the information in the customer db. so the update query is suposed to change the information in a record except the customer ID witch never changes.

 

[comgentech]

This problem is fixed Thank you so much for all your help you guys are great.