Using VBA to generate multiple reports as seperate .pdf

[davem05]

I have been struggling with this for a little while now. I would like to have the ability to save multiple reports (50-100, a few times a week), each requires saving using fields within the report, the report is based on a query "Result Query to Report" driven by the primary key "labNumber".



I have searched various forums for a suitable loop, and the below is the closest I have come, however it gets stuck at the DoCmd.OutputTo line, I admit my VBA with Access isn't the greatest, I'm much more used to using Excel. I say it gets "stuck", but I have to cancel it to make it stop. I can see the report placed into the directory, but it seems to get stuck in the loop or just loops over the same primary key (not sure how accurate that is).
The SQL is just a copy from the query builder.

Any help would be massively appreciated.

Thanks

Private Sub MultiReport_Click()
Dim strRptFilter As StringDim rst As DAO.RecordsetSet rst = CurrentDb.OpenRecordset("SELECT DISTINCT [labNumber]  FROM [Cat Details] ORDER BY [labNumber];", dbOpenSnapshot)Dim myPath As StringDim strReportName As StringDim reportName As StringDoCmd.OpenReport "Results Certificate 2", acViewReport, "", "", acNormalmyPath = "C:\Users\Dave\desktop\"strReportName = Reports![Results Certificate 2]![labNumber] & Reports![Results Certificate 2]![clientFullName] & ".pdf"reportName = "Results Certificate 2"strRptFilter = "" & _"SELECT Client.clientFullName, Client.clientAddress, [Cat Details].labNumber, [Cat Details].catName, [Cat Details].catMicrochip," & _"[Cat Details].catRegistration, [Cat Details].catDOB, [Cat Details].catBreed, [Cat Details].catSex, [Cat Details].vetChecked," & _"[Cat Details].resultRDHCM, [Cat Details].dateProcessedRD, [Cat Details].resultMCHCM, [Cat Details].dateProcessedMC, [Cat Details].resultPKD," & _"[Cat Details].dateProcessedPKD, [Cat Details].resultPKIN, [Cat Details].dateProcessedPKIN, [Cat Details].resultPRA, [Cat Details].dateProcessedPRA," & _"[Cat Details].resultBG, [Cat Details].dateProcessedBG, [Cat Details].resultGM2, [Cat Details].dateProcessedGM2, [Cat Details].resultHypo," & _"[Cat Details].dateProcessedHypo, [Cat Details].resultAmber, [Cat Details].dateProcessedAmber, [Cat Details].resultChoc, [Cat Details].dateProcessedChoc," & _"[Cat Details].resultDil, [Cat Details].dateProcessedDil, [Cat Details].resultCinn, [Cat Details].dateProcessedCinn, [Cat Details].resultAgouti," & _"[Cat Details].dateProcessedAgouti, [Cat Details].resultSia, [Cat Details].dateProcessedSia, [Cat Details].resultBurClpt, [Cat Details].dateProcessedBurClpt," & _"[Cat Details].resultM1 , [Cat Details].dateProcessedM1, [Cat Details].resultMMM2, [Cat Details].dateProcessedMMM2, [Cat Details].resultM3, [Cat Details].dateProcessedM3," & _"[Cat Details].resultM4 , [Cat Details].dateProcessedM4, [Cat Details].resultGSD, [Cat Details].dateProcessedGSD, [Cat Details].resultSMA, [Cat Details].dateProcessedSMA" & _"FROM Client INNER JOIN [Cat Details] ON Client.[clientID] = [Cat Details].[clientID];" & _"WHERE ((([Cat Details].labNumber) Between [lab number from] And [lab number to]));"Do While Not rst.EOFstrRptFilter = "[labNumber] = " & Chr(34) & rst![labNumber] & Chr(34)DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & strReportName, FalseDoEventsrst.MoveNextLooprst.CloseSet rst = Nothing End Sub

[pbaldy]

