Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Winston-Salem, NC
    Posts
    3

    Unanswered: Help with INSERT INTO syntax

    Hello, I am new to VBA and I need some help. I have a ListBox named ListBox1 set to MultiSelect. I want to be able to select 1 or more records and save the 2nd field named Cust# of each record into a table named Table1. This is the best I can come up with reading other posts, but I get syntax errors on the INSERT INTO statement, and don't know if the looping code is correct. Some help would be most appreciated.

    Dim i As Integer
    Dim strIN As String

    For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
    strIN = ListBox1.Column(0, i)

    DoCmd.RunSQL "INSERT INTO Table1 [Cust#]" & _
    "VALUES [strIN]" & ";"


    End If
    Next i

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Yours:
    DoCmd.RunSQL "INSERT INTO Table1 [Cust#]" & _
    "VALUES [strIN]" & ";"

    Mine:
    doCmd.RunSql "INSERT INTO Table1 ([Cust#]) " & _
    "VALUES (" & strIn& ");"

    Not sure if Mine will work, but yours didn't have a space before 'VALUES', and you were trying to insert [strIN] as a value instead of a reference to your calculated string.

    Sometimes when SQL I write doesn't seem to work as expected, I'll put a break in the code, go to the debug window, spit out the SQL and paste into a new query's SQL pane, and look at the design view to see what I'm doing wrong.

    HTH.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jan 2004
    Location
    Winston-Salem, NC
    Posts
    3
    Originally posted by HomerBoo
    Yours:
    DoCmd.RunSQL "INSERT INTO Table1 [Cust#]" & _
    "VALUES [strIN]" & ";"

    Mine:
    doCmd.RunSql "INSERT INTO Table1 ([Cust#]) " & _
    "VALUES (" & strIn& ");"

    Not sure if Mine will work, but yours didn't have a space before 'VALUES', and you were trying to insert [strIN] as a value instead of a reference to your calculated string.

    Sometimes when SQL I write doesn't seem to work as expected, I'll put a break in the code, go to the debug window, spit out the SQL and paste into a new query's SQL pane, and look at the design view to see what I'm doing wrong.

    HTH.
    Thanks, this worked after I put a space in (" & strIn & ");"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •