Exporting reports by group (1 Viewer)

vbaInet

AWF VIP
Local time
Today, 22:23
Joined
Jan 22, 2010
Messages
26,374
Step 4 is where the OutpuTo is and it should go in the CLICK event of the button you would like to use to export your report's record source per GroupID.

Copy and paste the SQL statement of your report's Record Source and indicate which field is the GroupID and I will try to explain further.
 

seant8

New member
Local time
Today, 17:23
Joined
Jun 6, 2013
Messages
1
After much study of this thread I was able to get vbaInet's solution to work for me. I registered so I could post my solution here in case it might help someone else. I'd like to thank him for a solution that works well

Step 1: Create a new module and declare the Report Filter variable as Public.
Code:
Public strRptFilter As String

Step 2: Put the following code in the Open event of the Report:
Code:
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If

Step 3: Put the following code in the Close event of the Report:
Code:
strRptFilter = vbNullString

Step 4: Place the following code in the On Click event of a button on the form where you want to export to pdf. My recordset wound up being different because my report is based on a parameter query that uses information from a combobox on the form. So intead of a SQL statement, I referenced the query itself and declared its parameters in VBA.
Code:
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

Hope this helps someone down the line, it took me a whole day to figure out.
 

zepel

New member
Local time
Today, 15:23
Joined
Aug 8, 2013
Messages
3
I have spent quite a bit of time trying to figure this one out recently, it seems this is the closest I have come, I have implemented the first 3 steps here, on the OutputTo I have a problem.

It runs and seems to get infinitely stuck, the report is opened and I can see it start to write the file to the set location, however that's as far as it gets. I have to cancel the procedure and the DoCmd.OutputTo line is highlighted, I have used this before to save single files and I can't see anything wrong with it.

Code:
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 "results certificate 2" is based on a query (where I copied the sql from)
I fear it is something to with how I am asking it to get the user to input the labNumber values (as a range)? I don't really know though...

Any help would be hugely appreciated! :D






The following is the barebones of the process.

Declare a variable in a Module (as Public):
Code:
Public strRptFilter As String
In the Open event of your report put this:
Code:
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
In the Close event of the report, remember to reset the variable:
Code:
strRptFilter = vbNullString
The OutputTo part:
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
Amend the bits in red and change the path name to suit your needs.

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:
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]
to this
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & [COLOR=Blue]chr(34)[/COLOR] & rst![[COLOR=Red]GroupID[/COLOR]] & [COLOR=Blue]chr(34)[/COLOR]
 
Last edited:

cheetah

New member
Local time
Today, 15:23
Joined
Sep 11, 2013
Messages
2
I am so close to getting this code from VBAInet to work. Right now it will create, name, and save the pdfs in the correct folder, but I'm getting the whole report for each member of the group instead of a filtered report for each group member. Could someone help me by posting an example of their SQL statement that worked? I tried to copy the SQL from my report's record source, but all that was there was the name of the table my report is based off of.

Code:
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
 

Pisces19

New member
Local time
Today, 18:23
Joined
Feb 27, 2015
Messages
1
The code suggested worked great for me. Thanks for that. I am just wondering, does anyone have any suggestions for vba code that would then email these new reports via outlook with email addresses saved in a corresponding recordset? Any suggestions for accomplishing this would be appreciated. I am assuming I would need to use the sendobject command, but I cannot determine how to make this work in conjunction with the previous code.
 

manish1

New member
Local time
Today, 15:23
Joined
Sep 30, 2015
Messages
1
I have used this code but it is generating 1300 files with 1300 pages. can someone please help?


The following is the barebones of the process.

Declare a variable in a Module (as Public):
Code:
Public strRptFilter As String
In the Open event of your report put this:
Code:
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
In the Close event of the report, remember to reset the variable:
Code:
strRptFilter = vbNullString
The OutputTo part:
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
Amend the bits in red and change the path name to suit your needs.

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:
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]
to this
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & [COLOR=Blue]chr(34)[/COLOR] & rst![[COLOR=Red]GroupID[/COLOR]] & [COLOR=Blue]chr(34)[/COLOR]
 

cali373

New member
Local time
Today, 18:23
Joined
Feb 5, 2018
Messages
1
Hello,

I got this to work, but I also need to export a table as Ms Excel, filtered by group. I tried changing the output format to excel but it did not work, it just exported the table without any filter on the column I wanted to filter on.
 

Mark_

Longboard on the internet
Local time
Today, 15:23
Joined
Sep 12, 2017
Messages
2,111
Can you please post the code you are using?
 

laza

New member
Local time
Today, 18:23
Joined
Aug 1, 2018
Messages
4
Hi all,

