Have an issue with my INSERT INTO string.

Question

User84451058 posted

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,RequestersLL ID," & _
"RequestersEmail,EMPFirstName,EMPTeam,EMPLastName, EMPNewTerminalNum,EMPLLID,EMPNewDeskNum," & _
"EMPExpectedStartDate,EMPRole,EMPNewCostCenter,EMP Telephoney,EMPCompType," & _
"EMPSoft/SysAccessProfile,EMPDashboardProfile,EMPAdditional Soft/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

 

Answer 1

User2047364424 posted

If I get your issue right, then you are passing values in the forms to the insert statement right? If so, there are two reasons: 

- You have a wrong parenthesis on txtEMPRole , it should be "]" at the end

- The whole SQL should be: 

Dim strSQL As String
            strSQL = "INSERT INTO tblAllRequestData (RequestersName,RequestersPhoneNumber,RequestersLL ID," & _
            "RequestersEmail,EMPFirstName,EMPTeam,EMPLastName, EMPNewTerminalNum,EMPLLID,EMPNewDeskNum," & _
            "EMPExpectedStartDate,EMPRole,EMPNewCostCenter,EMP Telephoney,EMPCompType," & _
            "EMPSoft/SysAccessProfile,EMPDashboardProfile,EMPAdditional Soft/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] & "')"

Dim strSQL As String

            strSQL = "INSERT INTO tblAllRequestData (RequestersName,RequestersPhoneNumber,RequestersLL ID," & _

            "RequestersEmail,EMPFirstName,EMPTeam,EMPLastName, EMPNewTerminalNum,EMPLLID,EMPNewDeskNum," & _

            "EMPExpectedStartDate,EMPRole,EMPNewCostCenter,EMP Telephoney,EMPCompType," & _

            "EMPSoft/SysAccessProfile,EMPDashboardProfile,EMPAdditional Soft/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] & "')

However, try to test with a short statement with fewer parameters first

Answer 2

User84451058 posted

Thank you. I also figured that I can't have a "/" in my table names for them to work in the SQL string.  Everything it cool now.

Answer 3

User-1199946673 posted

Note that these forums are intended for ASP.NET related questions only. When you've a question like this you should find another forum.