title: Unknown issue with insert statment
body: <p>I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"</p>
  
  <pre><code>Private Sub Command3_Click()
  Dim dbs As Database
  Dim sqlstr As String
  Set dbs = CurrentDb
  Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
  Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
  emp_no = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_empno, "")
  CAP_ID = Forms!frm_Capex_Submission!CAP_ID
  
  sqlstr = "INSERT INTO tbl_CapexStaff ( Forename, Surname, EmployeeID, CAP_ID) )" _
  &amp; " SELECT '" &amp; Nz(Me!shy_forename, "") &amp; "' AS Expr1, '" &amp; Nz(Me!shy_surname, "") &amp; "' AS Expr2, '" &amp; Nz(Me!shy_empno, "") &amp; " AS Expr3, " &amp; Forms!frm_Capex_Submission.CAP_ID &amp; " as expr4, """
  dbs.Execute (sqlstr)
  tbl_CapexStaff.Requery
  End Sub
  </code></pre>