Compile Error: Syntax Error in DoCmd.RunSQL Statement

[Evilferret]

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;"

Edit: 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:
http://www.techonthenet.com/access/f...in/dlookup.php

[help_me_with_access]

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.