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.


Sponsored Links:



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,
PB.




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).

Thanks,
sreni




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




Hi

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
objWBK.Close

' Destroy objects:
GoSub CleanUp

Exit_CouncilRecordsByPlanType:

Exit Function

CleanUp:

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


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

Return

Error_Exit_CouncilRecordsByPlanType:

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.

Thanks,
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.

http://www.granite.ab.ca/access/emai...recipients.htm

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 ";")
example:
FNAME;LNAME;DOB;DATE; ...
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
John
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
example:

FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...

FNAME;LNAME;DOB;DATE; ...
FNAME;LNAME;DOB;DATE; ...

Thanks,
Ivan




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
megatronixs




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?

Thanks

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.

thanks,




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.Parent.txtJobNo

the

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

that I'm trying to use is obviously incorrect.

any corrections would be most welcome

thanks




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];

Structure

tblDivisionInfo

DivisionName text
DivisionNumber text

tblAce

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

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




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
Else

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

End If

End Sub