Using one report and exporting to individual PDF files

I am not that familiar with Access; however, I have managed to set up my tables, queries and reports.

The report contains expenditures which are grouped by Approver.

What I need to do is create multiple PDF files (could be some other format) that can be emailed to each approver.

Does anyone know how to do this? I have seen this question on multiple forums, but no answers.

I am not familiar with visual basic or XML, but certainly willing to jump into it if someone out there can assist me.

Thank you.

Post your answer or comment

comments powered by Disqus
Hi Everyone,

Hoping someone wouldn't mind donating some time. I know the basics of VBA but I am trying to help out my father-in-law and I'm in over my head. He uses Access 2007 for a business database he runs. Here is the situation:

There is a report that when run asks for a date range (start and end date)
Once this runs, the report will generate 30-40 pages.

We want to:
(i) export to PDF (can do)
(ii) export to individual PDFs
(iii) give each PDF a filename from the fields on each exported page (ie. Client name + unique request number.pdf)

Thank you in advance,

Hi! I am wondering if someone can assist. I am trying to write code (which I am a little familiar with at the basic level) that will do the following within a given database:

Export a Report containing "Letters" to individual .pdf files (i.e. If report has 5 separate letters, I should end up with 5 separate files)Save EACH report to a specific network location (i.e. d:databaseregionmanager name). The "region" value and the "manager value" are contained in fields that make up the table on which the report is based. I can add them as hidden fields on the report if that helps in any way. These two fields will vary by letter. I would estimate there is 100 combinations between the two, but not sure at this point.

The letters are grouped by userID.

This is the first time I have ever posted to a forum so I am not familiar with everything I may need to provide for someone with more expertise to assist. Please let me know if I am missing anything. Again, doing this type of stuff is fairly new for me (including Access 2010; used to work on older versions).


I wonder if there is a simpler way to do this.

I have a report that is printed based on search criteria. These might be a radial search, name search, file number etc. Each report is based on a separate parameter query, so there is a separate query for the radial search, name, file number etc. Right now I have 9 queries and 9 reports. The only difference is which parameter is used. The reports are selected from a menu.

Is there a way to have one report and be able to choose which query the report displays?

Hi all,

Please help me with the issue mentioned in the subject. I have a report in an access database which has about 400 pages which I have to split and export to multiple pdf files.
In the same database I have a table with 3 columns:
- the first column contains the first page of the pdf file
- the second column contains the last page of the pdf file
- the last column contains the title of the pdf file

Eg: the report will be split in 3 pdf files:
- one that starts at page 1(first column of the table) and continues to page 112(second column of the table) and is named Document1.pdf(3rd column of the table)
- the second starts at page 113, continues to page 350 and is named Document2.pdf
- the third starts at page 351 until page 400 and is named Document3.pdf

Many thanks!

Is there a control that can be used in a report that when clicked upon will take the user to a specific page of the report? AND, if so, would such links be valid when the report was exported to a pdf file?

The application here is that of a multi-page directory of individuals whose profiles appear on pages subsequent to the first "index" page. I want to provide for the user the means to locate a name in the index that when clicked upon will take the user to the report page that contains the corresponding profile of the individual. Further, I want to export the report to a pdf file and have the "Index links" functional when the pdf file is view with Adobe Acrobat Reader.

Thanks for any thoughts.
Bill Stanton


I am very new to visual basic and require some serious help. I have an Access database with 10 queries set up. I have some code to run these queries and automatically populate excel. The 10 different queries need to go on 10 different sheets in excel.

There are currently 10 functions in vb to run each one separately. This seems to work (code below).

The problem is that other people will be using this and will not be able to change any parameters in the code if required. I have therefore set up a parameters table (Query_name, Excel_sheet, and Cell_address) and require some code that will loop through the table and run each query to populate excel as per parameters.

The following code I am using to run the query and export to excel:

Function CouncilRecordsByPlanType()

' Excel constants:
Dim strcXLPath As String
strcXLPath = "C:tempTemplate_data_quality_tabs_Camden.xls "
Dim strcXLTarget As String
strcXLTarget = "C:tempData_quality_tabs_Camden_20120321.xls "
Const strcWorksheetName As String = "Data"
Const strcCellAddress As String = "A3"

' Access constants:
Const strcQueryName As String = "result_LGA_01_DP_SP_noNZ"

' Excel Objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range

