SQL INSERT

Discussion in 'Microsoft Access VBA Modules' started by ydbc, Mar 16, 2010.

  1. ydbc

    ydbc Guest

    Hi,

    Please help.

    I am attemping to INSERT INTO a simple table. All I want to do is INSERT
    INTO a text field. This is the syntax I'm using:

    Dim NewCol As String
    NewCol = Me.txtNewColName
    DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (" &
    NewCol & " )"

    I keep getting "Enter parameter value". I'm really stuck.

    Thanks in anticipation.
     
    ydbc, Mar 16, 2010
    #1
    1. Advertisements

  2. ydbc

    John Spencer Guest

    You are missing the text delimiters.

    DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (""" &
    NewCol & """ )"

    OR

    DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES ('" &
    NewCol & "' )"

    OR


    DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) VALUES (" &
    Chr(34) & NewCol & Chr(34) & ")"
    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County
     
    John Spencer, Mar 16, 2010
    #2
    1. Advertisements

  3. hi,

    Rewrite it:

    Dim sql As String

    sql = "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
    "VALUES (" & txtNewColName.Value & " )"
    MsgBox sql
    DoCmd.RunSQL sql

    This will show you your error.
    As your value is a string, you need to enclose it into quotation marks.

    E.g.

    Public Function SqlQuote(AValue As Variant, _
    Optional ADelimiter As String = "'") As String

    If IsNull(AValue) Then
    SqlQuote = "NULL"
    Else
    SqlQuote = ADelimiter & _
    Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
    ADelimiter
    End If

    End Function

    use it as

    sql = "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
    "VALUES (" & SqlQuote(txtNewColName.Value) & " )"


    mfG
    --> stefan <--
     
    Stefan Hoffmann, Mar 16, 2010
    #3
  4. ydbc

    Sparker Guest

    'Surround the string value with single quotes just inside the double quotes
    like this:

    DoCmd.RunSQL "INSERT INTO tblVulnerableGroup ([VulnerableGroup]) " & _
    "VALUES ('" & Me.txtNewColName & "');"
     
    Sparker, Mar 17, 2010
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.