Results 1 to 6 of 6
  1. #1
    access kid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    3

    Sql statement WHERE in VBA failed to run

    Hi All,



    I made a vba script to run INERT INTO sql from table.CVTemp to CA.Temp with WHERE, but it always get failed on sentence which is with WHERE... and I don't know what went wrong...Can someone pls help? Thanks in advance! =]

    Btw, Me.ListInfo.Value is the value selected from listbox


    Code:
    DoCmd.RunSQL "INSERT INTO CATemp ( Origin, MAWB, Flight, ImportDate, Consignee, Qty, Wgt, HAWB, ShipmentNo )" & _
             "SELECT CVTemp.Origin, CVTemp.MAWB, CVTemp.Flight, CVTemp.ImportDate," & _
             "CVTemp.Consignee, CVTemp.Qty, CVTemp.Wgt, CVTemp.HAWB, CVTemp.ShipmentNo FROM CVTemp;" & _
             "WHERE CVTemp.MAWB=" & "'" & Me.ListInfo.Value & "';"

  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 would help:

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

    Offhand, you've got a semi-colon before the WHERE clause, which terminates the SQL statement. You're also likely to have a spacing problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    access kid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    3
    pbaldy,Thanks for your quick reponse =]I read the link you gave but sorry i still can't my mistake in the scriptI have revised it into below, but it still doesn't work
    Code:
    DoCmd.RunSQL "INSERT INTO CATemp ( Origin, MAWB, Flight, ImportDate, Consignee, Qty, Wgt, HAWB, ShipmentNo )" & _         "SELECT CVTemp.Origin, CVTemp.MAWB, CVTemp.Flight, CVTemp.ImportDate," & _         "CVTemp.Consignee, CVTemp.Qty, CVTemp.Wgt, CVTemp.HAWB, CVTemp.ShipmentNo FROM CVTemp" & _         "WHERE CVTemp.MAWB =" & Me.ListInfo.Value

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try the technique in the link (no, because you still aren't using a variable for the SQL)? As I also mentioned, you have a spacing problem that would probably be obvious if you used that debugging technique:


    "...ShipmentNo )SELECT ..."

    Note the lack of a space before SELECT.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    access kid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    3
    Hi pbaldy,

    Thanks so much I have get problem solved.
    As a beginner of access & vba for 2 weeks, so good i can see experts like you here =]

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. the operation on the ole object failed
    By merlin777 in forum Access
    Replies: 5
    Last Post: 09-05-2011, 05:43 PM
  2. ODBC -- Call Failed error
    By Coffee in forum Access
    Replies: 3
    Last Post: 07-08-2011, 10:34 AM
  3. Action Failed 2950
    By TracyBell in forum Access
    Replies: 1
    Last Post: 05-02-2011, 11:00 AM
  4. ODBC--call failed
    By TCB in forum Access
    Replies: 4
    Last Post: 04-28-2011, 08:23 AM
  5. Failed to update all field
    By somya123 in forum Queries
    Replies: 0
    Last Post: 01-30-2009, 12:52 AM

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