I am VERY new to VBA!

I found this code followed the steps and now troubleshooting...

My report is based on a query ONLY and the query prompts the user to select a date range (i.e. 07/01/2018 to 07/21/2018.) when opening the report.

I followed the steps with the exception that the PDF button location is on the report.

When I click the button - it begins printing but then errors out "Run-time error '2501': The OutputTo action was cancelled." Not sure why?

CODE BELOW:

Private Sub Command128_Click()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Client ID] FROM [Schedule] ORDER BY [Client ID];", dbOpenSnapshot)

Do While Not rst.EOF
strRptFilter = "[Client ID] = " & rst![Client ID]

DoCmd.OutputTo acOutputReport, "BillingReport", acFormatPDF, "C:\Users\sandy\Desktop\test" & "" & rst![Client ID] & ".pdf"
DoEvents
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

Private Sub Report_Close()

strRptFilter = vbNullString

End Sub

Private Sub Report_Open(Cancel As Integer)

If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If

End Sub

Thanks in advance!
 

Attachments

  • 1.JPG
    1.JPG
    15.3 KB · Views: 130
  • 2.JPG
    2.JPG
    11.7 KB · Views: 126
  • 4.JPG
    4.JPG
    21.3 KB · Views: 121
  • 5.JPG
    5.JPG
    19.7 KB · Views: 118
  • 6.jpg
    6.jpg
    86.9 KB · Views: 116

Mark_

Longboard on the internet
Local time
Today, 15:23
Joined
Sep 12, 2017
Messages
2,111
I followed the steps with the exception that the PDF button location is on the report.

Reports are for display, not interaction. You would want the button on a form.
 

laza

New member
Local time
Today, 18:23
Joined
Aug 1, 2018
Messages
4
Mark thanks for the quick response. Can I just have a button on the form that's it? I ended up getting the pdf button to work on the report (the troubles was with the location path. I took it out and it worked perfectly)... but my problem now is it isn't separating the files based on client ID.

Please excuse my beginner-ness!

Thanks,

Sandy
 

Mark_

Longboard on the internet
Local time
Today, 15:23
Joined
Sep 12, 2017
Messages
2,111
Normally you would have the button on a form and the form would also have fields for the dates. That way the user enters what they want first and hit the button. Makes it so they can verify their entry.

This also means you can have a text field (maybe with file picker) to select WHERE you want to save the files. That way you don't have to code in "C:\Users\sandy\Desktop\test" but can let the users decide where to put them.
 

laza

New member
Local time
Today, 18:23
Joined
Aug 1, 2018
Messages
4
Not much validation needs to be done other than the dates which on my report I have a field of duration where she can check what dates she put in.

Understood on the form; however, this is due by tomorrow AM.

Any suggestions on how I can get these pdfs to print seperately based on client ID or is the form the only option I have?
 

Mark_

Longboard on the internet
Local time
Today, 15:23
Joined
Sep 12, 2017
Messages
2,111
DoCmd.OutPutTo does not allow you to pass a "Where" clause the same way as DoCmd.OpenReport.

Since you can't pass the parameters in the DoCmd line, you will need to update your QUERY to use values you have on your calling form. Upside, you could have a text control (or potentially a label referenced by LabelName.Caption) that you update inside your loop and use in your query to limit your data to ONLY the values you are looking for.

Let us know if this is something you can work with.
 

laza

New member
Local time
Today, 18:23
Joined
Aug 1, 2018
Messages
4
Not sure what all that means.....unfortunately I am a beginner of two days with access.

I'm going to try and create a form based on my query and test out the form print to pdf button. Hopefully this will save multiple pdfs with only that specific client id's information within in.

Any beginner tips are welcome! :D
 

Mark_

Longboard on the internet
Local time
Today, 15:23
Joined
Sep 12, 2017
Messages
2,111
OK, to make this easy;
1) Make a BLANK form. This is called an "Unbound" form.
2) Add Textbox control for your StartDate
3) In your QUERY, go down to the criteria for that you use StartDate in and use the form's <= StartDate for your criteria. Use this LINK for help if you are not comfortable using form controls in queries.
4) Add a textbox on your form to hold the [Client ID]
5) For each record, where you currently have
Code:
strRptFilter = "[Client ID] = " & rst![Client ID]
you will replace it with
Code:
Me.YourTextFieldForTheClientID = [rst![Client ID]
6) You will have the same type of code as 3 to set your querie's [Client ID] = your forms's [Client ID]

This should give you what you are looking for; transactions on or after your startdate and matching the client ID for each of the reports you generate.
 

Users who are viewing this thread

Top Bottom