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?

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

"Pendragon" wrote:

> 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?

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?

"Pendragon" wrote:

> Never mind!! I modified my search a few times more in the newsgroup and
> found a solution using QueryDef.
>
> "Pendragon" wrote:
>
> > 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?

On Wed, 23 Jul 2008 09:29:02 -0700, Pendragon wrote:

> 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"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Wed, 23 Jul 2008 10:18:17 -0700, Pendragon wrote:

> 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?
>
> "Pendragon" wrote:
>
>> Never mind!! I modified my search a few times more in the newsgroup and
>> found a solution using QueryDef.
>>
>> "Pendragon" wrote:
>>
>>> 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?

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"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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.

"fredg" wrote:

> On Wed, 23 Jul 2008 09:29:02 -0700, Pendragon wrote:
>
> > 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"
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

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.

"Pendragon" wrote:

> 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 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...

On Wed, 23 Jul 2008 10:26:08 -0700, Pendragon wrote:

> 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.
>
> "fredg" wrote:
>
>> On Wed, 23 Jul 2008 09:29:02 -0700, Pendragon wrote:
>>
>>> 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"
>>
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail
>>

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.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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?

"(E-Mail Removed)" wrote:

> 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...
>