Access Append Query

I'm getting a Run-Time error '3134'
Syntax error in the INSERT INTO statement

here is the code:

Code:

---

      sqll = "INSERT INTO TBl_aus_history ( SERVICE_NUM, Paid, DATE_PAID, DATE_RECEIVED, CLAIM_NUM, Member_Name, MEMBER_NUM, PROGRAM_CODE, Provider_Name, PROVIDER_NUM, Refund_Type, Reason_Overpay, Identified_by_Source," & _
      "Memo_a, Dept_Error, Date_Requested, Date_Service, Product, REGION_CODE, [IRS#], Refund_Letter_Type, DATE_EFFECTIVE, [User], Computer, OPID, Date_Rec_Recovery, Provider_Account_Num" & _
      "SELECT Dynamic_Table.SERVICE_NUM, Dynamic_Table.Paid, Dynamic_Table.DATE_PAID, Dynamic_Table.DATE_RECEIVED, Dynamic_Table.CLAIM_NUM, Dynamic_Table.Member_Name, Dynamic_Table.MEMBER_NUM," & _
      "Dynamic_Table.PROGRAM_CODE, Dynamic_Table.Provider_Name, Dynamic_Table.PROVIDER_NUM, [Forms]![Frm_Data_Entry]![cmbRefun_Type] AS Refund_Type," & _
      "[Frm_Data_Entry]![cmbReason_Overpay]) AS Reason_Overpay, [Forms]![Frm_Data_Entry]![cmbSource] AS Identified_by_Source," & _
      "[Forms]![Frm_Data_Entry]![txtmemo] AS Memo_a, [Forms]![Frm_Data_Entry]![cmbDept_Error] AS Dept_Error, CDate([Forms]![Frm_Data_Entry]![txtrequested]) AS Date_Requested," & _
      "CDate([Forms]![Frm_Data_Entry]![txtSdos]) AS Date_Service, [Forms]![Frm_Data_Entry]![cmbProduct] AS Product, Dynamic_Table.REGION_CODE, Dynamic_Table.[IRS#], [Forms]![Frm_Data_Entry]![txtoption] AS Refund_Letter_Type," & _
      "Dynamic_Table.DATE_EFFECTIVE, [Forms]![Frm_Data_Entry]![txtuser] AS [User], [Forms]![Frm_Data_Entry]![txtcomputer] AS Computer, [Forms]![Frm_Data_Entry]![txt_OPID] AS OPID," & _
      "CDate([Forms]![Frm_Data_Entry]![txtDRR]) AS Date_Rec_Recovery, Dynamic_Table.Provider_Account_Num" & _
      "FROM Dynamic_Table"
   
      DoCmd.RunSQL sqll

---

You can't embed form variables within a SQL string because the parser WILL NOT look for the values in those forms ... You have to piece the SQL string together with the values yourself.

Could you not do it in one of the events of the form?

Be careful with your commas and spacing.

If you were to display your SQL statement in a message box you would see that the SELECT statement is getting caught:

...Provider_Account_NumSELECT Dynamic_Table...

So, the SELECT statement isn't being found.


Also, a querydef would be better as it will reduce database bloat as the query is already created rather than recreating a query each time. There's no need to in this instance as the query is identical every time. Better to just create it in the QBE.