Sql

[raystownlaura]

Below is some code that contains a SQL statement to run on the OnClick event of a button. When copying the SQL statement produced by the "stSQL" variable, and pasting into the SQL view of a new query, I get results just fine.

But I keep getting error 2342: "A RunSQL action requires an argument consisting of an SQL statement.", when it runs in code.

Variable stSQL, after being assigned returns in the immediate window as:

SELECT tblLiteratureInventory_InfoSite.ContactID, tblLiteratureInventory_InfoSite.Literature_BusinessName AS Literature_BusName, tblLiteratureInventory_InfoSite.LiteratureInfoSiteID, tblLiteratureInventory_InfoSite.DateCreated, tblLiteratureInventory_InfoSite.DateStocked, tblLiteratureInventory_InfoSite.QtyStocked, tblLiteratureInventory_InfoSite.StockedBy FROM tblLiteratureInventory_InfoSite WHERE (((tblLiteratureInventory_InfoSite.LiteratureInfoSiteID)=184) AND ((tblLiteratureInventory_InfoSite.DateCreated)=#5/26/2010#));

Pasting this into the SQL view of a query produces no errors so if anyone can tell me why I keep getting that error when the code runs, I'd appreciate it! Here's the code:

Private Sub cmdChangeDate_Click()
Dim iID As Integer
Dim rec As Recordset
Dim dt As Date
Dim stSQL As String


iID = [Forms]![frmContactDetails]![txtContactID]

dt = InputBox("Enter new stock date (mm/dd/yyyy):  ")

If vbYes = MsgBox("This will update all current records to " & dt & ". Proceed?", vbInformation + vbYesNo, "Update Stock Date") Then
    stSQL = "SELECT tblLiteratureInventory_InfoSite.ContactID, " _
    & "tblLiteratureInventory_InfoSite.Literature_BusinessName AS Literature_BusName, " _
    & "tblLiteratureInventory_InfoSite.LiteratureInfoSiteID, " _
    & "tblLiteratureInventory_InfoSite.DateCreated, tblLiteratureInventory_InfoSite.DateStocked, " _
    & "tblLiteratureInventory_InfoSite.QtyStocked, tblLiteratureInventory_InfoSite.StockedBy " _
    & "FROM tblLiteratureInventory_InfoSite WHERE " _
    & "(((tblLiteratureInventory_InfoSite.LiteratureInfoSiteID)=" & iID & ") AND " _
    & "((tblLiteratureInventory_InfoSite.DateCreated)=#" & dt & "#));"
    'run query to update all current records
    DoCmd.RunSQL (stSQL)
    
    Me.Requery 'refresh data
End If

End Sub

Thanks!!!

 

[DCrake]

RunSQL requires action type queries (Append, Update, Delete,Etc) not a Select query (which is what you have).

 

[raystownlaura]

Oops, I forgot about that. Been a while since I did access vba!

Thank you!!!!!