RunSQL question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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'
 
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
 
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?
 
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
 
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.
 
This is what I have:
Dim strSQL As String
strSQL = "SELECT DBM FROM tblDBM"
CurrentDb.QueryDefs("TempQuery").sql = strSQL
DoCmd.OpenQuery "TempQuery"
 
Back
Top