ISSUE: Access module is exporting 3 identical pages for each vendor report.
WHAT I WOULD LIKE: I would like each vendor report to be exported to pdf with only one page.
There are 3 lines of data in the source data (MT_SC_S15_FINAL_TABLE) for each vendor and this is why the report is producing 3 reports for each vendor. Unfortunately, there is really nothing I can do to change the data. I wrote it in SQL and brought it into Access in order to produce the report.
Is there a way only to export the first page of the report for each vendor?
Attaced is the database. View attachment Copy of MASTER Tower_Scorecards.zip
Here is my module code for export which you can see in the attached database;
Option Compare Database
Function exp()
Dim rsDat As Recordset
Set rsDat = CurrentDb.OpenRecordset("Select distinct [VENDOR#] From [EDISPATCH_TOWERS]")
rsDat.MoveFirst
Do
DoCmd.OpenReport "rpt_scorecards", acViewPreview, , "[VENDOR#] = '" & rsDat(0) & "'"
DoCmd.OutputTo acOutputReport, "rpt_scorecards", acFormatPDF, "C:\ACCOUNT_" & rsDat(0) & ".pdf"
DoCmd.Close acReport, "rpt_scorecards", acSaveNo
rsDat.MoveNext
Loop Until rsDat.EOF
MsgBox "Tower Scorecards Exported"
End Function
WHAT I WOULD LIKE: I would like each vendor report to be exported to pdf with only one page.
There are 3 lines of data in the source data (MT_SC_S15_FINAL_TABLE) for each vendor and this is why the report is producing 3 reports for each vendor. Unfortunately, there is really nothing I can do to change the data. I wrote it in SQL and brought it into Access in order to produce the report.
Is there a way only to export the first page of the report for each vendor?
Attaced is the database. View attachment Copy of MASTER Tower_Scorecards.zip
Here is my module code for export which you can see in the attached database;
Option Compare Database
Function exp()
Dim rsDat As Recordset
Set rsDat = CurrentDb.OpenRecordset("Select distinct [VENDOR#] From [EDISPATCH_TOWERS]")
rsDat.MoveFirst
Do
DoCmd.OpenReport "rpt_scorecards", acViewPreview, , "[VENDOR#] = '" & rsDat(0) & "'"
DoCmd.OutputTo acOutputReport, "rpt_scorecards", acFormatPDF, "C:\ACCOUNT_" & rsDat(0) & ".pdf"
DoCmd.Close acReport, "rpt_scorecards", acSaveNo
rsDat.MoveNext
Loop Until rsDat.EOF
MsgBox "Tower Scorecards Exported"
End Function
