Running a Customized Query

[Guest]

I created a customized SQL string that uses input from a form. Now, since it
is customized based on the given information in the form, I cannot create a
pre-designed query. So, to get around this, I began to use:

Dim SQLString As String
SQLString = "SELECT * FROM <table based on form input>"
DoCmd.RunSQL SQLString

Problem: RunSQL does not process the SELECT statement. Apparently it only
accepts action queries (queries that make modifactions to tables).

So, I tried:

Dim cmd As Command
cmd.CommandText = SQL String
cmd.CommandType = adCmdText
cmd.Execute

Problem: Doesn't work because I believe I have to set up the
cmd.ActiveConnection, which I do not know how to set up to tell it to connect
to the current DB.

Is there anything I can do to process this simple SELECT statement without
creating a pre-designed Query?

Thanks, Shane

 

[Guest]

I'm going to sort of answer my own question here in case anyone would like to
know the solution I came up with:

I will have to create my own search my opening a specific RecordSet based on
the form input and search through the fields I would like to search through.
However, I am not aware if VB has an equivalent to SQL's pattern matching
(Ex. WHERE Name LIKE 'John%' and returns names like John, Johnny, John Smith,
etc.)
In addition, I will have to search linearly and not be able to take
advantage of the fields that I indexed in a table for quicker search time.

If anyone has a better suggestion, please let me know.

Thanks, Shane

 

[Guest]

If anyone has a better suggestion, please let me know.

Thanks, Shane

Yes. There is a better suggestion. For the simple cases, NO CODE is needed
at all, and you can indeed use a single stored predefined query.

The trick is to use a Parameter Query. Instead of a criterion such as "John"
in the query, use a criterion

=[Forms]![NameOfYourForm]![NameOfAControl]

to pick up whatever value the user entered onto the form.

It's convenient to base a second Form, or a Report for printing (or both),
on this query; the form on which you enter the criteria can then have a
command button opening the form (or printing the report). It's not necessary
to ever open the query datasheet at all.

John W. Vinson/MVP

 

[Guest]

I have ran many queries that way, however, they only work in the WHERE clause
of SQL. If I wanted to Query on different tables, you cannot do something
like:

SELECT *
FROM Forms![Search Form]![Search Table];

The value obtained from the form is automatically enclosed in quotes, which
gives SQL a syntax error naturally. In the WHERE clause, that is ok, because
you would have:

SELECT *
FROM Table
WHERE Field = Forms![Search Form]![Search Text];

and this would create an example SQL command of:

SELECT *
FROM Table
WHERE Field = "Data";

Thanks for the response, but not quite what I need, hehe.

-Shane

If anyone has a better suggestion, please let me know.

Thanks, Shane

Yes. There is a better suggestion. For the simple cases, NO CODE is needed
at all, and you can indeed use a single stored predefined query.

The trick is to use a Parameter Query. Instead of a criterion such as "John"
in the query, use a criterion

=[Forms]![NameOfYourForm]![NameOfAControl]

to pick up whatever value the user entered onto the form.

It's convenient to base a second Form, or a Report for printing (or both),
on this query; the form on which you enter the criteria can then have a
command button opening the form (or printing the report). It's not necessary
to ever open the query datasheet at all.

John W. Vinson/MVP

 

[John Vinson]

I have ran many queries that way, however, they only work in the WHERE clause
of SQL. If I wanted to Query on different tables

Sorry. Missed that key point.

However - if you have multiple tables of the same structure, your
table design is simply and purely incorrectly normalized. In a
properly designed database the need to do this will not arise.

John W. Vinson[MVP]