Specify exported filename using form field

[fraser_lindsay]

Hi,

I have created a command button to open a report of a sepcific record, save/export as PDF and then close the report. This forum and Google were a huge help, once again.

I'm stuck with the last part though. I would like to name the exported file using a unique reference in one of my form fields.

At the moment it exports as the name of the report, even though it is filtered. Here is my code from my command button on-click event:

Private Sub SavePDF_Click()

'This code will open the named report and match the named fields below to open the report on that specific record

Dim sReportName As String
    Dim sFilter As String
    
    sReportName = "rptSpecificJSA"
    sFilter = "JSARef = " & Me.cboJSARef
 
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport sReportName, acPreview, , sFilter
    'This will export the filtered, report in preview mode as a PDF file with the name in quotations.
    DoCmd.OutputTo acOutputReport, "rptSpecificJSA", acFormatPDF, strAttachPath & strAttachPath2, False, , , acExportQualityScreen
    DoCmd.Close acReport, "rptSpecificJSA"

End Sub

What code can I use to specify the field "JSARef" in the file name?

Thanks

 

[fraser_lindsay]

I've been mesisng around iwth this all afternoon and I think I can get fields and date etc in the filename, at least they appear in the filename dialog.

However, when I click save I get a runtime error 2059, can't find object. Basically because it looks for an object wihich is a combination of my report name (rptSpecificJSA) and the additional parameters I added like 'JSARef'.

So I've gathered I need to keep my report name as it is but this doesn't help me. There must be some way to specify the filename and not use my actual report name?

Can anyone help?

 

[Kiwiman]

Howzit

I do the following when exporting to a spreadsheet (transferspreadsheet method). Most of this is sitting inside a loop, so I have psted the relevant bits to build the file name.

I assume there is a criteria in the output to where you can specify the filename that you are exporting to.

Dim stDefault as String  ' Default file location
Dim stFolder as String  '  Sub Folder of default location
Dim stPrefix as String  ' Name of sales exec to be included in file name
Dim stExtend as String ' File extension
Dim stFilePath as String ' Build full path of all variables
Dim stDocName as string ' query to export to excel

stDocName = "qryStatements"

stDefault = "C:\"
stFolder = "Stmts\"
stPrefix =  me.cboSalesId
stExtend = ".xls"

stFilePath = stDefault & stFolder & stPrefix & stExtend 


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, stFilePath, True

 

[Mike375]

A bit off topic but I think you will need a lot more than what you have to save a Report as a PDF file that Acrobat Reader can open, unless that is something to with A2007 as I think it can ouput a Report as a PDF

 

[fraser_lindsay]

Mike, yes, I'm using A2007, so I can go straight to PDF.

Kiwiman, thanks for your code pointers. After blending that with my existing code, lots of trial and error and some expletives I have it working.

The only annoying thing is that instead of combo box text I get the column ID number. I'm trying to fix that.

Here's my code:

Private Sub SavePDF_Click()

'This code will open the named report and match the named fields below to open the report on that specific record

Dim sReportName As String
    Dim sFilter As String
    
    sReportName = "rptSpecificJSA"
    sFilter = "JSARef = " & Me.cboJSARef
 
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport sReportName, acPreview, , sFilter
  
Dim stDefault As String  ' Default file location
Dim stFolder As String  '  Sub Folder of default location
Dim stExtend As String ' File extension
Dim stFilePath As String ' Build full path of all variables
Dim stDocName As String ' query to export

stDocName = "rptSpecificJSA"

stDefault = "C:\"
stFolder = "Users\fraserl\Documents\Temporary\"
stPrefix = "GEHS_JSA_" & Me.JSARef & "_" & Me.JobGroup & "_" & format$(Date, "ddmmyyyy") & "_" & format$(Time, "hhmm")
stExtend = ".pdf"

stFilePath = stDefault & stFolder & stPrefix & stExtend
  
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stFilePath, True, acExportQualityPrint
  
    
DoCmd.Close acReport, "rptSpecificJSA"
   
MsgBox "The current JSA record has been exported to PDF"
    


End Sub

 

[Kiwiman]

Howzit

Ahh expletives...been there done that, or to be honest been there still doing that.

It may be that your combo box is multicolumned?? When you reference the combobox control, I believe it references the bound column, not what is necessayrily visible to the user

You can try

me.yourcombobox.column(x) where x is the column you want.

to return the text column. I have never tried this approach myself, but have seen similar things on other posts.

 

[fraser_lindsay]

