Run Query Based on Dynamically Constructed SQL

[SyntaxSocialist]

I've got a form (frmEdit) that allows users to (1) search tblMain, (2) view those results in a subform, and (3) view the current record in a set of bound controls.

I want to add code for a button to run a query (in table view) based on the user's search. Since every search will be a little bit different and there would be an infinite number of combinations of search criteria, I'm thinking I want to use a temporary QueryDef object. Maybe. I'm not totally sure... So how do I make that work? What I've got so far looks like:

Private Sub btnRunQuery_Click()

    Dim mySQL As String
    Dim strSel As String
    Dim strWhere As String
        
    'Build SELECT Statement (strSel) based on user input (checkboxes) [B][DONE][/B]

    'Build WHERE clause (strWhere) based on user input [B][DONE][/B]

    'Build mySQL [B][DONE][/B]
    mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere

    'Run a query based on mySQL [COLOR="Red"][B][GREAT MYSTERY OF LIFE][/B][/COLOR]

End Sub

So really, I just need to figure out what do with this dynamically constructed SQL in order to allow the user to run a query based on it with a click of the button.

 

[John Big Booty]

Have a look at the DoCmd.RunSQL method.

 

[SyntaxSocialist]

Have a look at the DoCmd.RunSQL method.

Hmm... so enticingly simple. But it's spitting back run-time error 2342: "A RunSQL action requires an argument consisting of an SQL statement."

Odd, considering that my DoCmd.RunSQL line simply reads:

DoCmd.RunSQL mySQL

and mySQL is most certainly an SQL statement; an example value for mySQL taken from the Immediate window:

SELECT [RecID],[StartDate],[EndDate],[LastName],[Sponsor],[Status],[Notes] _
FROM tblMain _
WHERE ([LastName] LIKE "Lee*" AND [StartDate] <= #5/1/2013# And [EndDate] >= #5/1/2010# _
And ([Status] = "Complete" OR [Status] = "Abandoned"))

The example at the link below indicates to me that

Dim qdf As QueryDef
Set qdf = .CreateQueryDef("", mySQL)

is close to what I'm looking for. Still having trouble getting it to co-operate, though.

http://office.microsoft.com/client/...&queryid=01ae95f2-af26-4033-824a-2db3b4c0a599

 

[SyntaxSocialist]

Or maybe this is what I need to be looking at... a whole other can of worms that one is... hopefully I can find something simpler.

 

[mdlueck]

No, I would not suggest fussing with trying to get DoCmd to perform SQL queries. Stick with DAO.QueryDef objects and perhaps add ADO objects.

 

[sxschech]

Here is an example of changing where clauses in sql statements with the query def. First one has a group by (summary query) second is a regular select query.

Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
 'change where clause to ATH Only
        Set qd = db.QueryDefs("qryRates")
        qd.SQL = Replace(qd.SQL, "GROUP BY", " AND ATHAID='ATH' GROUP BY")
        Set qd = db.QueryDefs("qrySource")
        qd.SQL = Replace(qd.SQL, ";", " WHERE ATHAID='ATH';")

If you want to view the query, in datasheet view from the code

DoCmd.OpenQuery "myquery", acViewNormal

 

[SyntaxSocialist]

Here is an example of changing where clauses in sql statements with the query def. First one has a group by (summary query) second is a regular select query.

Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
 'change where clause to ATH Only
        Set qd = db.QueryDefs("qryRates")
        qd.SQL = Replace(qd.SQL, "GROUP BY", " AND ATHAID='ATH' GROUP BY")
        Set qd = db.QueryDefs("qrySource")
        qd.SQL = Replace(qd.SQL, ";", " WHERE ATHAID='ATH';")

If you want to view the query, in datasheet view from the code

DoCmd.OpenQuery "myquery", acViewNormal

Thanks! Thought I already mentioned that this did the trick. Looks like I dropped the ball there. Sorry for the delay