Ok, got it. Took a day longer than I expected for me to get to it but here
it is.
The only way I could think to do this was with a public variable, aside from
editing Leban's code itself, which I wanted to avoid. Usually I try and
avoid public vars, but I didn't think there was a way to pass a where
condition to the report through Leban's function (this may be doable, I
didn't even try it thought).
Essentially, what we're doing is creating a loop for the ReportToPDF
funtion. This loop will also increment our public variable by 1 each time
the code loops. The public var serves two purposes: it provides us with a
way to make separate filenames for each report, and more importantly, it sets
the recordsource for the report in it's Open event.
So, assuming that your report ID's are 1 through 52, this is what I came up
with.
In a standard module (preferably a new, though it doesn't really matter),
you need to create a public variable to hold the value of the current report
ID. (public vars are created after the Option codes at the beginning of the
module, but before any subs or functions).
So you'll see this:
'====CODESTART
Option Compare Database
Option Explicit
Public gTerritoryID As Long
'====CODE END
Next, go to your report, and in the report's OnOpen event, you need to limit
the records to those with the specified territory ID (which is held in our
new global variable, and will be set later).
So you should see this in your report:
'====CODESTART
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM tablname " _
"WHERE [IDfield] = " & gTerritoryID
End Sub
'====CODEEND
You may have to modify the SQL line a bit, I'm not sure what your details
are, but this will limit the records to whatever ID matches the value of
gTerritoryID. You may want to test this for accuracy before going any
further. The thing to remember here is that we need to make sure
gTerritoryID actually has a value before we try and run the report, as there
is no handling for it in the report itself.
The last part of this is to write a function that loops from 1 to 52,
handling the report and filenames at the same time. This could go in the
same module we put the Public Var in. See below:
'====CODESTART
Option Compare Database
Option Explicit
Public gTerritoryID As Long
Public Function PrintTerritoryReports() As Boolean
Dim lngCount As Long 'our counter from 1 to 52
Dim strFile As String 'our output file to pass to the function
'Initialize the counter
lngCount = 1
'Loop while counter is less than 53
While lngCount < 53
'Set the global variable for when the report is printed
gTerritoryID = lngCount
'Set the Filepath
'(make sure the folder exists, it will not be created for you)
strFile = "C:\YourFolder\Territory" & Trim(Str(gTerritoryID)) & ".pdf"
'Print the report
ConvertReportToPDF "Reportname", , strFile, False, False
'Increment the counter (will update next record and filename)
lngCount = lngCount + 1
Wend
'Reset the global var (not required, but cleanup is always nice)
gTerritoryID = 0
End Function
'====CODE END
That should do it. I just got done testing this before I posted, so you
should be all set. I wasn't sure that the Open event would run and
effectively restrict records when printed, but as it turns out it does. This
was tested on a Vista system using Access 2003, though I don't see it making
a difference either way.
If you have any more questions about how it works or need a hand getting it
set up with details specific to your code, let me know.
hth
--
Jack Leach
www.tristatemachine.com
- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
