Syntax error with the following code...

[Jason M Canady]

Hi, I am recieving the following error with the sql that I am trying to use
to add records to a datatable.

Run-Time Error '3134'
Syntax Error in INSERT INTO Statement

The code as it is written right now is:

sSQL = "INSERT INTO WOEquipment(WorkOrderID,EquipmentID) " & "SELECT " &
UniqueID & " As NewWorkOrderID, WOEquipment.EquipmentID, " & "FROM
WorkOrders INNER JOIN WOEquipment ON WorkOrders.KeyField =
WOEquipment.WorkOrderID" & "WHERE (((WOEquipment.WorkOrderID) = " &
Me.KeyField & "));"

I have tried various other tweaks, the only other major chage was:

sSQL = "INSERT INTO WOEquipment(WorkOrderID,EquipmentID) " & "SELECT " &
UniqueID & " As NewWorkOrderID, WOEquipment.EquipmentID, " & "FROM
WOEquipment " & "WHERE (((WOEquipment.WorkOrderID) = " & Me.KeyField &
"));"

(Note, no INNER JOIN statement...) I am not real sure where to go from
here, any assistance would be very helpful. Thanks in advance

Jason

 

[John Vinson]

I have tried various other tweaks, the only other major chage was:

sSQL = "INSERT INTO WOEquipment(WorkOrderID,EquipmentID) " & "SELECT " &
UniqueID & " As NewWorkOrderID, WOEquipment.EquipmentID, " & "FROM
WOEquipment " & "WHERE (((WOEquipment.WorkOrderID) = " & Me.KeyField &
"));"

You've got an extra comma before the FROM keyword. REmove the comma
after WOEquipment.EquipmentID and you should be better off.

It always helps to step through the code and view what's actually
ending up in sSQL in cases like this! You can copy and paste the
string from the Immediate window - view it by typing

?sSQL

in the debug window - into the SQL view of a new Query, and see what
error is generated.

 

[Jason M Canady]

John, Thanks bunches and bunches!!!

A darn comma! I can't tell you how many times I looked at that code and
could not figure it out! It now works perfectly! I will run some
additional tests this weekend to finish it off, but from a first glance it
looks as though it is working well.

Thanks again!

Jason