Help with SQL executed through form event...

[Jason M Canady]

Hi all...

I am trying to execute the following SQL when duplicating a record on a main
form. If I am doing this right it should grab the associated records from
the subform and duplicated them as well, but the SQL is giving the following
error and I cannot seem to find it:

Syntax error on INSERT TO Statement...

my SQL is as follows:

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

When I duplicate the actual query that I need (or a close approximation
anyway!) I get this:

INSERT INTO WOEquipment ( WorkOrderID, EquipmentID )SELECT
WOEquipment.WorkOrderID, WOEquipment.EquipmentID

FROM WorkOrders INNER JOIN WOEquipment ON WorkOrders.KeyField =
WOEquipment.WorkOrderID WHERE
(((WOEquipment.WorkOrderID)=[WOrkORders].[KeyField]));



The only MAJOR differences that I see is in regards to the inner join... Do
you think that this is what is causing my error? Is there something else
that I am missing in the first SQL statement?



Any assistance would be very much appreciated.



Jason M

 

[John Viescas]

You have an extra comma before the FROM clause and you're missing a space
before the SELECT and WHERE. Is UniqueID a control on the current form or
is a variable in your code? If on the form, you should prefix with Me. Try
this:

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


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411