query is running a piece of code three times

[brett]

I created a query. I wanted to display a big input box so
the user only had to press a 1,2,or a 3 to set the domain
name versus typing it out; a VB module called InputDomain
was wrote that does this. The query does work as expected
and the display box that I wrote in the module does appear
as expected; the issue is that the query runs the module
three times so the user that runs this query would have to
enter a number 1 the first, enter the two dates that are
parameters within the query, then press number 1 again
twice to answer the same thing three times. If I take out
the date criteria parameters, the input box from the
module only shows up once but if I add one parameter input
for like the starting date, then the input box from the
module shows up three times. I still need the user to be
able to input the starting and ending dates. Please tell
me what I'm doing wrong. Here's the SQL statements:

SELECT outages.Date, outages.Domain, outages.[From Who],
outages.Problem, outages.[FileNet Software Issue], outages.
[Production Outage Time], outages.[Total System Issue
Time], outages.[From Time], outages.[To Time], outages.
[Business Impact], outages.Resolution1,
outages.Resolution2, outages.Resolution3,
outages.Resolution4, outages.[Resolved By],
outages.CaseID, outages.[Preventative Action1], outages.
[Preventative Action2]
FROM outages
WHERE (((outages.Date)>=[Enter the starting date:] And
(outages.Date)<=[Enter the ending date:]) AND
((outages.Domain)=InputDomain()));

 

[AnExpertNovice]

This is probably not the best solution, but it will stop the aggravation.

Set a global variable with the value returned by the function.
In the function check to see if the global variable is 0 or length 0 or
whatever.
if so, run your current code.
if not, just return the value of the global variable.

Resetting the value is your responsibility!

Good luck.

 

[Duane Hookom]

I would create a form that allows users to enter all criteria into text or
combo boxes. Use these values to filter your query.