' DAO objects:
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objRS As DAO.Recordset

On Error GoTo Error_Exit_CouncilRecordsByPlanType

' Open a DAO recordset on the query:
Set objDB = CurrentDb()
Set objQDF = objDB.QueryDefs(strcQueryName)
Set objRS = objQDF.OpenRecordset

' Open Excel and point to the cell where
' the recordset is to be inserted:
Set objXL = New Excel.Application
objXL.Visible = True
Set objWBK = objXL.Workbooks.Open(strcXLPath)
Set objWS = objWBK.Worksheets(strcWorksheetName)
Set objRNG = objWS.Range(strcCellAddress)
objRNG.CopyFromRecordset objRS

' Save and close excel workbook
objWBK.SaveAs strcXLTarget

' Destroy objects:
GoSub CleanUp


Exit Function


' Destroy Excel objects:
Set objRNG = Nothing
Set objWS = Nothing
Set objWBK = Nothing
Set objXL = Nothing

' Destroy DAO objects:
If Not objRS Is Nothing Then
Set objRS = Nothing
End If
Set objQDF = Nothing
Set objDB = Nothing



MsgBox "Error " & Err.Number _
& vbNewLine & vbNewLine _
& Err.Description, _
vbExclamation + vbOKOnly, _
"Error Information"

GoSub CleanUp
Resume Exit_CouncilRecordsByPlanType

End Function

I have also found this query to loop through table but not sure how to get these codes to work together. Can anyone help with this.

Confused beginner

Every month I have to print some reports for different cities. As you see in picture. I have to select every city and print them. Can anyone help just once push the button and reports for each cities print separately and convert to PDF. Thank you. Attached Files MonthlyStatment.pdf (58.9 KB, 8 views) Reply With Quote 09-02-2012, 08:46 AM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,234 This and the related looping code should get you started.

You'd want to output to PDF rather than email.

Regards to all my English is not the best so I apologize in advance to everyone I will try to be as specific as possible.
My problem is the following:
I use the database and need to make a report and export to the TXT UTF-8 on pre-defined format (one-line 21 field separate by the ";")
I made a report on a query that displays the desired result, however, when export the report in txt utf-8 following errors occur:
If for example report is:

John - Senior;Smith; 7/7/55;1/1/11
After exporting I get
I noticed that the line-break occurs when using "-" and "space" I am not sure if even more character affects the export.

And another problem is the empty line




I am using MS Access 2003. Is there a way to have an acces report automatically export to a pdf?

Hi all,

Maybe this is the wrong place to ask the question, but is it possible to print out a report in PDF and then combine with other PDF files that are in the same folder and give the new PDF file a name from a value in a field?
If this is possible, that would solve me a lot of work as I have to do this manual and there are a lot of files to be combined per day.

Thanks in advance

pretty much the title says it all. i want to create a macro or VB that would export to an excel file and name the file the current date. if anyone can point me in the right direction i would greatly appreciate it

Mahalo Nui Loa

I am doing a transferspreadsheet method for a query result in access. I am exporting to the same file every time the method is ran. The problem I am having is I want the results to go to the same worksheet every time but I keep getting a new available worksheet whenever the method completes. ae: Original worksheet is Schedule and after running the command I get Schedule1, Schedule2... ect. I did read in help that when exporting to a 5.0 or later excel file this happens.
My question is. Is there any way around this?


Using Office 2003

lets see if i can explain this correctly. If there is a better way please point that out to me also. Thanks.

I have multiple forms that do just about the same thing. I want to clean up the database so I am going to use one form and switches to vary the data or displays if needed. the form can be opened directly, linked to a button and used as a subform. the form is based off one table, but depending on how its opened, the criteria for which record is shown changes. when used in the subform it needs to be based of a field in the main form. When used on the form with the button that opens the form it is using data from the button.

I have 2 different qry's for each problem. I just can't get the form to pick the correct qry va VBA code. I want to have the forms switch to determine which qry to use.


In my database I have a list of organizations with a certain goal. If that organization can not hit that goal we have to "reallocate" the left over goal to another organization. As it stands now I have the following fields, Goal (which should stay stagnant), change (to either subtract from one organization and add to another) and New. What I would like to do is have a way to have a way to calculate if there is a change to maybe have a drop drown that when the change is subtracted I can add that to "new" of another organization.

I'm trying to create a path that will automatically link a record to a pdf file on our network

