Results 1 to 4 of 4
  1. #1
    tecknologikal is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    1

    Insert INTO SQL Statement

    Can someone please help me with this SQL statement. I keep getting a missing ; at end of statement error:

    Private Sub cmdInsert_Click()
    Dim counter As Integer
    Dim strsql As String
    Dim appid As String
    appid = Forms![frmAppDkt]![AppCaseID]




    strsql = "insert into tbl_aple (aple_name, AppCaseID, Cal, Calatty, FFnumb, Dkt, Atty) Values (client, appid, calendar, code, ff, docket, attorney) from qryffnumb WHERE qryffnumb.docket =" & "'" & Forms("frmclient").List0.Column(2, counter) & "'");"




    For counter = 0 To Forms("frmclient").List0.ListCount
    If Forms("frmclient").List0.Selected(counter) Then
    DoCmd.SetWarnings False
    CurrentDb.Execute strsql

    End If
    Next counter
    DoCmd.SetWarnings True


    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This should help:

    http://www.baldyweb.com/ImmediateWindow.htm

    but your primary issue is a syntax problem. It's either

    INSERT INTO...
    VALUES...

    or

    INSERT INTO...
    SELECT...
    FROM...
    WHERE...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I wonder why you are duplicating so many fields to multiple records.

    Have to concatenate appID.

    strsql = "INSERT INTO tbl_aple (aple_name, AppCaseID, Cal, Calatty, FFnumb, Dkt, Atty) SELECT client, " & appid & ", calendar, code, ff, docket, attorney FROM qryffnumb WHERE qryffnumb.docket ='" & Forms("frmclient").List0.Column(2, counter) & "'");"

    No need to use SetWarnings with CurrentDb.Execute.

    Is this a multi-select listbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You also have a redundant closing bracket (in red) at the end of the statement:

    strsql = "INSERT INTO tbl_aple (aple_name, AppCaseID, Cal, Calatty, FFnumb, Dkt, Atty) SELECT client, " & appid & ", calendar, code, ff, docket, attorney FROM qryffnumb WHERE qryffnumb.docket ='" & Forms("frmclient").List0.Column(2, counter) & "'");"

    In fact, all three red characters should be removed, as they will cause a syntax error.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. INSERT INTO statement
    By berderder in forum Programming
    Replies: 4
    Last Post: 06-03-2016, 06:00 PM
  2. Insert into with a WHERE statement
    By hazeleyre23 in forum Access
    Replies: 10
    Last Post: 04-06-2016, 08:28 AM
  3. INSERT statement
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 03-29-2013, 12:53 PM
  4. Insert Into statement
    By TimMoffy in forum Programming
    Replies: 7
    Last Post: 07-13-2012, 07:10 AM
  5. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums