Results 1 to 4 of 4

Insert INTO SQL Statement

  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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,467
    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
    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
    46,082
    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?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  4. #4
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,603
    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, 05:00 PM
  2. Insert into with a WHERE statement
    By hazeleyre23 in forum Access
    Replies: 10
    Last Post: 04-06-2016, 07:28 AM
  3. INSERT statement
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 03-29-2013, 11:53 AM
  4. Insert Into statement
    By TimMoffy in forum Programming
    Replies: 7
    Last Post: 07-13-2012, 06:10 AM
  5. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 01: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
  •  
Tech Forums: Microsoft Office Forums