Okay I'm desperate

[Lori]

I have a problem that I've been trying to resolve, there is nothing on this
site that I can find nor in the help menu so here goes.

I have a report that is based on a query. The query will ask two questions,
1. Which Project # and 2. Which Trade.

Here is the problem, if there are 5 companies in the same trade on my list,
when I preview the report it will ask me both questions 5 times. How do I
stop this?

 

[RBear3]

How is your query built?

If it uses one table and has an "and" criteria it should work for you. Give
us more details.

 

[Lori]

It actually uses multiple tables and is combining data. There is no duplicate
request for the information since it is all coming from tables (no queries).
The query combines a total of 6 tables, all that have the same common ID#
which is the join between each. There is data pertaining to each trade in
each of the tables.

 

[RBear3]

Post the sql here so we can see the query.

 

[Dennis]

Well, what I do is open a little data-selection form which is completely
separate from the report. The user selects their criteria, and then I write
the query so that the selections are passed into it. Then the user NEVER gets
asked for values by the query.

I use a PUBLIC FUNCTION to pass the values into the query. I also use PUBLIC
variables (defined in a stand-alone module) to keep the values selected in
the form.

For example, I'll create:

PUBLIC MyVar1 as String (in the module)

Then, in the form, let's say you have a control called "me.MySelection". In
the form, after the click the "Generate Report" button, I'll set:

MyVar1 = me.MySelection

Now, also in the module, I'll define a finction. For example:

PUBLIC FUNCTION MyVarToQuery()

MyVarToQuery = MyVar1

End Function

Then, in the query, under CRITERIA, I specify "=MyVarToQuery()"


Works every time. Repeat for each parameter you want to integrate into a
query/report.

 

[Lori]

would this work if I created an unbound form that would open "On Open" for
the report?

 

[Lori]

THANK YOU THANK YOU THANK YOU