define filter at runtime

  • Thread starter Thread starter Stewart Hunt
  • Start date Start date
S

Stewart Hunt

I'm not real familiar with filters, so maybe this is a dumb question...

our app has following:
set dbase=wkspace.opendatebase(userfile)

and about a thousand queries like this:
set rsMyrecordset = dbase.openrecordset("select * from mytable")
(well maybe not a thousand, but a lot)

we are modifying app, introducing a [Year] field into [mytable]. and all
the existing queries need to be tweaked to consider this new field. the
user selects the year from a dropdown, and all the queries need to select
only records for the user-specified year.

so, question is, instead of going thru code, tweaking each query, can I
define a filter on the table, so that the queries can be left as-is, but the
recordset will now be year-sensitive ?

Thanks !
 
Dear Stewart:

I think what you're asking is for the ability to reference the value
in a control from inside the query. If you're using a Jet database
and not pass-through then this is easily done:

[Forms]![<your form's name goes here>]![<your control name goes here>]

Just put that directly in the query in place of the value to which you
want to filter.

If it is not for Jet or is a pass-through, then you'll need to build
the SQL string in a variable and use the SQL string for the query.
You can assign it as a RecordSource, RowSource, open a recordset with
it, or save it as a query and open the datasheet for it. Action
queries can also be created this way and function through the RunSQL
method.

But the first method above is probably your best bet, and is very
popular.

I'm not real familiar with filters, so maybe this is a dumb question...

our app has following:
set dbase=wkspace.opendatebase(userfile)

and about a thousand queries like this:
set rsMyrecordset = dbase.openrecordset("select * from mytable")
(well maybe not a thousand, but a lot)

we are modifying app, introducing a [Year] field into [mytable]. and all
the existing queries need to be tweaked to consider this new field. the
user selects the year from a dropdown, and all the queries need to select
only records for the user-specified year.

so, question is, instead of going thru code, tweaking each query, can I
define a filter on the table, so that the queries can be left as-is, but the
recordset will now be year-sensitive ?

Thanks !
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top