Output Multiple Reports from Recordsource

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I am trying to output multiple one page reports using a recordsource as the
parameter from a push button. The code I am using is listed below, but when I
press the button it does nothing.

Any help would be greatly appreciated.

Thank you

Private Sub AM_Click()
Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String
strPathName = DLookup("[ReportPath]", "ReportPath_SIA")
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("fltr_Associates_Scorecard_AEAM")

With rst
If .RecordCount = 0 Then
msgbox "No employees to process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![rptName] & "_" * ![Name] & ".snp"

DoCmd.OutputTo acOutputReport, acFormatSNP, strFileName
.MoveNext
Loop
.Close

End If
End With

Set rst = Nothing

End Sub
 
If you get your code so it is looping thru the recordset to create a
snapshot file for each record in the recordset you need to run the
report with the appropriate where clause, then create the snapshot file,
then close the report


DoCmd.OpenReport ReportName, acPreview, , WhereClause
DoCmd.OutputTo acReport, "", "Snapshot Format", ReportFile
DoCmd.Close acReport, ReportName

Note: not sure why I had "Snapshot Format" instead of acFormatSNP (the code
is kinda old, but works fine).

For recordsets, I usually use code such as this:
Dim db As Database
Dim RS As DAO.Recordset

Set db = CurrentDb()
Set RS = db.OpenRecordset("tblXXXX", dbOpenDynaset)
If Not (RS.BOF And RS.EOF) Then
RS.MoveFirst

'do stuff

End If
RS.Close

Exit_MySub:
Set RS = Nothing
Set db = Nothing


Take a look at our email module it has some nice batch reporting
capabilities to crank out reports, it also lets you email them as
attachments if you end up going that route.

HTH,
Mark
RPT Software
http://www.rptsoftware.com
 
Thank you for your help.

Mark Andrews said:
If you get your code so it is looping thru the recordset to create a
snapshot file for each record in the recordset you need to run the
report with the appropriate where clause, then create the snapshot file,
then close the report


DoCmd.OpenReport ReportName, acPreview, , WhereClause
DoCmd.OutputTo acReport, "", "Snapshot Format", ReportFile
DoCmd.Close acReport, ReportName

Note: not sure why I had "Snapshot Format" instead of acFormatSNP (the code
is kinda old, but works fine).

For recordsets, I usually use code such as this:
Dim db As Database
Dim RS As DAO.Recordset

Set db = CurrentDb()
Set RS = db.OpenRecordset("tblXXXX", dbOpenDynaset)
If Not (RS.BOF And RS.EOF) Then
RS.MoveFirst

'do stuff

End If
RS.Close

Exit_MySub:
Set RS = Nothing
Set db = Nothing


Take a look at our email module it has some nice batch reporting
capabilities to crank out reports, it also lets you email them as
attachments if you end up going that route.

HTH,
Mark
RPT Software
http://www.rptsoftware.com





Tim said:
I am trying to output multiple one page reports using a recordsource as the
parameter from a push button. The code I am using is listed below, but
when I
press the button it does nothing.

Any help would be greatly appreciated.

Thank you

Private Sub AM_Click()
Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String
strPathName = DLookup("[ReportPath]", "ReportPath_SIA")
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("fltr_Associates_Scorecard_AEAM")

With rst
If .RecordCount = 0 Then
msgbox "No employees to process"
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = strPathName & ![rptName] & "_" * ![Name] & ".snp"

DoCmd.OutputTo acOutputReport, acFormatSNP, strFileName
.MoveNext
Loop
.Close

End If
End With

Set rst = Nothing

End Sub
Click to expand...
Click to expand...
 
Back
Top