I have used the standard Outputto macro utility which is is not VBA... so I am bit lostBefore OutputTo.
I have used the standard Outputto macro utility which is is not VBA... so I am bit lostBefore OutputTo.
Public strRptFilter As String
If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
strRptFilter = vbNullString
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryClassRosterMultiple") 'My parameter query
qdf.Parameters(0) = [Forms]![frmClassRoster]![Weekday] 'Form control
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
strRptFilter = "[ClassID] = " & rst.Fields("ClassID")
DoCmd.OutputTo acOutputReport, "rptClassRosterMultiplePDF", acFormatPDF, "I:\Programs and Re-entry\Class Rosters" & "\" & rst.Fields("Instructor_Agency_ID") & Format(Date, "mmddyyyy") & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Private Sub MultiReport_Click()
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, , acFormatPDF, myPath & strReportName, False
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
The following is the barebones of the process.
Declare a variable in a Module (as Public):
In the Open event of your report put this:Code:Public strRptFilter As String
In the Close event of the report, remember to reset the variable:Code:If Len(strRptFilter) <> 0 Then Me.Filter = strRptFilter Me.FilterOn = True End If
The OutputTo part:Code:strRptFilter = vbNullString
Amend the bits in red and change the path name to suit your needs.Code:Dim rst As DAO.Recordset set rst = currentdb.openrecordset("SELECT DISTINCT [[COLOR=Red]GroupID[/COLOR]] FROM [[COLOR=Red]TableName[/COLOR]] WHERE[COLOR=Red] ...[/COLOR] ORDER BY [[COLOR=Red]GroupID[/COLOR]];", dbOpenSnapshot) do while not rst.eof strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]] DoCmd.OutputTo acOutputReport, "[COLOR=Red]ReportName[/COLOR]", acFormatPDF, "[COLOR=Red]Path to folder[/COLOR]" & "\" & rst![[COLOR=Red]GroupID[/COLOR]] & ".pdf" doevents rst.movenext loop rst.close set rst = nothing
For the SQL statement, simply copy the SQL from your report's record source and only SELECT the GroupID field. If the data type of the GroupID field is Text then you need to change this:
to thisCode:strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]
Code:strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & [COLOR=Blue]chr(34)[/COLOR] & rst![[COLOR=Red]GroupID[/COLOR]] & [COLOR=Blue]chr(34)[/COLOR]
Option Compare Database
Option Explicit
Private Sub Command16_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [DName] FROM [Spring2013FTmailmergeDEMO] ORDER BY [DName];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[DName] = " & Chr(34) & rst![DName] & Chr(34)
DoCmd.OutputTo acOutputReport, "Spring2013FTmailmergeDEMO", acFormatPDF, "C:\Users\Path to Folder" & "\" & rst![DName] & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
End Sub
Private Sub Report_Close()
strRptFilter = vbNullString
End Sub
The following is the barebones of the process.
Declare a variable in a Module (as Public):
In the Open event of your report put this:Code:Public strRptFilter As String
In the Close event of the report, remember to reset the variable:Code:If Len(strRptFilter) <> 0 Then Me.Filter = strRptFilter Me.FilterOn = True End If
The OutputTo part:Code:strRptFilter = vbNullString
Amend the bits in red and change the path name to suit your needs.Code:Dim rst As DAO.Recordset set rst = currentdb.openrecordset("SELECT DISTINCT [[COLOR=Red]GroupID[/COLOR]] FROM [[COLOR=Red]TableName[/COLOR]] WHERE[COLOR=Red] ...[/COLOR] ORDER BY [[COLOR=Red]GroupID[/COLOR]];", dbOpenSnapshot) do while not rst.eof strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]] DoCmd.OutputTo acOutputReport, "[COLOR=Red]ReportName[/COLOR]", acFormatPDF, "[COLOR=Red]Path to folder[/COLOR]" & "\" & rst![[COLOR=Red]GroupID[/COLOR]] & ".pdf" doevents rst.movenext loop rst.close set rst = nothing
For the SQL statement, simply copy the SQL from your report's record source and only SELECT the GroupID field. If the data type of the GroupID field is Text then you need to change this:
to thisCode:strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]
Code:strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & [COLOR=Blue]chr(34)[/COLOR] & rst![[COLOR=Red]GroupID[/COLOR]] & [COLOR=Blue]chr(34)[/COLOR]
I followed the steps with the exception that the PDF button location is on the report.
strRptFilter = "[Client ID] = " & rst![Client ID]
Me.YourTextFieldForTheClientID = [rst![Client ID]