Coded SQL into Query View?

[Pendragon]

I have two unbound text boxes for user input in order to do a quick search.
Based on the selection of one of two check boxes (individuals or companies),
the criteria entered in the text boxes are written into an SQL statement.

My command button to preview the data writes the SQL statement based on the
selections. What is the best way to then display the results? If I remember
correctly, DoCmd.RunSQL expects an action statement. Also, DoCmd.OpenQuery
is expected a named query, not a SQL statement (or string variable containing
such).

Suggestions?

 

[Pendragon]

Never mind!! I modified my search a few times more in the newsgroup and
found a solution using QueryDef.

 

[Pendragon]

Okay, I lied. I created the querydef, and following the Help File for
CreateQueryDef I am at the point where I believe the recordset exists.
However, like so many other Help pages, this help page is impractical in the
real world because it only shows you how to print the SQL and number of
records in the immediate window. Come on Microsoft! Get real.

So now that I have the query def, I'm still stuck at the point from the
first post - how do I get the results to display?

 

[fredg]

I have two unbound text boxes for user input in order to do a quick search.
Based on the selection of one of two check boxes (individuals or companies),
the criteria entered in the text boxes are written into an SQL statement.

My command button to preview the data writes the SQL statement based on the
selections. What is the best way to then display the results? If I remember
correctly, DoCmd.RunSQL expects an action statement. Also, DoCmd.OpenQuery
is expected a named query, not a SQL statement (or string variable containing
such).

Suggestions?

You can use code to change an already existing query's SQL.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName")
Dim strSQL as String

strSQL = "Select YourTable.[FieldA], YourTable.[FieldB] from
YourTable Order by [FieldA];"

DoCmd.OpenQuery "YourQueryName", acViewDesign
qdf.SQL = strSQL
DoCmd.Close acQuery, "YourQueryName", acSaveYes

DoCmd.OpenQuery "YourQueryName"

 

[fredg]

Okay, I lied. I created the querydef, and following the Help File for
CreateQueryDef I am at the point where I believe the recordset exists.
However, like so many other Help pages, this help page is impractical in the
real world because it only shows you how to print the SQL and number of
records in the immediate window. Come on Microsoft! Get real.

So now that I have the query def, I'm still stuck at the point from the
first post - how do I get the results to display?

I'm not sure my previous reply went.
Here it is again.

You can use code to change an already existing query's SQL.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName")
Dim strSQL as String

strSQL = "Select YourTable.[FieldA], YourTable.[FieldB] from
YourTable Order by [FieldA];"

DoCmd.OpenQuery "YourQueryName", acViewDesign
qdf.SQL = strSQL
DoCmd.Close acQuery, "YourQueryName", acSaveYes

DoCmd.OpenQuery "YourQueryName"

 

[Pendragon]

Thanks. I can certainly use this in other operations I have, but with the
present project (which I will use elsewhere), I would rather not create a
saved query. There has to be some way of writing a SQL statement in VBA and
then viewing the results.

I have two unbound text boxes for user input in order to do a quick search.
Based on the selection of one of two check boxes (individuals or companies),
the criteria entered in the text boxes are written into an SQL statement.

My command button to preview the data writes the SQL statement based on the
selections. What is the best way to then display the results? If I remember
correctly, DoCmd.RunSQL expects an action statement. Also, DoCmd.OpenQuery
is expected a named query, not a SQL statement (or string variable containing
such).

Suggestions?

You can use code to change an already existing query's SQL.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName")
Dim strSQL as String

strSQL = "Select YourTable.[FieldA], YourTable.[FieldB] from
YourTable Order by [FieldA];"

DoCmd.OpenQuery "YourQueryName", acViewDesign
qdf.SQL = strSQL
DoCmd.Close acQuery, "YourQueryName", acSaveYes

DoCmd.OpenQuery "YourQueryName"

 

[Jim Burke in Novi]

If you're looking to display the query results the same way you would when
you execute a query thru the Access panels, in a grid format, I don't know of
a way to do that, though it may be possible. I would normally show them in a
report or via a form or in a control on a form or in a message box.

 

[pietlinden]

You could use the technique from Access Developer's Handbook that they
use in the SQL Scratchpad... or you could create the SQL statement and
assign it as the rowsource for an unbound subform in Datasheet view in
a form...

 

[fredg]

Thanks. I can certainly use this in other operations I have, but with the
present project (which I will use elsewhere), I would rather not create a
saved query. There has to be some way of writing a SQL statement in VBA and
then viewing the results.

I have two unbound text boxes for user input in order to do a quick search.
Based on the selection of one of two check boxes (individuals or companies),
the criteria entered in the text boxes are written into an SQL statement.

My command button to preview the data writes the SQL statement based on the
selections. What is the best way to then display the results? If I remember
correctly, DoCmd.RunSQL expects an action statement. Also, DoCmd.OpenQuery
is expected a named query, not a SQL statement (or string variable containing
such).

Suggestions?

You can use code to change an already existing query's SQL.

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName")
Dim strSQL as String

strSQL = "Select YourTable.[FieldA], YourTable.[FieldB] from
YourTable Order by [FieldA];"

DoCmd.OpenQuery "YourQueryName", acViewDesign
qdf.SQL = strSQL
DoCmd.Close acQuery, "YourQueryName", acSaveYes

DoCmd.OpenQuery "YourQueryName"

Regarding > There has to be some way of writing a SQL statement in VBA
and then viewing the results.<
You are, I believe, mistaken. There is no way to run a new Select
query using just VBA.
You can create a brand new query (using CreateQueryDef) and view that,
but that is still a query.
It's easiest to simply create any query and save it in the database.
Then modify it's SQL as I've indicted in my previous reply.

 

[Pendragon]

This is where I started to go. In my test run, the record navigation buttons
are showing a correct record count, so I know the SQL is being accurately
executed. However, I'm not sure what to do in terms of displaying the
records. Even though I have Datasheet View on the subform, I have to
manually create the fields on the subform. While not a big deal for this
query, this form/subform is going to be used for different queries with
different fields and varying numbers of fields.

How does one vary what fields are displayed on a form in a datasheet view
from query to query?

 

[Stuart McCall]

DoCmd.OpenQuery "YourQueryName", acViewDesign

qdf.SQL = strSQL
DoCmd.Close acQuery, "YourQueryName", acSaveYes

Hi Fred

Actually you don't need the OpenQuery or DoCmd.Close lines. Just qdf.SQL =
<whatever> will do it.

 

[pietlinden]

Okay, I lied. I created the querydef, and following the Help File for
CreateQueryDef I am at the point where I believe the recordset exists.
However, like so many other Help pages, this help page is impractical in the
real world because it only shows you how to print the SQL and number of
records in the immediate window. Come on Microsoft! Get real.

So now that I have the query def, I'm still stuck at the point from the
first post - how do I get the results to display?

if you use a querydef and modify the SQL property, you can use
DoCmd.OpenQuery qdf.Name

and it will open in normal view as if you opened it from the queries
tab.