Unknown problem with insert statement

[brainox]

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"

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) )" _
& " SELECT '" & Nz(Me!shy_forename, "") & "' AS Expr1, '" & Nz(Me!shy_surname, "") & "' AS Expr2, '" & Nz(Me!shy_empno, "") & " AS Expr3, " & Forms!frm_Capex_Submission.CAP_ID & " as expr4, """
dbs.Execute (sqlstr)
tbl_CapexStaff.Requery
End Sub

Many thanks in advance

 

[Isskint]

Replace SELECT with VALUES and remove the Expr1, Expr2....

SELECT is used to select data from a record source.

Also have a look at your syntax. The third value has an open apostrophe but no closing apostrophe. You have an extra pair of quotes " at the end of the line and no semicolon ; at the end ( semicolon is used to identify the end of an SQL statement)