Export as PDF with variable document name and file path name

[thisisanemergency]

I have searched extensively for a solution but have not come across anything that explicitly refers to my issue and so I've not been able to resolve thus far.

I have a query that is filtered through a selection on a combo box and the result populates a report. This works perfectly.

I have a button that exports the resulting report to PDF format and saves it to a SharePoint document library. This also works perfectly.



My challenge is to:

1. Set the PDF document name to the value contained in a field called "DocSetName" in my query and include the prefix "CRF_". The value of "DocSetName" is unique for each record.
2. Set the save location to the correct folder in my document library, the name of which is also determined by the value of the "DocSetName" field in my query.

So the saved result should look like this:

\\portal\site\doclibrary\variablefoldername\CRF_variabledocname.pdf

where: variablefoldername = value of "DocSetName" and variabledocname = "CRF_" + value of "DocSetName".

I'm using Access 2010 and VBA.

Any ideas?

 

[pbaldy]

It may have been helpful to see your code, but generally you can concatenate fixed text with variable info from a form or whatever:

strPath = "\\portal\site\doclibrary\" & Me.DocSetName & "\CRF_" & Me.DocSetName

 

[thisisanemergency]

Hi Paul

Thanks for your response. I have tried your suggestion but I think I'm making an error. This is what I have now after your input. Am I missing something?

Private Sub Command6_Click()
 
DoCmd.OutputTo acOutputReport, "RepQryCustName", "PDFFormat(*.pdf)", \\portal\site\doclibrary\" & Me.DocSetName & "\CRF_" & Me.DocSetName", True, "", , acExportQualityPrint
End Sub

Thanks!

 

[pbaldy]

Try

DoCmd.OutputTo acOutputReport, "RepQryCustName", "PDFFormat(*.pdf)", "\\portal\site\doclibrary\" & Me.DocSetName & "\CRF_" & Me.DocSetName & ".pdf", True, "", , acExportQualityPrint

 

[thisisanemergency]

Paul - we're so close I can almost smell it! But now it's giving me "Compile Error: Method or data member not found" and highlights the first instance of Me.DocSetName in the path. I've checked my query and the DocSetName field is there and it has the data in it that I expect to see. I think this is pretty much the problem I've been wrestling with - how to get the code to find my field. Or am I confusing things?

 

[thisisanemergency]

Paul - we're so close I can almost smell it! But now it's giving me "Compile Error: Method or data member not found" and highlights the first instance of Me.DocSetName in the path. I've checked my query and the DocSetName field is there and it has the data in it that I expect to see. I think this is pretty much the problem I've been wrestling with - how to get the code to find my field. Or am I confusing things?

Could it be that while DocSetName is present in my query, I have not used the field in my report?

 

[pbaldy]

Yes, it needs to be available on the report (you can hide it).

 

[Sketchin]

This is what I did, if it helps:

 If Dir("S:\Contracts\Geomatics LAB\Templates\Drafts\" & Me.CompanyName, vbDirectory) = "" Then
        MkDir ("S:\Contracts\Geomatics LAB\Templates\Drafts\" & Me.CompanyName)
    Else
        'do nothing for the "C:\Text Files" directory already exists
        'MsgBox "S:\Contracts\Geomatics LAB\Templates\Drafts\ & Me.CompanyName directory already exists"
    End If
MyFileName = Format(Me.DateOutReq, "yyyy") & _
                 "-" & Format(Me.DateOutReq, "mm") & "-" & Format(Me.DateOutReq, "dd") & _
                 "-" & Me.CompanyName & "-ScheduleA" & ".pdf"
MyPath = "S:\Contracts\Geomatics LAB\Templates\Drafts\" & Me.CompanyName & "\"
DoCmd.OutputTo acOutputReport, "rptScheduleA", acFormatPDF, MyPath & MyFileName, False

 

[thisisanemergency]

Yes, it needs to be available on the report (you can hide it).

How great is a night in the pub for the brain! I finally got it working, thanks for your help with this. I found though that putting the field on the report didn't work, I had to put a field on the form where my button is located instead.

 

[pbaldy]

Glad you got it sorted out.