Output a PDF with Query name to the filename

[BBBryan]

Hi,
If someone can help me.
I have a button on my form that Outputs a PDF. I have got it to work and add Todays Date onto the file name.
I also want to add a Work Order no from a Query but not sure how to.

My Query name is [WoTestPackLabelPdfQy]
I want the [WoTestPackLabelPdfQy] no to be the start of the file name

Here is what I have now
Private Sub PDF_Click()
Dim outputTestPackLabel As String

outputTestPackLabel = "\\llkna1-2\hewittb$\Welding Folder Structure\Job Packages - Welding Jobs\Maintenance\AdobePDFs\TestPackLabelForAdobe (" & Format(Date, "Medium Date") & ").pdf"

DoCmd.OutputTo acOutputReport, "TestPack(Stamp) Label Single", acFormatPDF, outputTestPackLabel, False, , , acExportQualityPrint

End Sub

This code will put the PDF into the correct folder and add todays date EX:TestPackLabelForAdobe (28-Sep-12)

I am not sure how to add the [WoTestPackLabelPdfQy] into the start of the file. EX:Wo65347722 - TestPackLabelForAdobe (28-Sep-12)

Thanks BBBryan

 

[pbaldy]

You can use a DLookup() to get the value from the query and concatenate the result into the path. You could also get it from a form, if one happens to be open with the correct value.

 

[BBBryan]

Hi Pbaldy,
Thanks for you help.
I did try this with DLookUp and I get an error.

outputTestPackLabel = "\\llkna1-2\hewittb$\Welding Folder Structure\Job Packages - Welding Jobs\Maintenance\AdobePDFs\DLookup("[WorkOrder]", "WoTestPackLabelPdfQy") & TestPackLabelForAdobe (" & Format(Date, "Medium Date") & ").pdf"

I think I am missing some of the syntax. Not sure how to write this.


Thanks

 

[pbaldy]

The concatenation is off. Try

outputTestPackLabel = "\\llkna1-2\hewittb$\Welding Folder Structure\Job Packages - Welding Jobs\Maintenance\AdobePDFs\" & DLookup("[WorkOrder]", "WoTestPackLabelPdfQy") & "TestPackLabelForAdobe (" & Format(Date, "Medium Date") & ").pdf"

 

[rbtm2006]

I simply use BulletPrinter. This works for us amazingly well... try it out

 

[BBBryan]

Thanks Pbaldy,
That works great.
Just a liitle note:
Just so I understand - Why does a " & go in front of the DlookUp


Thanks BBBryan

 

[pbaldy]

You started a string at "\\. As you had it, that string continued into the DLookup, which Access couldn't interpret as part of the path. The first quotes around the field name in the DLookup actually closed off that string, leaving you with an invalid path and a syntax error in the concatenation. You concatenate literal strings (defined by opening and closing quotes) with formulas to get your finished string:

"Something here" & Formula & "more text" & AnotherFormula

You had

"Something hereFormula & "more text" & AnotherFormula

You correctly restarted the string after the formula, but hadn't ended it before.

 

[BBBryan]

oh I see now. So you need to start off with the & ". and put the little bits together wioth the ". Thats where I made the mistake. Ok I am trying and learn from this.
Thanks for your Help.

 

[kipcliff]

When I work with complex lines of code and concatenations, I like to paste them in Word and highlight the pieces with different colours to help see what is there.

 

[BBBryan]

Hi Kipcliff,
Thank for your tip.
Thats a good idea.
I am going to use that


BBryan