Using a variable twice (1 Viewer)

[yippie_ky_yay]

Hello,

In my main report, I have 2 subreports that run the exact same query (they just display different data). The query prompts the user for a year. This works great except that the user is prompted twice (because the query runs twice - once for each subreport).

Does anyone have any suggestions on how I could get it down to one prompt?

Thanks in advance!
-Sean

 

[cstahlberg]

Do you set up your queries in code? if so, you can simply prompt the user with a text box on an open event and get the value for year and then, using that, incorporate the value into the query language:

e.g.

sub Report_open
dim qdf as querydef
dim yearval as integer
yearval = inputbox("What year?")
set qdf = currentdb().createquerydef("subquery1","Select * from [mytable] where year = " & yearval & ";")
me.subform1.recordsource = "subquery1"
me.subform2.recordsource = "subquery1"
qdf.close
End Sub

hope that helps

 

[yippie_ky_yay]

Good idea cstahlberg - thanks!

-Sean

 

[Rich]

Use a form to enter the Parameter, just keep it open while the Report is opening, change the criteria to the form and textbox

 

[yippie_ky_yay]

Use a form to enter the Parameter, just keep it open while the Report is opening, change the criteria to the form and textbox

Thanks Rich - my first instinct was to create a form, but I wasn't sure how I'd do it.

-Sean

 

[WayneRyan]

Yippie,

Base your report on a query. The query will use stuff like:

Forms![YourParameterForm]![SomeParameter]

as the criteria for fields. Then you can also display the parameters on the
report using the same syntax.

Wayne

 

[yippie_ky_yay]

Thanks everyone - it's now working great!

-Sean