Query will not run

[lawdy]

I have created many queries without any problems. But now all I have is problems. When I run the following query, I get a message saying "A RunSQL action requires an argument consisting of an SQL statement". The query is:

Dim strQue As String
strQue = "SELECT tblProposalWorksheet.fLngProposalNo " _
& "FROM tblProposalWorksheet " _
& "WHERE (((tblProposalWorksheet.fLngProposalNo)=1993074)); "

DoCmd.SetWarnings False
DoCmd.RunSQL strQue
DoCmd.SetWarnings True

Provided I have a table named 'tblProposalWorksheet' with a field in it named 'fLngProposalNo' and a record or two where the fLngProposalNo equals '1993074', is there any reason it should not run? Thanks in advance.

[RuralGuy]

Don't turn off the warnings and see what happens.

[Bob Fitz]

I think that "DoCmd.RunSQL" can only be used to run an Action query. Yours does not look like an action query to me.
Perhaps you should try:
DoCmd.OpenQuery

[RuralGuy]

From Help:

SQLStatement Required Variant. A string
expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE,
SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or
DROP INDEX statement. Include an IN clause if you want to
access another database.

It really needs to be an Action query and not a passive Select query. Look into:
CurrentDB.Execute SQL

[lawdy]

Neither DoCmd.OpenQuery or turning off the warnings has made any difference. Any other ideas?

[RuralGuy]

As I said, CurrentDB.Execute

[lawdy]

Ok. I created and saved a query and used 'CurrentDB.Execute myQuery'. It said "Can not excute a select query".

[ssanfu]

The commands "DoCmd.RunSQL " and "CurrentDB.Execute" only work with Action queries.

"DoCmd.OpenQuery" only works with saved queries (a valid name of a query in the current database). It will open a Select query or execute Action queries.

---------------------------------------------------------------------------------

Using VBA, you would use something like:

Public Sub test()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim strQue As String

    Set d = CurrentDb

    strQue = "SELECT tblProposalWorksheet.fLngProposalNo " _
             & "FROM tblProposalWorksheet " _
             & "WHERE (((tblProposalWorksheet.fLngProposalNo)=1993074)); "


    Set r = d.OpenRecordset(strQue)
    
    If Not (r.BOF And r.EOF) Then

        'Do something

    End If

    r.Close
    Set r = Nothing
    Set d = Nothing
End Sub

------------------------------------------------------------

Create a query, switch to SQL view and paste in:

SELECT tblProposalWorksheet.fLngProposalNo
FROM tblProposalWorksheet
WHERE (((tblProposalWorksheet.fLngProposalNo)=1993074));

Save the query as "Query3".

Then run this code:

Public Sub test2()
    Dim strQue As String

    strQue = "Query3"

    DoCmd.OpenQuery (strQue)

End Sub

The query opens and is visible..... You can add new data and do edits, but not very useful (IMO - no control on how data is entered - no validation). Using a form & filter the record source would be much better....


My $0.02

[RuralGuy]

Steve's got it nailed. Why do you want to run a Select query this way anyway?

[lawdy]

I looked back at my other queries, and they are all action queries. My thinking was just wrong. Thanks for your persistence with a hard headed newbie.

[RuralGuy]

Everyone has to start somewhere. Have fun. I marked this thread as Solved for you.