Can you post the code in a more readable format?

[davem05]

Can you post the code in a more readable format?

My apologies, I hadn't managed to notice it copied over like that, late night!

Private Sub MultiReport_Click()


Dim db As DAO.Database


Dim strRptFilter As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [labNumber]  FROM [Cat Details] ORDER BY [labNumber];", dbOpenSnapshot)


Dim myPath As String
Dim strReportName As String
Dim reportName As String


DoCmd.OpenReport "Results Certificate 2", acViewReport, "", "", acNormal


myPath = "C:\Users\Dave\desktop\"
strReportName = Reports![Results Certificate 2]![labNumber] & Reports![Results Certificate 2]![clientFullName] & ".pdf"
reportName = "Results Certificate 2"


strRptFilter = "" & _
"SELECT Client.clientFullName, Client.clientAddress, [Cat Details].labNumber, [Cat Details].catName, [Cat Details].catMicrochip," & _
"[Cat Details].catRegistration, [Cat Details].catDOB, [Cat Details].catBreed, [Cat Details].catSex, [Cat Details].vetChecked," & _
"[Cat Details].resultRDHCM, [Cat Details].dateProcessedRD, [Cat Details].resultMCHCM, [Cat Details].dateProcessedMC, [Cat Details].resultPKD," & _
"[Cat Details].dateProcessedPKD, [Cat Details].resultPKIN, [Cat Details].dateProcessedPKIN, [Cat Details].resultPRA, [Cat Details].dateProcessedPRA," & _
"[Cat Details].resultBG, [Cat Details].dateProcessedBG, [Cat Details].resultGM2, [Cat Details].dateProcessedGM2, [Cat Details].resultHypo," & _
"[Cat Details].dateProcessedHypo, [Cat Details].resultAmber, [Cat Details].dateProcessedAmber, [Cat Details].resultChoc, [Cat Details].dateProcessedChoc," & _
"[Cat Details].resultDil, [Cat Details].dateProcessedDil, [Cat Details].resultCinn, [Cat Details].dateProcessedCinn, [Cat Details].resultAgouti," & _
"[Cat Details].dateProcessedAgouti, [Cat Details].resultSia, [Cat Details].dateProcessedSia, [Cat Details].resultBurClpt, [Cat Details].dateProcessedBurClpt," & _
"[Cat Details].resultM1 , [Cat Details].dateProcessedM1, [Cat Details].resultMMM2, [Cat Details].dateProcessedMMM2, [Cat Details].resultM3, [Cat Details].dateProcessedM3," & _
"[Cat Details].resultM4 , [Cat Details].dateProcessedM4, [Cat Details].resultGSD, [Cat Details].dateProcessedGSD, [Cat Details].resultSMA, [Cat Details].dateProcessedSMA" & _
"FROM Client INNER JOIN [Cat Details] ON Client.[clientID] = [Cat Details].[clientID];" '& _
'"WHERE ((([Cat Details].labNumber) Between [lab number from] And [lab number to]));"


Do While Not rst.EOF
strRptFilter = "[labNumber] = " & Chr(34) & rst![labNumber] & Chr(34)


DoCmd.OutputTo acOutputReport, "Results Certificate 2", acFormatPDF, myPath & strReportName
DoEvents
rst.MoveNext
Loop


rst.Close
Set rst = Nothing






End Sub

[pbaldy]

No worries. I see a few things. For starters, the first line setting strRptFilter is meaningless, since it isn't used and gets reset inside the loop anyway. Plus you don't use it in the loop for anything anyway. Secondly, you get the report values before the loop, so they will always be the same. Similarly, since you've opened the report before the loop, I think you'd always get the same output anyway.

[davem05]

Thanks for looking at it, much appreciated.
I admit I didn't really know what I was doing, I've gone back to the drawing board on this one.
Thanks

[June7]

Maybe this will help https://www.accessforums.net/import-...ges-25042.html