I have written the following Insert Into statement in Access 2010 VBA:
Private Sub AddBPSSButton_Click()
' CurrentDb.Execute "INSERT INTO TabClearDetail(C_Site) VALUES(" & Me.C_Site & ")"
Dim strSQL As String
'MsgBox Me.[Clearance Applying For]
'MsgBox Me.[Contract Applying for]
'MsgBox Me.[C_Site]
'MsgBox Me.[C_SponsorSurname]
'MsgBox Me.[C_SponsorForename]
'MsgBox Me.[C_SponsorContactDetails]
'MsgBox Me.[C_EmploymentDetail]
'MsgBox Me.[C_SGNumber]
'MsgBox Me.[C_REF1DateRecd]
'MsgBox Me.[C_REF2DateRecd]
'MsgBox Me.[C_IDDateRecd]
'MsgBox Me.[C_IDNum]
'MsgBox Me.[C_CriminalDeclarationDate]
'MsgBox Me.[Credit Check Consent]
'MsgBox Me.[C_CreditCheckDate]
'MsgBox Me.[Referred for Management Decision]
'MsgBox Me.[Management Decision Date]
'MsgBox Me.[C_Comment]
'MsgBox Me.[C_DateCleared]
'MsgBox Me.[C_ClearanceLevel]
'MsgBox Me.[C_ContractAssigned]
'MsgBox Me.[C_ExpiryDate]
'MsgBox Me.[C_LinKRef]
'MsgBox Me.[C_OfficialSecretsDate]
strSQL = "INSERT INTO TabClearDetail(Clearance Applying For, Contract Applying for, " & _
"C_Site, C_SponsorSurname, C_SponsorForename, C_SponsorContactDetails, C_EmploymentDetail, " & _
"C_SGNumber, C_REF1DateRecd, C_RED2DateRecd, C_IDDateRecd, C_IDNum, " & _
"C_CriminalDeclarationDate, Credit Check Consent, C_CreditCheckDate, Referred for Management Decision, " & _
"Management Decision Date, C_Comment, C_DateCleared, C_ClearanceLevel, C_ContractAssigned, " & _
"C_ExpiryDate, C_LinkRef, C_OfficialSecretsDate) VALUES('" & Me.[Clearance Applying For] & "', " & _
"'" & Me.[Contract Applying for] & "', '" & Me.[C_Site] & "', '" & Me.[C_SponsorSurname] & "', " & _
"'" & Me.[C_SponsorForename] & "', '" & Me.[C_SponsorContactDetails] & "', " & _
"'" & Me.[C_EmploymentDetail] & "', '" & Me.[C_SGNumber] & "', '" & Me.[C_REF1DateRecd] & "', " & _
"'" & Me.[C_REF2DateRecd] & "', '" & Me.[C_IDDateRecd] & "', '" & Me.[C_IDNum] & "', " & _
"'" & Me.[C_CriminalDeclarationDate] & "', '" & Me.[Credit Check Consent] & "', '" & Me.[C_CreditCheckDate] & "', " & _
"'" & Me.[Referred for Management Decision] & "', '" & Me.[Management Decision Date] & "', " & _
"'" & Me.[C_Comment] & "', '" & Me.[C_DateCleared] & "', '" & Me.[C_ClearanceLevel] & "', " & _
"'" & Me.[C_ContractAssigned] & "', '" & Me.[C_ExpiryDate] & "', '" & Me.[C_LinKRef] & "', " & _
"'" & Me.[C_OfficialSecretsDate] & "');"
DoCmd.RunSQL (strSQL)
'MsgBox strSQL
End Sub
All The MsgBox calls work, so I believe I have typed all column names and text box names correctly. I am getting a Syntax error when I get to the DoCmd.RunSQL line. Have been staring at this for quite a while trying to see if I have missed a comma or speech mark or something, but am hoping maybe another set of eyes will see my mistake.
Any help will be greatly appreciated.
Thanks!
