diff between query and sql code?

[bbxrider]

in the example following here, it seems to be invoking the 'yourQuery'
but the sql code is doing that same thing only based on an index
lookup. so........
why is 'yourQuery' being invoked, why not open the table involved
up front and then run the sql?
i was trying to invoke a more complicated query that requires a user input
key to determine which data to look up and how to pass that key to the query
from a form

example:
getting some data based on a user input keyword and eventually displaying it
involved using a predefined query

Select field1, field2, field3 From yourTable
and it's called yourQuery

Now let's say the controls on your form are called:
txtField and txtKeyword

Now something like this code goes in the click event of your cmd button:
**air code**

Dim db As Database
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourQuery")
strSql = "Select field1, field2, field3 From yourTable " & _
"Where " & Me.txtField & " Like '*" & _
Me.txtKeyword & "*'"
'you might want to view the above to make sure
'it's syntax is correct
MsgBox strSql
qdf.SQL = strSql
'now the query that your report is
'based on has the chosen criteria
Set qdf = Nothing
Set db = Nothing

 

[Van T. Dinh]

I am not entirely sure I understood your question but ...

It looks like the RecordSource of your Report is the Query and the code
simply modifies the Query to add the criteria and then perhaps, open the
Report with the modified Query.

The whole purpose of the posted code is to modify the Query AFAICS.

HTH
Van T. Dinh
MVP (Access)

 

[bbxrider]

thanx for the reply
my question still is
if you have a query that is select1, select2, etc from some table
and
you have sql code that does select1, select2, etc from the same table based
on some
index/key value that is obtained via form input
why do you need the query at all?
the sql code does everything and more than the query?
and i don't understand afaics????

 

[Van T. Dinh]

Like I wrote, the code you posted simply modifies the Query. Why you
modified the Query is your decision and I can't answer that. However,
people often use a basic Query as the RecordSource for a Report (the *Query
Name* is saved in the design of the Report) and then modify the Query in
code to suit the user's needs.

I personally prefer to use Parameter Query as the RecordSource for the
Report and make sure all the Parameter values are available before I open
the Report. In this case, I don't need to modify the Query.

AFAICS: as far as I can see.