Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 03-09-2011, 05:23 AM   #16
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,422 Times in 2,388 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exporting reports by group

To start with, have even done what was explained in post #10?

vbaInet is offline   Reply With Quote
Old 03-11-2011, 06:23 AM   #17
e-negron
Newly Registered User
 
Join Date: Mar 2011
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
e-negron is on a distinguished road
Re: Exporting reports by group

I don't see the posts numbered....
e-negron is offline   Reply With Quote
Old 03-11-2011, 06:56 AM   #18
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,422 Times in 2,388 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exporting reports by group

On the far right of a user's post you will see the numbers next to what looks like a scale, just above the Join Date.

vbaInet is offline   Reply With Quote
Old 03-16-2011, 05:18 AM   #19
e-negron
Newly Registered User
 
Join Date: Mar 2011
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
e-negron is on a distinguished road
Re: Exporting reports by group

I did wrote the first 3 instructions, but I could not follow the rest...
It is not crucial that the program creates the folders to save the reports of each group. I can create them manually because they are standard. What I really would like to be done is that report be saved in the particular folder which has the same name as the group Id and the name of the reports contain the month that they belong. The data from which the report is based has the variable of the month. As an example, the name of one report could be "101_201103.xls" to be saved in the folder named "101".
e-negron is offline   Reply With Quote
Old 03-16-2011, 05:38 AM   #20
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,422 Times in 2,388 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exporting reports by group

What was explained in post #10 is the first step to achieving what you want. You have to get that working before even thinking about trying to save them in different folders. In programming you don't jump the gun.
vbaInet is offline   Reply With Quote
Old 03-17-2011, 12:08 AM   #21
obama5493
Newly Registered User
 
Join Date: Mar 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
obama5493 is on a distinguished road
Re: Exporting reports by group

Quote:
Originally Posted by vbaInet View Post
Before OutputTo.
I have used the standard Outputto macro utility which is is not VBA... so I am bit lost
obama5493 is offline   Reply With Quote
Old 03-17-2011, 04:53 AM   #22
e-negron
Newly Registered User
 
Join Date: Mar 2011
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
e-negron is on a distinguished road
Re: Exporting reports by group

But where it is the Outputto or where I write it?

e-negron is offline   Reply With Quote
Old 03-17-2011, 04:58 AM   #23
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,422 Times in 2,388 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exporting reports by group

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.
vbaInet is offline   Reply With Quote
Old 06-06-2013, 09:41 AM   #24
seant8
Newly Registered User
 
Join Date: Jun 2013
Posts: 1
Thanks: 1
Thanked 2 Times in 1 Post
seant8 is on a distinguished road
Re: Exporting reports by group

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.
seant8 is offline   Reply With Quote
The Following 2 Users Say Thank You to seant8 For This Useful Post:
AgDawg (02-03-2014), zepel (08-19-2013)
Old 08-19-2013, 06:15 PM   #25
zepel
Newly Registered User
 
Join Date: Aug 2013
Posts: 3
Thanks: 5
Thanked 0 Times in 0 Posts
zepel is on a distinguished road
Re: Exporting reports by group

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!






Quote:
Originally Posted by vbaInet View Post
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 [GroupID] FROM [TableName] WHERE ... ORDER BY [GroupID];", dbOpenSnapshot)

do while not rst.eof
    strRptFilter = "[GroupID] = " & rst![GroupID]

    DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "Path to folder" & "\" & rst![GroupID] & ".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 = "[GroupID] = " & rst![GroupID]
to this
Code:
    strRptFilter = "[GroupID] = " & chr(34) & rst![GroupID] & chr(34)

Last edited by zepel; 08-19-2013 at 06:26 PM.
zepel is offline   Reply With Quote
Old 09-11-2013, 02:20 PM   #26
cheetah
Newly Registered User
 
Join Date: Sep 2013
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
cheetah is on a distinguished road
Re: Exporting reports by group

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
cheetah is offline   Reply With Quote
Old 02-26-2015, 04:55 PM   #27
Pisces19
Newly Registered User
 
Join Date: Feb 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Pisces19 is on a distinguished road
Re: Exporting reports by group

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.
Pisces19 is offline   Reply With Quote
Old 09-30-2015, 01:19 PM   #28
manish1
Newly Registered User
 
Join Date: Sep 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
manish1 is on a distinguished road
Re: Exporting reports by group

I have used this code but it is generating 1300 files with 1300 pages. can someone please help?


Quote:
Originally Posted by vbaInet View Post
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 [GroupID] FROM [TableName] WHERE ... ORDER BY [GroupID];", dbOpenSnapshot)

do while not rst.eof
    strRptFilter = "[GroupID] = " & rst![GroupID]

    DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "Path to folder" & "\" & rst![GroupID] & ".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 = "[GroupID] = " & rst![GroupID]
to this
Code:
    strRptFilter = "[GroupID] = " & chr(34) & rst![GroupID] & chr(34)
manish1 is offline   Reply With Quote
Old 02-05-2018, 02:05 PM   #29
cali373
Newly Registered User
 
Join Date: Feb 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
cali373 is on a distinguished road
Re: Exporting reports by group

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.
cali373 is offline   Reply With Quote
Old 02-05-2018, 03:28 PM   #30
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Exporting reports by group

Can you please post the code you are using?

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
exporting reports as pdf from vba? CHAOSinACT Modules & VBA 3 09-07-2010 04:28 PM
Exporting Reports aziz rasul Modules & VBA 3 06-21-2006 11:39 PM
Exporting Reports Dazzla Reports 1 05-24-2005 07:22 PM
exporting reports stevehooley Reports 0 03-14-2005 05:57 AM
Exporting Reports SASHA_D Reports 6 06-04-2003 02:08 AM




All times are GMT -8. The time now is 04:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World