Access Sql State not recognized

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
Engtech05Asked:
Who is Participating?
 
Patrick MatthewsCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
DoCmd.RunSQL is only for Action queries (update, append, delete, maketable)

Try

DoCmd.OpenQuery strSql
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
And actually, the argument for OpenQuery has to be the Name of a saved query.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Engtech05Author Commented:
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
 
Patrick MatthewsCommented:
Then please see my comment http:#a36944484

This is a classic "query by form" scenario.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, then I would do what matthewspatrick suggested.

mx
0
 
Engtech05Author Commented:
Ok, where does the query language go?
0
 
Patrick MatthewsCommented:
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
 
Engtech05Author Commented:
Yeah Yippie, You are da Man. I wish I could think better than I do sometimes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.