469,281 Members | 2,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

How to save an Access report to pdf with filename based on fieldname

7
Hi Evereyone,

I’m looking for some help and I’m sure to find it here ;o)

I’ve created in access a simple database to obtain reports. Via a combobox I select a customer number, click on a button and the specific report appears.
I’m able to save manually each report to a pdf file and give it a name, but I want it to be
automatically done. The most important is that filename must be “customer name” and “code” appearing on the report.

I’ve found some solutions but not working in practice.

My DB is composed of:
- A query named “QryHyperion” based on 2 excell files
o Fields composing the DB:
  • Hyperion
  • Name
  • SAP
  • Some others(not important to be mentioned)
- A report named “ RptHyperion”
- A form named “form1” with a combobox named “combo8”


Actual used syntax:
Expand|Select|Wrap|Line Numbers
  1. Dim myPath As String
  2. Dim strReportName As String
  3.  
  4. DoCmd.OpenReport "RptHyperion", acViewPreview, , "[SAP]=" & Me![Combo8], acWindowNormal
  5.  
  6. myPath = "S:\Accounts\Reconciliations\Recon letters\"
  7. strReportName = [Name] + "-" + [Hypérion] + ".pdf"  (this line causes me trouble)
  8. DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
  9. DoCmd.Close acReport, "RptHyperion"
File must be saved as “Name” & “Hyperion” .pdf
So how do I have to link both fields to be used as filename when saving?


Thanks in advance for helping me
Nov 16 '11 #1

✓ answered by TheSmileyCoder

Ok, so this is what I would do:
Change the rowsource of the combobox to:
Expand|Select|Wrap|Line Numbers
  1. SELECT SAP,Cust,Hyperion
  2.   FROM [Hyperion accounts] 
  3.   ORDER BY [SAP];
Now the code can get easy access to the information Hyperion and Cust from the combobox like so:
Expand|Select|Wrap|Line Numbers
  1. strReportName = Me.cmb_SelectSap.Column(1) & "-" & Me.cmb_SelectSap.Column(2) & ".pdf"
(Just for clarity, column(0) is the SAP number)

11 17914
TheSmileyCoder
2,321 Expert Mod 2GB
At at first glance, im guessing that the place where you run the code might not have access to the fields [Name] and [Hypérion].

Are you running the code from a form module, or a seperate module?

And if from a form, does the forms recordsource include [Name] and [Hyperion]?

If they do, try this.

First, Name is reserved word in access, and using that as a field name will likely cause you grief, and in alot of cases probably grief in a non-sensical way. For example to refer to Form.Name is that the field name in the forms underlying recordset, or the forms name?
If you insist on keeping the field named Name, then have a control on the form bound to the field, but make sure the control is named something else, for example tb_Name (TextBox name). Then you could use:
Expand|Select|Wrap|Line Numbers
  1. strReportName = Me.tb_Name & "-" & Me.tb_Hypérion + ".pdf"
Hope this helps you.
Nov 16 '11 #2
beacon
579 512MB
Are you receiving an error message? If so, can you provide it? Are you sure you have permissions to the folder where you're trying to export the file?

Have you tried removing the myPath + strReportName argument in the DoCmd.OutputTo altogether to see if Access prompts you to name the file?

Also, you may want to create another variable for the full path and concatenate the path to the report name there instead of doing so in the DoCmd.

Hope this helps,
beacon
Nov 16 '11 #3
NeoPa
32,173 Expert Mod 16PB
You're using the + character for concatenation, which is unreliable as it returns a value of Null if either of the values it joins is Null (See Using "&" and "+" in WHERE Clause for a full explanation).

If you use the & character instead then you will at least see what is missing :
Expand|Select|Wrap|Line Numbers
  1. strReportName = [Name] & "-" & [Hypérion] & ".pdf"
Also check the actual name of the field. Is it actually [Hypérion], or might it be [Hyperion]?
Nov 17 '11 #4
nicodg
7
Hello,

Thanks to you all for replying,

@ Beacon
No error message received

@ Neopa
've changed "+" to "&" to be sure

