Get RPT header from recordset in loop

[iowabuckmaster]

Setup
From Form1 user picks YRQTR from drop down
and picks another YRQTR from drop down (for comparison)
and picks Region from Drop Down
and then user used to pick a salesdistrict
Then would go to another form for the type of report to run.

With 40 sales districts it took a long time to run them all.

We want to write all 40 reports out to 40 different files with one click.

I have that working fine. Instead of using the option to choose the sales district, I loop through the table (record set). And insert field into file name. (see code below)

I have a minor detail that I can’t seem to get and it shouldn’t be this hard.

In the report header is =[Forms]![Select Criteria]![Salesdistrict]
Which is pointing to the 4th dropdown that I don’t use in the form anymore. So the report header is blank.

I want bring into the report header the Salesdistrict field from the Record Set, (same one I am inserting into the filename below).

THE QUESTION
How can I get the report to use the current Salesdistrict from my recordset for each report written.????? So instead of =[Forms]![Select Criteria]![Salesdistrict]
I should use ?

I have made many attempts but can’t find get it with correct syntax. I hope it is possible.

Here is the some of the setup and code.

Dim db As DAO.Database
Set db = DBEngine(0)(0)
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("SalesdistrictList", dbOpenTable)

Do Until rst.EOF
IndSalesdistrict = rst("Salesdistrict")

Go to Sub with Bunch of code to build report data

If Fmt = 2 Then
DoCmd.OutputTo acReport, "State Sales Report", "RichTextFormat(*.rtf)", "C:\" & IndSalesdistrict, True
End If

rst.MoveNext
Loop


"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ

 

[Remou]

How about something like:
[tt]'Module
Public IndSalesdistrict

Sub XYZ()
IndSalesdistrict = "Utopia"
...
End Sub

'Report
Private Sub Report_Open(Cancel As Integer)
Me.txtTitle.Caption = IndSalesdistrict
End Sub[/tt]

 

[iowabuckmaster]

I didn't get it at first but it got me going in the right direction and here is what works.
--------------------------------------------------
'MODULE 1
Option Compare Database

Public GlobalIndSalesdistrict As String

Public Function GetGlobalIndSalesdistict() As String
GetGlobalIndSalesdistict = GlobalIndSalesdistict
End Function
------------------------------------------------------
' SUB
.....
Do Until rst.EOF
IndSalesdistict = rst("Salesdistict")
GlobalIndSalesdistict = rst("Salesdistict")
.....

----------------------------------------------------
Then in the report, in the control for the heading,
I was able to call the function

=GetGlobalIndSalesdistict()

"I'm just glad I had my bow in hand, from the time I saw him till he was down was about 45 seconds!" Nov. 1st 157 7/8 Typ