Variable Criterion for Query

[Green Biro]

I have a form, FORMA, that has a button and two text boxes. Clicking the
button runs a doCmd OpenReport. The report contains a subreport which runs
a query, which uses the values of the text boxes on the form for its
criterion.

The problem is that I now want to use the same subreport in another report
with another form, FORMB. And of course at the moment it isn't working
because the criteria of the query is pointing to a filed in FORMA, which
isn't loaded.

I can see various way around this but I'm wandering what is the slickest
scaleable method. Here's my thoughts so far but I would appreciate input
from someone with more Access application development experience than
myself.
- I could create a new query that pulls its criterion from my new form.
Seems like unneccesary duplication.
- I could make the query a parameter query and somehow set the parameter in
VBA. Not sure how to do this within the OpenReport statement, especially as
the report is a subreport.
- I could dynamically set the WHERE clause when calling the OpenReport
command. Not sure what event I'd use and the subreport would need to know
its parent's name as that is what would ultimately dictate the criterion for
the query.

I'm thinking of going for the third route mentioned above but before I start
writing loads of extra code, please can someone else give advice on the best
way to deal with this situation.

Many thanks

GB

 

[Arvin Meyer [MVP]]

1. Use a select statement instead of a saved query at the recordsource.

2. Create a dialog form that supplies the criteria for everything.

3, Use VBA code with the select statement to open the form to the record(s)
th at you want.

More than one way to skin that cat.

 

[Green Biro]

Hmm.... Possible

The query is however much too complicated to be laid out as an SQL
statement. It also uses another query as one of its sources and it's that
subquery that requires the parameter.

I might be able to adapt things a bit and then try your approach, which does
appeal to me in principle.

I will report back to group how I get on.


GB