Have an issue with my INSERT INTO string.

[PuddinPie]

I am trying to pull the values out of a form and dump them into a table. When I try I get the error that my INSERT INTO statement has a syntax error but no error number or what is causing the error. Is there a way of getting a better error description? Can anyone see anything wrong with the codeing?

Private Sub btnSubmit_Click()
On Error GoTo Err_btnSubmit_Click

Dim strSQL As String
strSQL = "INSERT INTO tblAllRequestData (RequestersName,RequestersPhoneNumber,RequestersLLID," & _
"RequestersEmail,EMPFirstName,EMPTeam,EMPLastName,EMPNewTerminalNum,EMPLLID,EMPNewDeskNum," & _
"EMPExpectedStartDate,EMPRole,EMPNewCostCenter,EMPTelephoney,EMPCompType," & _
"EMPSoft/SysAccessProfile,EMPDashboardProfile,EMPAdditionalSoft/SysAccess,EMPSpecialAccess/Furniture)" & _
"VALUES ([Forms].[frmNewEMPExternal].[txtReqName],[Forms].[frmNewEMPExternal].[txtReqPhone]," & _
"[Forms].[frmNewEMPExternal].[txtReqLLID],[Forms].[frmNewEMPExternal].[txtReqEmail]," & _
"[Forms].[frmNewEMPExternal].[txtEMPFirstName],[Forms].[frmNewEMPExternal].[txtEMPTeam]," & _
"[Forms].[frmNewEMPExternal].[EMPLastName],[Forms].[frmNewEMPExternal].[txtEMPTerminalNum]," & _
"[Forms].[frmNewEMPExternal].[txtEMPLLID],[Forms].[frmNewEMPExternal].[txtEMPDeskNum]," & _
"[Forms].[frmNewEMPExternal].[txtEMPStartDate],[Forms].[frmNewEMPExternal].[txtEMPRole)," & _
"[Forms].[frmNewEMPExternal].[txtEMPCostCenter],[Forms].[frmNewEMPExternal].[FrmTelephony]," & _
"[Forms].[frmNewEMPExternal].[FrmComputer],[Forms].[frmNewEMPExternal].[cboAccessProfile]," & _
"[Forms].[frmNewEMPExternal].[cboDashboardProfile],[Forms].[frmNewEMPExternal].[txtAdditionalAccess]," & _
"[Forms].[frmNewEMPExternal].[txtSpecialAccess]"

DoCmd.RunSQL strSQL
Exit_btnSubmit_Click:
Exit Sub
Err_btnSubmit_Click:
MsgBox err.Description
Resume Exit_btnSubmit_Click

End Sub

Thank you

 

[MarkK]

Refer to a named member of a collection like this or like that...

Forms!frmNewEMPExternal
Forms("frmNewEMPExternal")

...but not like this...

Forms.frmNewEMPExternal

Also, in some cases you may need to evaluate a reference outside of your SQL string. Consider...

dim a as long
dim b as long
dim sql as string

a=1:b=2
sql = "SELECT * FROM Table WHERE Field = " & a + b

Note how...

sql = "SELECT * FROM Table WHERE Field = a + b"

...would fail because in respect to the SQL the values of a and b are out of scope.
HTH,

 

[boblarson]

AND you have to surround items that put text into text fields with quotes and dates in date fields with octothorpes (#).

So like this (parts I added are in red):

"VALUES ([B][COLOR=red]" & Chr(34) &[/COLOR][/B] [Forms][B][COLOR=red]![/COLOR][/B][frmNewEMPExternal]![txtReqName] [COLOR=red][B]& Chr(34) & "[/B][/COLOR],[

 

[MarkK]

Octothorpes? That's cool. Never knew that.

 

[boblarson]

Octothorpes? That's cool. Never knew that.

Yeah, it has a real name - most people in the U.S. call it the pound sign but not sure what others outside the U.S. call it since a pound sign would be £.