Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Sql State not recognized

Posted on 2011-10-10
10
Medium Priority
?
440 Views
Last Modified: 2013-11-28
Ok, from this little bit of code I keep getting thios error:
Error # 2342
A RunSQL action requires an arguemtn consisting of an SQL statement.

When the code runs the msgbox shows me the sql statement.
However the Docmd.RunSql (strSql) gives that error.

Thanks ahead of Time.


Private Sub cmdGen_Click()
Dim strSql, strType, StrDesc As String
strType = Me!AssetType
StrDesc = Me!AssetDescription
strSql = "SELECT * "
strSql = strSql & "FROM Asset WHERE [Asset].[AssetType] = '" & strType & "'"
strSql = strSql & " And [Asset].[AssetDescription] = '" & StrDesc & "';"
MsgBox strSql
DoCmd.SetWarnings False
DoCmd.RunSQL (strSql)
DoCmd.SetWarnings True
DoCmd.OpenReport "Asset", acViewPreview

End Sub
0
Comment
Question by:Engtech05
  • 4
  • 3
  • 3
10 Comments
 
LVL 75
ID: 36944467
DoCmd.RunSQL is only for Action queries (update, append, delete, maketable)

Try

DoCmd.OpenQuery strSql
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36944484
RunSQL is used for "action" queries, not for a straight SELECT.

If you are trying to filter a report based on form entries, I would simply set up my query as:

SELECT * 
FROM Asset 
WHERE [AssetType] = Forms![NameOfForm]![AssetType] And 
    [AssetDescription] = Forms![NameOfForm]![AssetDescription]

Open in new window


and base the report on that query.
0
 
LVL 75
ID: 36944509
And actually, the argument for OpenQuery has to be the Name of a saved query.

mx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75
ID: 36944576
Try this:

    Dim strSql, strType, StrDesc As String
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Set db = CurrentDb
   
    strType = Me!AssetTypeQueryDef
    StrDesc = Me!AssetDescription
    strSql = "SELECT * "
    strSql = strSql & "FROM Asset WHERE [Asset].[AssetType] = '" & strType & "'"
    strSql = strSql & " And [Asset].[AssetDescription] = '" & StrDesc & "';"

    Set qdf = db.CreateQueryDef("qryTemp", strSql)
    DoCmd.SetWarnings False
    DoCmd.OpenQuery qdf.Name
    DoCmd.SetWarnings True
    DoCmd.OpenReport "Asset", acViewPreview
    Set qdf = Nothing
    Set db = Nothing

this is just going to open a query.  

Not clear on what you are trying to do?
0
 

Author Comment

by:Engtech05
ID: 36944600
I am trying to base a report on the query that comes from a form that the user selects the asset type and description.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36944623
Then please see my comment http:#a36944484

This is a classic "query by form" scenario.
0
 
LVL 75
ID: 36944630
Well, then I would do what matthewspatrick suggested.

mx
0
 

Author Comment

by:Engtech05
ID: 36944651
Ok, where does the query language go?
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 36944667
Open your query in design view, and then switch to SQL view.

That will display the SQL statement for the query.  Replace the SQL statement with what I gave you (be sure to update the name of the form, of course!).
0
 

Author Closing Comment

by:Engtech05
ID: 36944871
Yeah Yippie, You are da Man. I wish I could think better than I do sometimes.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Loops Section Overview

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question