RunSQL question

[Guest]

I have a form with a button that I want the user to be able to click to run a
query. I keep getting this error:
Run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement

I have reduced my query to the simplest form to eliminate other errors:
Private Sub cmdFindDBM_Click()
Dim strSQL As String
strSQL = "SELECT DBM FROM tblDBM"
DoCmd.RunSQL strSQL
End Sub

I cannot see what is wrong. I need to build a more complex query and I
can't even get this working! Any help would be appreciated.
Thank you,
Judy

 

[Guest]

docmd.runsql is used for executable sql statements, such as INSERT INTO or
UPDATE or DELETE queries. it will not work with a SELECT query.

what you need to do is have a control such as a list box and set the
rowsource = strsql when they click the button

such as:
Private Sub cmdFindDBM_Click()
Dim strSQL As String
strSQL = "SELECT DBM FROM tblDBM"
myListBox.rowsource = strSQL
myListBox.requery
End Sub

 

[Guest]

You need to use RunSQL on an action query, update, append, make table.

To run a select query you need to create a query and insert the sql into it,
and the run
Docmd.openquery QueryName

That will open the query result

 

[Guest]

Actually, I want the results of the query to be exported to an Excel
spreadsheet.

I have tried to learn something from other people's code. So far I have
found that I cannot declare a "Database" or a "DAO.Database" (I can declare
an ADODB). I don't see an option to declare any type of QueryDef.

Can you give me any help with these things? The object is to build a query
from a multi-select list box.

Thank you,
Judy

 

[Guest]

Now the question is, how do I define a query in code? I tried using:
DoCmd.OpenQuery strSQL
and got "Run-time error '7874';
Can't find the object 'SELECT DBM FROM tblDBM'

 

[Guest]

Create a query that contain the string
SELECT DBM FROM tblDBM

And then run the query,
you can also export the query as excel file

docmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel7,"QueryName",FileName and location

 

[Guest]

I know how to create a query in Access that is "hard-coded" with a condition.
And I know how to have a parameter that comes from a field on a form. But I
don't know how to create a query that lets the user choose from a
multi-select list box. Can you help with this?

 

[Guest]

I was really hoping this would work. I get:
Run-time error '3265':
Item not found in this collection

for the line:
CurrentDb.QueryDefs("TempQuery").sql = strSQL

If you know why I am getting this error I would appreciate hearing back from
you. Even if you don't, thank you so much for replying and trying!
Judy

 

[Guest]

did you created a query called TempQuery, just an empty one

 

[Guest]

did you create a new query called TempQuery, if not create a new query, dont
insert any SQL into it and save it as "TempQuery"

Open a new query in design view and just save it, the code will insert the
right SQL into it.

 

[Guest]

This is what I have:
Dim strSQL As String
strSQL = "SELECT DBM FROM tblDBM"
CurrentDb.QueryDefs("TempQuery").sql = strSQL
DoCmd.OpenQuery "TempQuery"