Export Data for Distinct Value from Report Header

[ertweety]

Hi There,



I'm having a problem with a module I wrote and I am hoping someone can help.

What I am Trying to Achieve - A separate report for each distinct vendor, exported by vendor#. My source data has 3 lines for the same vendor# and therefore produces a separate report for each line of data. The reports are identical. How do I just export one report for each Vendor#?


My report is based on a table called 'MT_SC_S15_FINAL_TABLE'. It is being grouped by the VENDOR#.

I have another table with is called 'EDISPATCH_TOWERS'. This table has a list of all my Vendor #'s. Actually both tables include the Vendor #.

Now this is the code I have written....

Option Compare Database
Function exp()
Dim rsDat As Recordset
Set rsDat = CurrentDb.OpenRecordset("Select [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

Help!

[June7]

Try:

SELECT DISTINCT [VENDOR#] From [EDISPATCH_TOWERS]

[ertweety]

Thanks again June7. I think it works but I get a pop up box saying 'Enter Parameter Value'. Below that message it says 'A0385' which is a Vendor#. If I hit cancel it exports. How do I get this message not to appear so I don't have to hit cancel 100 times?

[June7]

The vendor number is text, not number. Text values need apostrophe delimiters (use # for date values). Try:

"[VENDOR#] = '" & rsDat(0) & "'"

[ertweety]

Thanks again June! You are awesome!

I think I almost have this thing working. The only problem I am having now is that when a seaparate .pdf report is exported for each vendor, the same report is repeated 3 times in each file. So, for vendor # A0385, the pdf has 3 pages in the report that are all the same. Is there a way to just get 1 page in the report?

Here's the code again;


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

[June7]

Something wrong about the report design and/or the source data which I expect is a query of joined tables. If you want to provide project I will look at. Follow instructions at bottom of my post. Identify what objects are involved.

[ertweety]

Copy of MASTER Tower_Scorecards.zipI am using both tables for my module export.


'MT_SC_S15_FINAL_TABLE' is the table that my report is built on. You will see that Vendor #'s have 3 lines of data each but I would only like to export one. e

I should mention that I built everything in SQL and then just imported it into access in order to build the report. That is why there are no queries in the db attached.

Thanks for your help June7. I appreciate it.