Trouble with Append Query

[AdamOnAccess]

I can't figure this out...

I'm in Windows XP with Access 2007. I have this code in my form's class module...

mySql = "INSERT INTO mtb_PartWordsSavedDetail ( PartWordsSavedHeadID, PartWords )"
       mySql = mySql & " SELECT CLng(" & Me!PartWordsSavedHeadID & ") AS [HeadID], mtb_PartWordsTemp.PartWordTemp"
       mySql = mySql & " FROM mtb_PartWordsTemp"
       mySql = mySql & " WHERE mtb_PartWordsTemp.PartWordsBox=" & PartBox & ";"
       Debug.Print mySql
 
       DoCmd.RunSQL mySql
 

When I run it through the form, my table does not update, but the immediate window does produce the final Sql in mySql:

INSERT INTO mtb_PartWordsSavedDetail ( PartWordsSavedHeadID, PartWords ) SELECT CLng(16) AS [HeadID], mtb_PartWordsTemp.PartWordTemp FROM mtb_PartWordsTemp WHERE mtb_PartWordsTemp.PartWordsBox=1;
 

When I copy and paste the above SQL into a query, it runs fine and the table updates.

I feel the problem has something to do with the [HeadID] getting converted into a string by the &'s. I tried using the CLng function to convert to a long integer. Again, the Sql updates the table correctly when I run it through a query, but it will not work in the module.

Can anyone help?
Thanks,
Adam

[NeoPa]

Is it possible that the table is locked when the form is open? Perhaps the form is bound to that table.

Try running the SQL as a query immediately before it executes line #7 of your code.

You will need to stop the code at that point. See Debugging in VBA for help on stopping the code.