I'm able to store the path details OK but I also need to include the job no which happens to be the same name as the Pdf file

The job no is taken from a txt box that needs to be added to the end of the path

the path is ldl2180DataAttachementsInvoices

the job no is



	Me.txtFilePath = "ldl2180DataAttachementsInvoices" & "Me.Parent.txtJobNo"

that I'm trying to use is obviously incorrect.

any corrections would be most welcome


I have very little experience with SQL and Access, but here it goes. I have a field called DivisionName and one called DivisionNumber. The DivisionNumber is populating fine. What I need to do is populate the DivisionName dependant on what the DivisionNumber is. For example: DivisionNumber = 111 and this refers to DivisionName = Maintenance, and DivisionNumber = 129 refers to DivisionName = Sales. I have a single table (tblDivisionInfo) in which both these fields are in. I am thinking I may need a If Else statement inserted somewhere in the SQL, but I don't know where or how. The SQL of the query below may assist. I am querying tables to generate a report and export to an excel file. If there is any way to accomplish this with the expression builder … let me know. Thanks for any assistance you can provide.

SELECT tblDivisionInfo.[DivisionName], Mid([tblDivisionInfo. [DivisionNumber],4,3), tblAce.[CenterName], tblAce.[Center Desc], tblAce.[Expenses], tblAce.[Budget], tblAce.[Balance]
FROM tblDivisionInfo, tblAce
RIGHT JOIN tblAce ON tblDivisionInfo.DivisionName = tblAce.DivisionName
ORDER BY tblAce.[CenterName];



DivisionName text
DivisionNumber text


CenterName text
Center Desc text
DivisionName text
Expenses number
Budget number
Balance currency

I have a database which imports data from a text file does some conversions & reports and exports to a text file. What i need to do is instead of specifying the filename, is to tell it to get the oldest file in a folder as the import file. Then when the conversions have been done, move the file to another directory, can someone give me a clue
(BTW I 'm a complete newbie to vba, so go easy on me )

I'm using version 5 and want to rename some files. a search on the internet gave me this

	my.Computer.FileSystem.RenameFile("C:Test.txt", "SecondTest.txt")

I have put the code to a command button but as soon as I move the cursor away I get an error message saying "compile error expected ="

sorry but yet again I have crashed and burnt and find myself asking for help.

any advice please?

I have Access database that have a hyperlink field to the PDF file. For which in the form I put Click button with the below code so that I can select the hyperlink to open up the pdf file which is working fine with Access 2003 .

Now problem is after installing Access 2007 when I click command button I got first MS OFFICE security warning message :

“Opining d:filetraker2009nad1.pdf
Some file can contain virus or otherwise be harmful to your computer. It is important to be certain that this file is from a trustworthy source. Would you like to open this file? “

When I press ok it doesn’t open Adobe Acrobat Reader nor linked pdf file.

(my code is as below)

Private Sub Command184_Click()

On Error Resume Next
Dim ctl As Control

If IsNull(FileTarkerHL) = False Then

Set ctl = Me.FileTarkerHL
Application.FollowHyperlink Me.FileTarkerHL

Call MsgBox("Sorry No Image Attached ", vbOKOnly + vbInformation, " FileTracker ")

End If

End Sub

The tool that we use for web analytics only has one export option availible, CSV. Since our sales reps cannot distribute the CSV file to their clients they need individual PDF's which they can distribute. In order to save myself an entire day of data entry every month, I am building an access application which will import the CSV and spit out one report for each advertiser.

This is where my problem is. I would like to auto email the report to the rep as a function of the report generation process. I have connected Access to Outlook in the past but this was for a one report that went to a few addresses. In this case I would need the opposite, to export to pdf and send all of "Jim's" reports to him in a single email.

any help is greatly appriciated.

Hi All,

I'm very new to VBA but have cobbled together the following code from a number of more experienced users online. I have a report called ITD Summary (Division) that has page breaks for each different BFR Name, and I'm trying to use this code to export each of these BFR Name sections of the report as individual .pdf files. The code below executes just fine, but it ends up exporting the entire report for each of the individual BFR Names rather than just the individual section of the report. Any advice? Thanks in advance!

CODE (sorry for the poor formatting)

Sub TestModule()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim baseSQL As String
Dim rptSQL As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT [BFR Name] FROM [BFR Names]",
Set qdf = dbs.QueryDefs("[DOM Query 2]")

baseSQL = " SELECT [DOM Query 1].Investigator, [DOM Query 1].[Fund
Code], [DOM Query 1].Revenue, [DOM Query 1].[Non Salary Exp], [DOM
Query 1].[Salary Expense], [DOM Query 1].[Earned Rev], nz([DOM Query
1]![Non Salary Exp],0)+nz([DOM Query 1]![Salary Expense],0) AS [Total
Expenses], nz([DOM Query 1]![Revenue],0)+nz([DOM Query 1]![Non Salary
Exp],0)+nz([DOM Query 1]![Salary Expense],0) AS [R/3 Balance],
IIf(nz([Earned Rev],0)-nz([Revenue],0)

Help needed!!
I have report created in Access 2010 originated from a querybased on a table.
My table is called: tblFiledReconn
In order to manipulate what data I want to see in the reportI created a list query called: RptQry_List_Table_For_Entech_Use
Finally I have a report based on this query called:FieldReconnFormReport

There is a field on my table called FacilityID.
What I need is to print a report for each FacilityID into anindividual PDF file containing the FacilityID as part of the file name. At themoment this would be 817 individual reports.

I also wish if I can add a date included on the report whichis on another field called: Date. But again this is a wish and not a must.Since I am not an avid code writer I decided to get the need accomplishedbefore attempting this. Since the Date field is a date, I guess I need toconvert it into string or numbers to the format I desire before adding it tothe name which would made the code more complex.
Searching and reading forums I was able to develop thefollowing code. In this attempt I was trying to accomplish the following.
For each facility ID print a pdf individual report with thefacility id as part of the name followed by "_FRECON.pdf". Forexample for facility ID wwMH84303 the report file name would bewwMH84303_FRECON.pdf
Here is the code:
Code: Private Sub Command0_Click() Dim MyDB As DAO.Database Dim MyRS As DAO.Recordset Dim strSQL As String Dim strRptName As String Dim count As Integer strRptName = "FieldReconnFormReport" strSQL = "Select tblFieldReconn.[FacilityID] FromRptQry_List_Table_For_Entech_Use;" Set MyDB = CurrentDb Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly) With MyRS Do While Not MyRS.EOF DoCmd.OpenReport strRptName, acViewPreview, ,"[FacilityID]=" & ![FacilityID] DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF,"C:Temporary FR Forms" & ![FacilityID] &"_FRECON.pdf" DoCmd.Close acReport, strRptName, acSaveNo .MoveNext Loop End With MyRS.Close Set MyRS = Nothing End Sub The problem is when I run the code there seems to be aproblem with the openreport comand line. A window comes out requesting aparameter value. The Enter Parameter Value window shows the Current Facility IDand a text box. If I hit OK it creates a one page report with blank fields butwith the correct file name. If I type the current Facility Id on the text boxit creates the one page report correctly. So I my guess is that it is notunderstanding the instruction that matches the Facility ID in the report withthe corresponding Facility ID from the recordset ( "[FacilityID]="& ![FacilityID]). Since it does creates the correct file name I assume itis creating the desired recordset. I have spent several hours trying to get itwork but no success. Please help!
Now to the wish part:
As I explained there is also a Date field in the table(,query and report). It would be great if the created file name could be acombination of both. For example for FacilityID: wwMH98765 visited ofDate:11/20/2012 it would be great is the file name could bewwMH98765_FR112012.pdf or better if wwMH98765_FR121120.pdf (year,month,day)
Thanks in advance


I am trying to export each dealer_id record from a general report using a command button. The report is already groupped by dealer_id. The name of the database where the records are is named : Active_Dealer_List. I would like to print the record Main_Report using the command(0) button in a form. Anyone can help me with the code because I have absolutly no idea how to do it.

I am also wondering if it's really harder to move directly to the pdf files. However, I cannot install every software I need on the computer but I already have a few ones installed on it.


I want to be able to in a form click a button which automatically creates a report converts it to .pdf and set the PDF file name to one of the field values and saves it in a specific location (k:/statements)

E.G if field "accountno" in tblaccount is 45678 then the pdf would be called k:/statements/45678.pdf

Table Name= tblaccount

Form Name= frmaccount

Report Name= rptstatement

does anyone know the code to do this or a good forum posting to help (had a look on here and Google and could not find anything definitive)

Not finding an answer? Try a Google search.