Dumb Question on defaulting Query Prompt in VBA

[Andre Laplume via AccessMonster.com]

I have a button on a form that calls some VBA code. In particular it opens
a report. The report when opened executes a query to get the data. The
query has a prompt: [Enter the dept number]. So, When you click the form
button it immediately prompts for dept number which I type in and then
opens the report for that dept. I run this for 5 depts.

Well, I want to automate this to open the report for each of the depts and
write the output to a share. I know how to write the report output to a
share as a .rtf file. My issue is how to hard code the dept so I need not
bbe prompted. Essentially (in English) I want to do this:

Set DeptNbr = 300
Open Report to share
Set DeptNbr = 400
Open Report to share
etc.
etc.

I am unsure of how to set the value in the VBA code though. I also wonder
if I must adjust the query in some way....in other words how to get the vba
to communicate with the query.....

THANKS!

 

[Klatuu]

Put this in the On Click Event of your button

Dim aryDepts
Dim intCounter As Integer
aryDepts = Array("200", "300", "400", "500", "600")
For intCounter = 0 To UBound(aryDepts)
'Open Report Here
Next intCounter

 

[Andre Laplume via AccessMonster.com]

thanks!