Yes, you're right, it's a a lookup from another table. I tried the column 1 thing but it just crashes when running the code. I had high hopes though.

I'm messing around with invisible text boxes and referencing those instead, but haven't quite got that working either.

 

[Kiwiman]

Howzit

Try the code below. Make sure the immediate window is open when the vba window comes up when stepping through the code.

When referencing the combobox columns, the column indexes start at 0 (first column), 1 (2nd column) etc.

I have just tested the column method and it does work. I have taken a guess where yours should go.

Private Sub SavePDF_Click()

'This code will open the named report and match the named fields below to open the report on that specific record

[b][COLOR="Red"]stop  [/COLOR][/b]' This will halt the vba code.  You can step through each code using the F8 function key

Dim sReportName As String
    Dim sFilter As String

    [b][COLOR="Red"]Dim sName as string
   
    sReportName = "rptSpecificJSA"
    sFilter = "JSARef = " & Me.cboJSARef
    
    [b][COLOR="Red"]sName = me.cboJSARef.column(1)
    debug.print sName[/COLOR][/b]  ' what is assigned to the variable will show in the immediate window

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport sReportName, acPreview, , sFilter
  
Dim stDefault As String  ' Default file location
Dim stFolder As String  '  Sub Folder of default location
Dim stExtend As String ' File extension
Dim stFilePath As String ' Build full path of all variables
Dim stDocName As String ' query to export

stDocName = "rptSpecificJSA"

stDefault = "C:\"
stFolder = "Users\fraserl\Documents\Temporary\"
'stPrefix = "GEHS_JSA_" & Me.JSARef & "_" & Me.JobGroup & "_" & format$(Date, "ddmmyyyy") & "_" & format$(Time, "hhmm")

[b][COLOR="Red"]stPrefix = "GEHS_JSA_" & sName & "_" & Me.JobGroup & "_" & format$(Date, "ddmmyyyy") & "_" & format$(Time, "hhmm")[/COLOR][/b]

stExtend = ".pdf"

stFilePath = stDefault & stFolder & stPrefix & stExtend
  
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stFilePath, True, acExportQualityPrint
  
    
DoCmd.Close acReport, "rptSpecificJSA"
   
MsgBox "The current JSA record has been exported to PDF"
    


End Sub

 

[fraser_lindsay]

Seriously, that is quality! Thanks a million, now I know the correct syntax. I was trawling the internet and found many other code examples which referenced the .column(1) method but I just couldn't get the syntax correct.

Fantastic, thanks again.

I made a slight amendment to the code to add my second field using your logic and it worked perfectly.

I also realised that the True/False command in the code line near the end will load Adobe after the PDF is created. I found it annoying so made it 'false'.


P.S. Anyone reading this should be aware that I'm using A2007 AND you need to make sure you have installed the PDF add-in from Microsoft. You can get that here:

http://www.microsoft.com/downloads/...3C-6D89-4F15-991B-63B07BA5F2E5&displaylang=en


My final code is:

Private Sub SavePDF_Click()
'This code will open the named report and match the named fields below to open the report on that specific record

''Stop  ' This will halt the vba code.  You can step through each code using the F8 function key

Dim sReportName As String
    Dim sFilter As String

   
    sReportName = "rptSpecificJSA"
    sFilter = "JSARef = " & Me.cboJSARef
    
    sName = Me.cboJSARef.Column(1)
    Debug.Print sName  ' what is assigned to the variable will show in the immediate window

    sName2 = Me.cboJobGroup.Column(1)
    Debug.Print sName  ' what is assigned to the variable will show in the immediate window

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport sReportName, acPreview, , sFilter
  
Dim stDefault As String  ' Default file location
Dim stFolder As String  '  Sub Folder of default location
Dim stExtend As String ' File extension
Dim stFilePath As String ' Build full path of all variables
Dim stDocName As String ' query to export

stDocName = "rptSpecificJSA"

stDefault = "C:\"
stFolder = "Users\fraserl\Documents\Temporary\"

stPrefix = "GEHS_JSA_" & sName & "_" & sName2 & "_" & format$(Date, "ddmmyyyy") & "_" & format$(Time, "hhmm")

stExtend = ".pdf"

stFilePath = stDefault & stFolder & stPrefix & stExtend
  
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stFilePath, False, acExportQualityPrint
''The True/False option here will load Adobe after it creates the PDF when 'true is selected'
    
DoCmd.Close acReport, "rptSpecificJSA"
   
MsgBox "The current JSA record has been exported to PDF"
    



End Sub