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

[nicodg]

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:

Dim myPath As String
Dim strReportName As String
 
DoCmd.OpenReport "RptHyperion", acViewPreview, , "[SAP]=" & Me![Combo8], acWindowNormal
 
myPath = "S:\Accounts\Reconciliations\Recon letters\"
 strReportName = [Name] + "-" + [Hypérion] + ".pdf"  (this line causes me trouble)
DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
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

[TheSmileyCoder]

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:

strReportName = Me.tb_Name & "-" & Me.tb_Hypérion + ".pdf"

Hope this helps you.

[beacon]

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

[NeoPa]

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 :

strReportName = [Name] & "-" & [Hypérion] & ".pdf"

Also check the actual name of the field. Is it actually [Hypérion], or might it be [Hyperion]?

[nicodg]

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?

 Private Sub Command13_Click() 
Dim myPath As String 
Dim strReportName As String 
DoCmd.OpenReport "Rep Hyperion recon ", acViewPreview, , "[SAP]=" & Me![Combo8], acWindowNormal 
 
myPath = "S:\Accounts\Reconciliations\Recon letters\" 
strReportName = [Cust] & " " & [Hypérion] & ".pdf"
DoCmd.OutputTo acOutputReport, "Rep Hyperion recon", acFormatPDF, myPath + strReportName, True 
 
DoCmd.Close acReport, "Rep Hyperion recon"
 
End Sub 

Have a nice day

[TheSmileyCoder]

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.

[nicodg]

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

[TheSmileyCoder]

Ok, so this is what I would do:
Change the rowsource of the combobox to:

SELECT SAP,Cust,Hyperion
  FROM [Hyperion accounts] 
  ORDER BY [SAP];

Now the code can get easy access to the information Hyperion and Cust from the combobox like so:

strReportName = Me.cmb_SelectSap.Column(1) & "-" & Me.cmb_SelectSap.Column(2) & ".pdf"

(Just for clarity, column(0) is the SAP number)

[nicodg]

Just apply all needed changes and it works fine.

Many thanks.

Hope this topic will give an answer to other users too

[TheSmileyCoder]

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.

[NeoPa]

I agree with that. And intelligent responses too. Always more fun to deal with :-)

[NeoPa]

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.