Results 1 to 2 of 2
  1. #1
    Evilferret is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2012

    Compile Error: Syntax Error in DoCmd.RunSQL Statement

    I am running into a syntax error in my DoCmd.RunSQL

    The SQL statement runs fine independent of the VBA code (Assuming I replace the variable with a valid ID number)

    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim rstTableInventory As DAO.Recordset
    Set rstTableInventory = dbs.OpenRecordset("Inventory")
    InventoryPartID = rstTableInventory!PartID
    TopRecordID = DoCmd.RunSQL "SELECT TOP 1 TempID FROM Temp_Report_PurchaseOrderDetails WHERE PartID = "& InventoryPartID &" AND QtyShipped<QtyRequired ORDER BY PurchaseOrderDate;"

    Looked into it a bit more. DoCmd.RunSQL is intended for SQL that affects the data in the database. It's not designed to return values back to variables, and it won't work in this manner. Looking for an alternative way to retrieve the value. Ideas?

    Edit #2: SOLVED
    DoCmd.RunSQL returns a recordset or something. It doesn't work with a variable, even if you're only selecting a single piece of data. Instead, use DLookup which will return a VALUE from a recordset:

    TopRecordID = DLookup("TempID", "Temp_Report_PurchaseOrderDetails", "PartID = " & InventoryPartID & " AND QtyShipped<QtyRequired")
    For more information on DLookup:

  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    space out your "" marks and your ampersand markings. that's probably it.

    and if your code is on two lines, include the "_" line continuation character. that's not needed in access's sql builder.

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

Similar Threads

  1. Syntax Error with DoCmd.RunSQL
    By dandoescode in forum Programming
    Replies: 2
    Last Post: 06-25-2012, 11:06 AM
  2. DoCmd.OpenForm Syntax Error
    By alsoto in forum Forms
    Replies: 3
    Last Post: 02-29-2012, 01:14 PM
  3. Compile Error Syntax Error
    By Mohamed in forum Access
    Replies: 3
    Last Post: 10-06-2011, 10:12 AM
  4. problem with my DoCmd.RunSQL statement
    By cgclower in forum Access
    Replies: 1
    Last Post: 07-24-2011, 06:12 PM
  5. compile error: expected end of statement
    By RedGoneWILD in forum Programming
    Replies: 5
    Last Post: 07-29-2010, 10:12 AM

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