Saving as Snapshot files when looping Recordset

[johnnywinter via AccessMonster.com]

I am on to step 2- to save reports as snapshot with the file names as fields
from the recordset or table. Thanks to pietlinden, jerrydata and raskew
(bob) for all their help to get me this far.


I loop a recordset to filter a report and print only those records matchinfg
the record in the recordset


What I want to do:

Output a Snapshot file instead of a report and make the directory if not
already there and then save the SNP in:

C:\Commissions\ [Month] [Year] \ & [REP] &.snp

[Month] [YEAR] are both fields in each record selected to print

[REP] is a field that is in each record selected to print (also is in the
recordset)


PROBLEMS:

1) the below code that filters prefectly in printing. . .. does not filter
records when saving as a snp.

2) How do code the location to use the [fields] in both the subfolder name
and the file name


Here's my code that works perfectly to print out hardcopies.
*************************************************************************************************

Private Sub Command0_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Me.Filter = "[Rep]= '" & rsRep.Fields!REP & "' "
Me.FilterOn = True

Do Until rsRep.EOF

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, Me.Filter, "
[Rep]= '" & rsRep.Fields!REP & "' "

doCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo

rsRep.MoveNext

Loop

rsRep.Close
Set rsRep = Nothing

End Sub

**********************************************************************************************

 

[johnnywinter via AccessMonster.com]

I have gotten code to work to the stage that it loops and saves a Snapshot
file for each record in recordset.

Problem is that it saves them with the same file name, so that all I left
with is the snapshot report for the last record in recordset.

So now the question is: what is code to save each file as different file
with name of:

Report Name and field [REP] from the recordset.

There are also 2 fields I would like in the name. (fields are [MONTH] and
[YEAR]. These are fields in each record in the recordset and are also
fields in each record of the table which is the source for the report (tbl
"CurrMoCollectionsByRep].

so ideally I would like the file name to be saved as:

c:\Commissions\ [REP] [MONTH] [YEAR]. snp


Here is my code that works except saving as separate file names.
******************************************************************************************
Private Sub Command3_Click()

Dim rsRep As DAO.Recordset
Dim strReport As String
Dim strPath As String
Dim strFileName As String

strReport = "Curr Mo Collections EACH Rep"
strPath = "C:\Commissions & \"


Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Me.Filter = "[Rep]= '" & rsRep.Fields!REP & "' "
Me.FilterOn = True

strWhere = "[Rep]= '" & rsRep.Fields!REP & "' "
Do Until rsRep.EOF
doCmd.OpenReport strReport, acViewPreview, , "[Rep]= '" & rsRep.Fields!REP
& "' "

doCmd.OutputTo ObjectType:=acOutputReport, _
objectname:=strReport, _
outputformat:=acFormatSNP, _
outputfile:="C:\Commissions\jwtest & .snp" (THIS IS MY PROBLEM)

doCmd.Close acReport, strReport, acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub
***********************************************************************************************************

 

[johnnywinter via AccessMonster.com]

I have code working to output a report that has been filtered/where and
outputs a separate report for each record in a recordset and then outputs
each report as a Snapshot file to a unique file name which is comprised of
values from fields in the recordset


Thanks to everyone for their input.


NEW QUESTIONS:

1) I want to create a subdirectory (if it does not exist) to be named from 2
fields in the recordset: [Month] [Year]-- and save the snapshot files in
that new subdirectory. (so that each month's reports are in a separate
subdirectory)

2) How to I check that both the directory and the subdirectory do not exist
and if they do not exist to make them?

I had to Open Report first to use the filter or where,
If you want to print our hard copies of each report just change (in line
Docmd.Open Report ) "acViewPreview" to "acViewNormal" . If you do not want
to create Snapshot files comment out the section : Docmd.OutputTo ..........
......

For anyone that needs it, here is my code to output Snapshot reports to
unique file names-- based on a recordset

************************************************************************************************************

Private Sub Command3_Click()

Dim rsRep As DAO.Recordset
Dim strReport As String
Dim strPath As String
Dim strFileName As String

strReport = "Curr Mo Collections EACH Rep"
strPath = "C:\Commissions & \"


Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Me.Filter = "[Rep]= '" & rsRep.Fields!REP & "' "
Me.FilterOn = True

strWhere = "[Rep]= '" & rsRep.Fields!REP & "' "
Do Until rsRep.EOF
doCmd.OpenReport strReport, acViewPreview, , "[Rep]= '" & rsRep.Fields!REP
& "' "

doCmd.OutputTo ObjectType:=acOutputReport, _
objectname:=strReport, _
outputformat:=acFormatSNP, _
outputfile:="C:\Commissions\ " & rsRep.Fields!Month & " " & rsRep.Fields!
year & " " & rsRep.Fields!REP & " " & rsRep.Fields!repNAME & ".snp"

doCmd.Close acReport, strReport, acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub
**************************************************************************************************************