Asking for the same Criteria 6 times

[margaret]

I have a report that uses from a query. When I run the query, the criteria
box pops up one time. I fill in the info I need and the query runs. Great.
However, when I run the report using the query, the criteria box pops up six
times. I'm having the problem with several reports that use a criteria.
They all use different queries to run and they all do this. When I was
using Access 2003, no problem. However, now that I've upgraded to Access
2007, I get this problem.

Any help?

 

[KARL DEWEY]

Try using a form to enter the criteria and then reference the form in the
queries.

 

[Keith G Hicks]

You can also create a function and use that in the query, that way the
parameter form that Karl mentioned doesn't have to remain open when you run
the report/query.

Set up a public variable for the value you need. Then create a function with
a similar name (similar so you know what it matches up with). Then use the
function in the query criteria.

In your global/public variables module:

public iCustID as Integer

Public Function fncCustID()
fncCustID = iCustID
End Function

Then like I said above, use fncCustID() in your query (since you cannot use
variables in queries).

In my example you can only run it for one customer ID. If you need multiple
custs for the query, save the selected ID's to a permanent temp table and
use that table in an inner join in your query.

HTH,

Keith