locked
Have an issue with my INSERT INTO string. RRS feed

  • 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

     

    Wednesday, December 1, 2010 11:07 AM

Answers

  • 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


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 1, 2010 2:21 PM

All replies

  • 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


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 1, 2010 2:21 PM
  • 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.

    Wednesday, December 1, 2010 3:47 PM
  • 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.

    Wednesday, December 1, 2010 4:21 PM