@ thesmileycoder
‘ve changed fieldname “Name” to “Cust” and linked form to recordsource. This resolves problem but generates a new one :(
Unfortunately when saving, it takes always first record from record source for every report.

So, I suppose a filter is needed or another syntax code with a condition referring to the used reference in combobox (combo8)???

How do I have to write it down? and where?

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command13_Click() 
  2. Dim myPath As String 
  3. Dim strReportName As String 
  4. DoCmd.OpenReport "Rep Hyperion recon ", acViewPreview, , "[SAP]=" & Me![Combo8], acWindowNormal 
  5.  
  6. myPath = "S:\Accounts\Reconciliations\Recon letters\" 
  7. strReportName = [Cust] & " " & [Hypérion] & ".pdf"
  8. DoCmd.OutputTo acOutputReport, "Rep Hyperion recon", acFormatPDF, myPath + strReportName, True 
  9.  
  10. DoCmd.Close acReport, "Rep Hyperion recon"
  11.  
  12. End Sub 


Have a nice day
Nov 17 '11 #5
TheSmileyCoder
2,321 Expert Mod 2GB
Ok, so you open a report with a filter based on [SAP]. What is SAP and is it in any way related to Cust and Hyperion? Could you give some more details on your combo8, like its rowsource, as well as the field types used for cust, Hyperion, SAP?


Just to clarify, its does not take the FIRST record, it takes the ACTIVE record, which if you have not done anything to scroll through records, will of course be the FIRST record.


On another note, I strongly urge you to ALWAYS give every object you use a sensible name. Instead of combo8, it could be cmb_SelectSAP.

It may seem trivial at this point to keep track of your few objects, but it will get harder down the road, and getting used to simply always renaming objects will save you alot of grief.
Nov 17 '11 #6
nicodg
7
Hi again,

Purpose of the report is to give an overview of all open invoices into accounting of our entities.

Starting from 2 excel files:
the 1st containing all references of entities (Hyperian Accounts)
SAP: customer nr from our ERP (sap) system
Hyperion: our coorporate recognize every entity with a different IDnr called Hyperion
Cust: Name of the entity
2nd excel file represents overview of documents (Accounts Statements):
sap(same as before), invoice nr, invoice date, duedate, amount.

1 query (Qry Hyperion)based on those excel files with a relationship on [SAP].

1 form: with recordsource set on DB Hyperion Account
1 combobox (badly named combo8) with row source SELECT [Hyperion accounts].[SAP] FROM [Hyperion accounts] ORDER BY [SAP];
1 button: need to run code as set before
So I select "customer nr [SAP]" from list. By clicking on the button
my report "Rep Hyperion recon" opens and appears only with filtered information.

the rest remains the same as before.


I'll try change names as you requested as soon as possible

Hope you get enough information

Kind regards
Nov 17 '11 #7
TheSmileyCoder
2,321 Expert Mod 2GB
Ok, so this is what I would do:
Change the rowsource of the combobox to:
Expand|Select|Wrap|Line Numbers
  1. SELECT SAP,Cust,Hyperion
  2.   FROM [Hyperion accounts] 
  3.   ORDER BY [SAP];
Now the code can get easy access to the information Hyperion and Cust from the combobox like so:
Expand|Select|Wrap|Line Numbers
  1. strReportName = Me.cmb_SelectSap.Column(1) & "-" & Me.cmb_SelectSap.Column(2) & ".pdf"
(Just for clarity, column(0) is the SAP number)
Nov 17 '11 #8
nicodg
7
Just apply all needed changes and it works fine.

Many thanks.

Hope this topic will give an answer to other users too
Nov 18 '11 #9
TheSmileyCoder
2,321 Expert Mod 2GB
Thats very nice to hear.

PS. I just want to compliment you on your initial question. For a first time poster that is very good description you provided, as well as appropriate details, and follow up posts in which you reply to all posters, and adress the issues raised. It makes a world of difference in the motivation to help.
Nov 18 '11 #10
NeoPa
32,173 Expert Mod 16PB
I agree with that. And intelligent responses too. Always more fun to deal with :-)
Nov 18 '11 #11
NeoPa
32,173 Expert Mod 16PB
Another post was added here by the OP but it was a new question so it's been moved to its own thread - Saving Access Reports as PDFs.
Nov 30 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tom Weddell | last post: by
2 posts views Thread by =?Utf-8?B?QmlsbHkgWmhhbmc=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.