Syntax issue with DoCmd.RUNSQL

[mrheffner]

Hello, Please help.


I am trying to make a copy of a record into the same table. When this is complete it will be a feature which allows my users to create Template records which can then be used when creating work orders.


I am getting a syntax error on the INSERT INTO. I can't see what I am doing wrong.


Here is the code:

    SQL = "INSERT INTO [t_Material Consumed]([Material Name], [Color#], Percent) " & _
          "SELECT [Material Name], [Color#], Percent " & _
          "FROM [t_Material Consumed] " & _
          "WHERE ([Consuming Work Order] = '" & currentID & "');"
          
    MsgBox (SQL)
 
    DoCmd.RunSQL SQL

I uploaded an attachment with the info from the MsgBox. Any help would be greatly appreciated.


Btw, the fields included are not everything. I just included those to keep it simple while debugging.


Mike

 

[theDBguy]

Hi. Maybe "Percent" is a reserved word. Try enclosing it in brackets, e.g. [Percent]. Hope it helps...

 

[pbaldy]

This is an easier way to get the SQL, since you can copy/paste it into a new query for testing.

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

 

[Gasman]

IDs are normally numeric, you have yours as text? Is it text?

A display of the MSGBOX SQL would help?

 

[pbaldy]

A display of the MSGBOX SQL would help?

Check the attachment.

 

[Gasman]

Check the attachment.

Oops, sorry, missed that completely. :banghead:

 

[mrheffner]

It was the [Percent]. It is working now. The other suggestions were also helpful. Thanks to everyone.

 

[theDBguy]

It was the [Percent]. It is working now. The other suggestions were also helpful. Thanks to everyone.

Hi. Congratulations! Glad to hear you got it to work. We were all happy to assist. Good luck with your project.