adjusting a query at run time

[GREGORY HICKMOTT]

Can I add a where statement to a query at run time an what is the syntex
with opening the query

 

[Allen Browne]

You can reassign the query statement by setting the SQL property of the
QueryDef.

This kind of thing:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...

That is probably more efficient than creating another query into the first,
i.e.:
SELECT * FROM Query1 WHERE ...

 

[Guest]

-----Original Message-----
Can I add a where statement to a query at run time an what is the syntex
with opening the query


.
put your criteria in [ ] on the criteria line of the

query design window under the field you want to search

 

[GREGORY HICKMOTT]

I have tryed but get errors in my sql i think im gettig close
'SQL = "UPDATE procedurespersamplebyclientsub " & "WHERE
procedurespersamplebyclientsub.ProceduresId = -1606758480"

 

[Andrew Backer]

I use CurrentDb.QueryDefs("queryname").Sql = SOMESQL

What is the benefit to using dbEngine(0)(0)? Does that equate to
Workspaces(0)? Benefits/drawbacks?

I keep see that method mentioned, as well as the workspaces method,
but I'm not sure why.

You can reassign the query statement by setting the SQL property of the
QueryDef.

This kind of thing:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...

That is probably more efficient than creating another query into the first,
i.e.:
SELECT * FROM Query1 WHERE ...

..--------------------------------------
| Andrew Backer
| backer_a @ h0tmai1 dot com
`--

 

[Allen Browne]

Gregory, if you just want to supply the -1606758480 at runtime, then you
could follow the advice in the other thread, and just enter this in the
Criteria row of your original query under the ProceduresID field:
[Enter the ProcedureID:]

In what context are you executing your update query?
As an example, enter a dummy value in your original update query, and then
switch it to SQL View (View menu when you are in query design). Could you
build a string that emulates what you see, and then Execute the string?

 

[Allen Browne]

Hi Andrew

Either will do. The difference is the dbEngine(0)(0) refers to the default
database that is already open in the default workspace. Advantage: very
quick to reference.
Disadvantage: may not be up to date if you have recently changed the data
structure (creating tables, fields, querydefs etc).

CurrentDb() creates a new database variable, and points it to
dbEngine(0)(0). Before it assigns it, it flushes all the collections in the
default workspace, so it is always up to date. The disadvantage is that it
flushing all the collections takes time, so it is slower, but safer.

It is important to understand that you create a new database variable with
each call to CurrentDb(). For exmaple, this will not work:
CurrentDb.Execute "DELETE FROM MyTable;"
Debug.Print CurrentDb.RecordsAffected
This will always return zero, because the 2nd call creates a new database
variable, which means the RecordsAffected property is zeroed.

 

[GREGORY HICKMOTT]

-1606758480"
this is just a sample of data that will change with a combobox selection
i looked at the sql view and got the syntex that worked
now i just have to replace my sample with the combobox tag
thanks for the help

 

[John Vinson]

-1606758480"
this is just a sample of data that will change with a combobox selection
i looked at the sql view and got the syntex that worked
now i just have to replace my sample with the combobox tag

The syntax would be something like

WHERE fieldname = [Forms]![your-form-name]![your-combo-name]